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

Dear Henryfuaong,

Kindly do not repeat the same queries twice. I think some contributors have answered this same query in another post of yours. I am repeating the same again.

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

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

Hi Henry

You could create a pivot table from your table of data, then display the country you need and its associated data in that Pivot Table on the required sheet.

---

henryfuaong wrote:

Good day!

 

I have a MS Excel spreadsheet with lots of rows (records) and about 8 columns. I need to extract all the rows if the value in a certain column equal to a specific string and putting them automatically in a separate tab.

 

Example: Column D has a title “Country”. Let us say, I only want to pull out all records whose value in Column D equal to “USA” and transferring those rows in another tab of the same spreadsheet.

 

Currently, what I am doing includes a manual step. First, I set the filter and the results, I cut and paste them in the other tab.

 

Appreciate if someone can share a solution to address my concerned.

 

Henry

---

Alternatively if you use Power Query (assuming you have Excel 2010 or later) then you can pull the data into Power Query, filter it by the required country and Load the data into another sheet.

If you need some guidance on these approaches then it would be great if you can provide a sample data set.

Thanks

Wyn