Click cell to generate dynamic table

Question

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?

So what is the question here? Where did you get stuck coding/trying? What do you need help with? — JvdV, Apr 05 '23 at 15:06
@JvdV sorry, I forget to put this. I already edited with the question. — DatBigD, Apr 05 '23 at 15:11

JvdV · Accepted Answer · 2023-04-05T15:56:05.070

You could try the following:

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

Click cell to generate dynamic table

1 Answers1

Linked