0 votes

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?

Apr 6, 2023 in Others 259 views

## 1 answer to this question.

0 votes

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
```

answered Apr 6, 2023 by
• 63,720 points

0 votes
1 answer

## How to generate the "create table" sql statement for an existing table in postgreSQL

Create a table for a demo: CREATE TABLE ...READ MORE

0 votes
1 answer

## Change date format of cell in excel from dd.mm.yyyy to yyy/mm/dd ( excel version 2013 )

Hello :)   Excel’s Format Cells function can quickly ...READ MORE

0 votes
1 answer

## How to import data from a HTML table on a website to excel?

Hello  To import any HTML file in excel there ...READ MORE

0 votes
2 answers

## How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

0 votes
1 answer

## Calculate Birthdate from an age using y,m,d in Excel

Hello, yes u can find your birthdate using ...READ MORE

0 votes
1 answer

## Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

0 votes
0 answers

## Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

0 votes
1 answer

## IF - ELSE IF - ELSE Structure in Excel

In this case, you can use nested ...READ MORE

0 votes
1 answer

## Excel-VBA - How to identify Target range (more than 1 cell) is deleted in a Worksheet_Change function?

You misunderstand the purpose of the function ...READ MORE

0 votes
1 answer

## How do I create a hyperlink from Word to Excel specific cell, without macros/vba?

All you have to do is copy ...READ MORE