Extract Data From A List

[ExcelRoy]

Hi All,

I am looking for a formula to extract data from a list dependent on what is in columns D

ie the list on sheet 1 will be

A1 : Parts
B1 : Description
C1 : Cost
D1 : Status

On sheet 2 I need

A1 : Parts only with "Ordered","Partially Received" or "Pending" status
B1 : Description with "Ordered","Partially Received" or "Pending" status
C1 : Cost with "Ordered","Partially Received" or "Pending" status
D1 : Status with "Ordered","Partially Received" or "Pending" status

Many thanks

 

[Rick Rothstein]

Give this macro a try...

[table="width: 500"]
[tr]
	[td]Sub ExtractDataDependingOnValueInColumnD()
  Dim R As Long, LastRow As Long, UnusedCol As Long
  With Sheets("Sheet1")
    LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
    UnusedCol = .Cells(1, Columns.Count).End(xlToLeft).Column + 1
    Cells(1, UnusedCol).Value = "X"
    .Cells(2, UnusedCol).Resize(LastRow - 1) = Evaluate(Replace(Replace("IF((@D2:D#=""Ordered"")+(@D2:D#=""Partially Received"")+(@D2:D#=""Pending""),""X"","""")", "#", LastRow), "@", "'" & .Name & "'!"))
    Intersect(.Columns(UnusedCol).SpecialCells(xlConstants).EntireRow, Columns("A:D")).Copy Sheets("Sheet2").Range("A1")
    Columns(UnusedCol).Clear
  End With
End Sub[/td]
[/tr]
[/table]

 

[ExcelRoy]

Wow, many thanks Rick