excelexcel-formuladynamic-tables

Click cell to generate dynamic table

I'm working in a table with Excel. Here is an example of the sheet, this is save in Sheet1:

A B C D
al id id id
df id desc desc
df id id desc
df id id id
ff desc id desc
ff desc id desc
al id id id
al id id desc
mn desc desc desc
mn desc desc desc
ff desc id desc

In this table, the goal was to compare the column A with duplicate values and you will get a table of columns A B C and D. With that table, I have to compare de columns B C and D at once. Later, I have to create a new column where I have to put 1 if they all match, 0 otherwise. For the previous table, this is what I get:

A B
al 0
df 0
ff 1
mn 1

I save this new table in other sheet named Sheet2. There are hundreds of rows in the table.

Now, I want to click in a cell of A and generate like dynamic table in the columns, for instance D,E,F,G; and get just the information in relation with the value of the cell A.

I put an example:

If I click in the value cell A2, in the columns from D to G, the following information has to be generated:

D E F G
al id id id
al id id id
al id id desc

If I click in the cell A4, this is what I have to get

D E F G
ff desc id desc
ff desc id desc
ff desc id desc

So, how can I do that?

Is there an option that do all this or I have to code a formula?

Solution

  • You could try the following:

    enter image description here

    Formula in F1 (as per my answer to your previous question):

    =LET(x,UNIQUE(A1:A11),HSTACK(x,--MAP(x,LAMBDA(y,ROWS(UNIQUE(FILTER(B1:D11,A1:A11=y)))=1))))

    Formula in F6:

    =FILTER(A1:D11,A1:A11=INDIRECT(CELL("address")),"")

    This is a volatile function which means that when you select a cell in range F1:F4 and hit F9 you'd recalculate these functions meaning the spilled result will change accordingly.

    It's not the same as changing the table upon a simple click, but that would require VBA. For example, use the following simplified code in your sheet's code panel:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Range("F1:F4")) Is Nothing And Target.Cells.Count = 1 Then
    Range("F6").Formula2 = "=FILTER(A1:D11,A1:A11=""" & Target.Value2 & ""","""")"
End If

End Sub

    enter image description here