Click cell to generate dynamic table

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 by Kithuzzz
• 38,010 points
194 views

1 answer to this question.

0 votes

Try the following:

enter image description here

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

enter image description here

answered Apr 6, 2023 by narikkadan
• 63,420 points

Related Questions In Others

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

answered Jun 20, 2022 in Others by nisha
• 2,210 points
323 views
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

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
1,311 views
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

answered Feb 10, 2022 in Others by gaurav
• 23,260 points
6,336 views
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

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
746 views
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

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
1,355 views
0 votes
1 answer

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

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

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
1,461 views
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

Feb 17, 2022 in Others by Edureka
• 13,670 points
715 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

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

answered Feb 18, 2022 in Others by gaurav
• 23,260 points
2,425 views
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

answered Sep 23, 2022 in Others by narikkadan
• 63,420 points
3,092 views
0 votes
1 answer
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP