Forum Discussion

Sekoleyte's avatar
Sekoleyte
Iron Contributor
Mar 22, 2020
Solved

How can i resolve this problem?

I prepared a table in excel with "IF" formula using a data, then i copied and pasted the values in the table as  values. Then i have so many blank cells in my table and i cannot delete them easily as...
excel
Formulas and Functions
  • JoneFriestad's avatar
    JoneFriestad
    Mar 22, 2020

    Sekoleyte 

     

    Have you tried the Filter function ?

    Example in file with your source data "as is" in first sheet, and with your source data in a table in second sheet.

    "=FILTER(Table1[Provinces];Table1[Countries]=G$1)"

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Mar 22, 2020

Sekoleyte 

Another variant with legacy solution

To select list of countries in M1

=IFERROR(
    INDEX($C$2:$C$12,
      AGGREGATE(15,6,1/(COUNTIF($L$1:L$1,$C$2:$C$12)=0)*(ROW($C$2:$C$12)-ROW($C$1)),1)
   ),
"")

and drag it to the right.

 

To pickup provinces in M2

=IFERROR(
    INDEX($D$2:$D$12,
       AGGREGATE(15,6,
          1/($C$2:$C$12=M$1)*(ROW($D$2:$D$12)-ROW($D$1)),
          ROW()-ROW(M$1)
      )
   ),
"")

and drag it to the right and down.

Sekoleyte's avatar
Sekoleyte
Iron Contributor
May 01, 2020
Thank you so much. After your answer, i searched something about array functions and aggregate usage. I really loved your approach. That legacy solution is so helpful, and i spend a few hours to comprehend it. Thanks for sharing it with us !

Resources