CODEWITHSUNDEEP
mysqlvbaexcelexcel-formula
Andrea S
Andrea S

Reputation: 1

How to extract all rows from excel data set when cell/column contains certain text

I have an Excel data-set that contains more columns and rows than I need in my final table. I would like to extract the rows that contain the word "Domestic" in it into a new table.
For example, I have 6 columns of data (A, B, C, D, E, F) and column "F" is where the word "Domestic" would be if the cell contains it. I would then like to bring back columns A, C, and D for all rows where column F contains "Domestic" - and if the row does not contain "Domestic" it is ignored. My headers is in row 1 and my data runs from row 2-5500. Can someone please help me with a formula or exact VBA code that would do this in Excel?

Upvotes: 0

Views: 4284

Answers (1)

Nathan_Sav
Nathan_Sav

Reputation: 8531

Using an array formula, like so, having data in a1:f21, drag down and across from I1

=IFERROR(INDEX($A$1:$F$21,SMALL(IF($F$1:$F$21="domestic",ROW($F$1:$F$21)),ROWS($I$1:$I1)),COLUMNS($I$1:I$1)),"")

Upvotes: 1

Related Questions