Click cell to generate dynamic table

Question

I'm using Excel to work in a table. An example of the sheet, which was saved in Sheet 1, is shown below:

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

The objective of this table was to compare the duplicate values in column A. As a result, you obtain a table containing columns A, B, C, and D. I have to compare the columns B, C, and D in that table all at once. Then, I need to make a new column and enter 1 if they all match, otherwise 0 in it. What I get for the previous table is as follows:

A	B
al	0
df	0
ff	1
mn	1

This new table is saved in a another sheet called Sheet2. The table has thousands of rows.

Now, I want to click in cell A to create a dynamic table-like structure in the columns, such as D,E,F, and G, and to get only the data related to the value of cell A.

I gave an instance:

The following data must be produced if I click in the value cell A2 in the columns marked D through G:

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?

Answer 1

Try the following:

The 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))))

The formula in F6:

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

This function is volatile, which means that it will be recalculated when you choose a cell in the range F1:F4 and press F9, changing the spilled result accordingly.

While altering the table with a single click would be preferable, that would require VBA. Use the streamlined code shown below, for instance, in the code panel of your sheet:

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