How To Automatically Transpose the Filtered Row in MS Excel Into Another Tab

Dear Henryfuaong,

I have attached a sample Excel file which will extract the data from Sheet1 (Data) to Sheet2 (Vijaykumar) and to Sheet3 (Shetye).

You may select as many countries as you want to.

In sheet 2, the countries are listed as per the sequence in the main Data.

Inn Sheet 3, the countries are listed as per the selection.

The formulas used are

=IF(ISERROR(MATCH($D2,$L$2:$L$7,0)),"",MAX(I$1:I1)+1)

=IF(ISERROR(MATCH($D2,$L$2:$L$7,0)),"",VLOOKUP($D2,$L$2:$M$7,2,0)+ROW()*0.001)

=IFERROR(IF(LEN(INDEX(Data!$A$1:$I$20,MATCH($A5,Data!$I$1:$I$20,0),B$4))=0,"-",INDEX(Data!$A$1:$I$20,MATCH($A5,Data!$I$1:$I$20,0),B$4)),"-")

=IFERROR(SMALL(Data!$J$2:$J$20,$A5),"-")

=IFERROR(IF(LEN(INDEX(Data!$A$1:$I$20,MATCH($B5,Data!$J$1:$J$20,0),C$4))=0,"-",INDEX(Data!$A$1:$I$20,MATCH($B5,Data!$J$1:$J$20,0),C$4)),"-")

You may change the data ranges as required.

The entire operation is carried by creating helper columns (Index), in each sheet.

Do let me know if this is what you wanted to do, or if you need some clarifications.

Vijaykumar Shetye,

Spreadsheet Excellence,

Panaji, Goa, India