3

I have the following excel spreadsheet:

  A      B      C       D      E     F     G      H
1 #    Size    Age    Color         =B1   =C1    =D1
2 1    Big
3 2    Small   2983   Yellow
4 3            3459   Red
5 4    Big            Yellow
6 5
7 6    Big     2345

Image

I would like to populate the columns F-H with the numbers (#) whose rows contain some value in the corresponding B-D column. I.e., I would like the final table to look like this:

Image

The rows F-H should of course then automatically update as entries are added into or removed from the table. How can I do this in Excel?

Mate de Vita
  • 393

1 Answers1

4

Try using the following formula:

enter image description here

• Formula used in cell F2

=IFERROR(AGGREGATE(15,6,(ROW($A$2:$A$7)-ROW($A$2)+1)/
 (B$2:B$7<>""),ROWS(F$1:F1)),"")

The above formula needs to be filled down and filled right! And this works as per the tags posted. That means this formula is applicable from Excel 2010+ onwards.

Or, Using FILTER() function to copy right, as it will automatically spill for the rest of the cells:

=FILTER($A$2:$A$7,B2:B7<>"")

Or, Using REDUCE() to return the whole array:

=IFNA(DROP(REDUCE("",SEQUENCE(COLUMNS(B2:D7)),LAMBDA(x,y,
 HSTACK(x,FILTER(A2:A7,INDEX(B2:D7,,y)<>"")))),,1),"")
Mayukh Bhattacharya
  • 4,486