Extract data from one tab into another if a particular cell is blank

[St Vitus Dance]

Hi All,

I have data in one tab that I paste in from an export daily. The number of columns is always the same but the rows are always different.

If a cell in column V in tab 2 is blank, I want to be able to extract the data from each cell in that row into tab 1. If the cell in column v in tab 2 isnt blank i dont want it to be copied in.

Also, as the number of rows in tab 2 can vary, id like to have the formula descending to cover for a few hundred entries just in case. I'd like tab 1 to look tidy so if there is no data to be returned that a 0 or N/A# etc isnt returned.

Is this possible? Would it be an IF, MATCH, INDEX formula i would have to have pasted into the cells in tab 1? If so, would anyone be able to recommend a formula to help?

Thanks all, appreciate any help

 

[My Aswer Is This]

Try this Vba script.

The script looks down column"A" of Sheet(2) till it finds no more data in column "A"
And copies the rows that meet your criteria from Sheet (2) to sheet (1)

Sub Copy_Empty()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow1 As Long
Dim Lastrow2 As Long
Lastrow1 = Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row + 1
Lastrow2 = Sheets(2).Cells(Rows.Count, "A").End(xlUp).Row
    For i = 1 To Lastrow2
        If Sheets(2).Cells(i, "V").Value = "" Then
           Sheets(2).Rows(i).Copy Destination:=Sheets(1).Rows(Lastrow1)
            Lastrow1 = Lastrow1 + 1
        End If
    Next
Application.ScreenUpdating = True
End Sub

 

[tusharm]

While I usually favor non-code solutions, this one is much easier to do with code.

So, lightly tested:

Sub Macro2()
    Dim LO As ListObject
    Set LO = Worksheets(2).ListObjects.Add(xlSrcRange, Range("A1").CurrentRegion, , xlYes)
    LO.Range.AutoFilter Field:=3, Criteria1:="="
    LO.Range.Copy Worksheets(1).Cells(1, 1)
    End Sub

Hi All,

I have data in one tab that I paste in from an export daily. The number of columns is always the same but the rows are always different.

If a cell in column V in tab 2 is blank, I want to be able to extract the data from each cell in that row into tab 1. If the cell in column v in tab 2 isnt blank i dont want it to be copied in.

Also, as the number of rows in tab 2 can vary, id like to have the formula descending to cover for a few hundred entries just in case. I'd like tab 1 to look tidy so if there is no data to be returned that a 0 or N/A# etc isnt returned.

Is this possible? Would it be an IF, MATCH, INDEX formula i would have to have pasted into the cells in tab 1? If so, would anyone be able to recommend a formula to help?

Thanks all, appreciate any help

 

[St Vitus Dance]

Thanks very much folks. If possible, I'd rather avoid using VBA. Would there be a formula solution at all?

 

[My Aswer Is This]

I will let tusharm: answer your question. I'm sure he has a better answer then mine. I'll move on and find someone else to help.

 

[tusharm]

There is a formula based approach but this is one case where either SQL or VBA is superior to the formula approach. IMO, way too much work and too much subsequent maintenance. But, if you insist...

Suppose your data are in Sheet2 in A1:C13 with row 1 being headers. The idea is to extract those rows in A:B that have empty values in C.

So, in Sheet1, enter in, say, F1:H1 the literals 'Idx' (w/o the quote), and the headers from the range in Sheet2.
In F2 enter the array formula =SMALL(IF(Sheet2!$C$2:$C$13="",ROW(Sheet2!$C$2:$C$13)-ROW(Sheet2!$C$2)+1),ROW(Sheet1!$F2)-ROW($F$2)+1) -- to enter an array formula complete data entry with CTRL+SHIFT+ENTER. If done correctly, *Excel* will show the formula enclosed in curly brackets { and }

Select G2:H2 and enter the array formula =INDEX(Sheet2!$A$2:$C$13,Sheet1!F2,COLUMN(G2:H2)-COLUMN($G$2)+1)

Copy F2:H2 down until you see an error in column F (#NUM!)

Thanks very much folks. If possible, I'd rather avoid using VBA. Would there be a formula solution at all?

 

[My Aswer Is This]

I surely do not understand this solution of tosharms but I'm sure it must work.
I always wonder why so many people do not like Vba. It's like the different's in riding on a tricycle verses and driving a Sports car in my opinion. Everything today works off programming.

But then those who can write formulas like this are doing something I cannot do.

 

[St Vitus Dance]

There is a formula based approach but this is one case where either SQL or VBA is superior to the formula approach. IMO, way too much work and too much subsequent maintenance. But, if you insist...

Suppose your data are in Sheet2 in A1:C13 with row 1 being headers. The idea is to extract those rows in A:B that have empty values in C.

So, in Sheet1, enter in, say, F1:H1 the literals 'Idx' (w/o the quote), and the headers from the range in Sheet2.
In F2 enter the array formula =SMALL(IF(Sheet2!$C$2:$C$13="",ROW(Sheet2!$C$2:$C$13)-ROW(Sheet2!$C$2)+1),ROW(Sheet1!$F2)-ROW($F$2)+1) -- to enter an array formula complete data entry with CTRL+SHIFT+ENTER. If done correctly, *Excel* will show the formula enclosed in curly brackets { and }

Select G2:H2 and enter the array formula =INDEX(Sheet2!$A$2:$C$13,Sheet1!F2,COLUMN(G2:H2)-COLUMN($G$2)+1)

Copy F2:H2 down until you see an error in column F (#NUM!)

Thanks so much!

 

[tusharm]

You are welcome.

Thanks so much!