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

[AndySP]

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-599 Can someone please help me with a formula or exact VBA code that would do this in Excel?

 

[Special-K99]

Try (untested)

in Sheet2!A1
=IFERROR(INDEX(Sheet1!$A$1:$D$599,SMALL(IF(Sheet1!$F$1:$F$599="Domestic",ROW(INDIRECT("1:"&ROWS($A$1:$A$59)))),ROW(A1)),1),"")
in Sheet2!B1
=IFERROR(INDEX(Sheet1!$A$1:$D$599,SMALL(IF(Sheet1!$F$1:$F$599="Domestic",ROW(INDIRECT("1:"&ROWS($A$1:$A$59)))),ROW(A1)),3),"")
in Sheet2!C1
=IFERROR(INDEX(Sheet1!$A$1:$D$599,SMALL(IF(Sheet1!$F$1:$F$599="Domestic",ROW(INDIRECT("1:"&ROWS($A$1:$A$59)))),ROW(A1)),4),"")

Note: These are array formulas, enter them using Ctrl-Shift-Enter
Copy them down the columns

 

[Fishboy]

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-599 Can someone please help me with a formula or exact VBA code that would do this in Excel?

Hi AndySP, welcome to the boards.

A possible VBA solution is as follows. This code assumes that the sheets are called Sheet1 and Sheet2 (amend as required), and also that A1:C1 on Sheet2 already has the respective column headers in place:

Sub CopyCells()
' Defines variables
Dim Cell As Range, cRange As Range


' Defines LastRow1 as the last row of data in sheet 1 based on column A
LastRow1 = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
' Defines LastRow2 as the first blank row of data in sheet 2 based on column A
LastRow2 = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row + 1


' Sets the check range as F2 to the last row of F on sheet1
Set cRange = Sheets("Sheet1").Range("F2:F" & LastRow1)


' For each cell in the check range
For Each Cell In cRange
    ' If the cell value is Domestic then...
    If Cell.Value = "Domestic" Then
        ' Copy cell A of the cell row to the first blank row of column A in sheet2
        Sheets("Sheet1").Range("A" & Cell.Row).Copy Sheets("Sheet2").Range("A" & LastRow2)
        ' Copy cells C and D of the cell row to the first row of column B in sheet2
        Sheets("Sheet1").Range("C" & Cell.Row, "D" & Cell.Row).Copy Sheets("Sheet2").Range("B" & LastRow2)
        ' Increase LastRow2 by 1 to account for the new data
        LastRow2 = LastRow2 + 1
    End If
' Check next cell in check range
Next Cell


End Sub

 

[AndySP]

Thanks but I am not getting any data
?

 

[Fishboy]

Thanks but I am not getting any data
?

Who is that aimed at?

 

[AndySP]

Thanks Fishboy! But I am getting almost double the number of rows being brought back than what contains "Domestic" in column F?

Hi AndySP, welcome to the boards.

A possible VBA solution is as follows. This code assumes that the sheets are called Sheet1 and Sheet2 (amend as required), and also that A1:C1 on Sheet2 already has the respective column headers in place:

Sub CopyCells()
' Defines variables
Dim Cell As Range, cRange As Range


' Defines LastRow1 as the last row of data in sheet 1 based on column A
LastRow1 = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
' Defines LastRow2 as the first blank row of data in sheet 2 based on column A
LastRow2 = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row + 1


' Sets the check range as F2 to the last row of F on sheet1
Set cRange = Sheets("Sheet1").Range("F2:F" & LastRow1)


' For each cell in the check range
For Each Cell In cRange
    ' If the cell value is Domestic then...
    If Cell.Value = "Domestic" Then
        ' Copy cell A of the cell row to the first blank row of column A in sheet2
        Sheets("Sheet1").Range("A" & Cell.Row).Copy Sheets("Sheet2").Range("A" & LastRow2)
        ' Copy cells C and D of the cell row to the first row of column B in sheet2
        Sheets("Sheet1").Range("C" & Cell.Row, "D" & Cell.Row).Copy Sheets("Sheet2").Range("B" & LastRow2)
        ' Increase LastRow2 by 1 to account for the new data
        LastRow2 = LastRow2 + 1
    End If
' Check next cell in check range
Next Cell


End Sub

 

[AndySP]

Thanks Special-K99 but I am not getting any data returned...?

Try (untested)

in Sheet2!A1
=IFERROR(INDEX(Sheet1!$A$1:$D$599,SMALL(IF(Sheet1!$F$1:$F$599="Domestic",ROW(INDIRECT("1:"&ROWS($A$1:$A$59)))),ROW(A1)),1),"")
in Sheet2!B1
=IFERROR(INDEX(Sheet1!$A$1:$D$599,SMALL(IF(Sheet1!$F$1:$F$599="Domestic",ROW(INDIRECT("1:"&ROWS($A$1:$A$59)))),ROW(A1)),3),"")
in Sheet2!C1
=IFERROR(INDEX(Sheet1!$A$1:$D$599,SMALL(IF(Sheet1!$F$1:$F$599="Domestic",ROW(INDIRECT("1:"&ROWS($A$1:$A$59)))),ROW(A1)),4),"")

Note: These are array formulas, enter them using Ctrl-Shift-Enter
Copy them down the columns

 

[AndySP]

@Fishboy - I see what the code is doing now! If I wanted to bring back columns C, D and F from Sheet 1 into Sheet 2 how would I write this? To adjust this code:
Copy cells C and D of the cell row to the first row of column B in sheet2
Sheets("Sheet1").Range("C" & Cell.Row, "D" & Cell.Row).Copy Sheets("Sheet2").Range("B" & LastRow2)
' Increase LastRow2 by 1 to account for the new data
LastRow2 = LastRow2 + 1

 

[Fishboy]

@Fishboy - I see what the code is doing now! If I wanted to bring back columns C, D and F from Sheet 1 into Sheet 2 how would I write this? To adjust this code:
Copy cells C and D of the cell row to the first row of column B in sheet2
Sheets("Sheet1").Range("C" & Cell.Row, "D" & Cell.Row).Copy Sheets("Sheet2").Range("B" & LastRow2)
' Increase LastRow2 by 1 to account for the new data
LastRow2 = LastRow2 + 1

Hi Andy,

Assuming you want them in the first 3 columns on the destination sheet then you would want to tweak the code as highlighted below:

Sub CopyCells()
' Defines variables
Dim Cell As Range, cRange As Range




' Defines LastRow1 as the last row of data in sheet 1 based on column A
LastRow1 = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
' Defines LastRow2 as the first blank row of data in sheet 2 based on column A
LastRow2 = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row + 1




' Sets the check range as F2 to the last row of F on sheet1
Set cRange = Sheets("Sheet1").Range("F2:F" & LastRow1)




' For each cell in the check range
For Each Cell In cRange
    ' If the cell value is Domestic then...
    If Cell.Value = "Domestic" Then
        ' Copy cells C and D of the cell row to the first row of column A in sheet2
        Sheets("Sheet1").Range("C" & Cell.Row, "D" & Cell.Row).Copy Sheets("Sheet2").Range("A" & LastRow2)
        ' Copy cell F of the cell row to the first blank row of column C in sheet2
        Sheets("Sheet1").Range("F" & Cell.Row).Copy Sheets("Sheet2").Range("C" & LastRow2)

        ' Increase LastRow2 by 1 to account for the new data
        LastRow2 = LastRow2 + 1
    End If
' Check next cell in check range
Next Cell