Pull some rows from one worksheet and compile into another

[Steven975]

I'm looking for a way to automatically take anything with the status "Pending" and compile a list of them on a seperate another worksheet. Any help is much appreciated!

Worksheet One (INPUT)

Name	Amount	Status
John	150,000	Approved
Chris	350,000	Pending
Steven	215,000	Funded
Shawn	320,000	Pending
James	125,000	Funded

Worksheet Two (OUTPUT)

Chris	350,000	Pending
Shawn	320,000	Pending

 

[hajiali]

Sheet 2:
COLUMN A

=IFERROR(INDEX(Sheet1!$A$2:$A$100,AGGREGATE(15,6,(ROW(Sheet1!$C$2:$C$100)-1)/(Sheet1!$C$2:$C$100="PENDING"),ROWS(Sheet1!$C$2:$C2))),"")

COLUMN B

=IFERROR(INDEX(Sheet1!$B$2:$B$100,AGGREGATE(15,6,(ROW(Sheet1!$C$2:$C$100)-1)/(Sheet1!$C$2:$C$100="PENDING"),ROWS(Sheet1!$C$2:$C2))),"")

COLUMN C

=IFERROR(INDEX(Sheet1!$C$2:$C$100,AGGREGATE(15,6,(ROW(Sheet1!$C$2:$C$100)-1)/(Sheet1!$C$2:$C$100="PENDING"),ROWS(Sheet1!$C$2:$C2))),"")

 

[RasGhul]

Setup like this you can choose a status, and should work well for large data sets.

Book1
	A	B	C	D	E	F	G	H	I	J
1	Name	Amount	Status		Test	Pending	Criteria	Approved	Funding	Pending
2	Chris	350,000	Pending
3	Shawn	320,000	Pending
OUTPUT

Cell Formulas
Range	Formula
A2	A2	{=IF(ROWS($A$2:A2)>COUNTIF(INPUT!$C$2:$C$6,OUTPUT!$F$1),"",INDEX(INPUT!A$2:A$6,SMALL(IF(INPUT!$C$2:$C$6=OUTPUT!$F$1,ROW(INPUT!A$2:A$6)-ROW(INPUT!A$2)+1),ROWS($A$2:A2))))}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

Cells with Data Validation
Cell	Allow	Criteria
F1	List	=$H$1:$J$1

 

[Steven975]

Now it looks like I will need to pull from multiple Worksheets to compile this list. Will these techniques still apply?

 

[RasGhul]

Yes it's possible but requires more setup. How many sheets are you extracting from?

 

[Steven975]

There will be 20 or so?

 

[Steven975]

Is at an option to upload the file so you can see what I am referring to?

 

[RasGhul]

I've achieved it with 4 sheets but 20 sheets would be better with a VBA option. I can't assist with VBA for this scenario.

 

[sandy666]

look at PowerQuery aka Get&Transform

 

[Peter_SSs]

There will be 20 or so?

For a vba approach try this. I have assumed that the results go on an existing sheet called 'Summary' (change the code to suit your results sheet name) and that those results will appear below any existing data in column A of that Summary sheet. Test in a copy of your workbook.

Sub Status_Summary()
  Dim ws As Worksheet
  Dim a As Variant, b As Variant
  Dim i As Long, k As Long
 
  Const StatusOfInterest As String = "Pending" '<- Could change this to Approved etc
 
  For Each ws In Worksheets
    If ws.Name <> "Summary" Then
      a = ws.Range("A2", ws.Range("C" & ws.Rows.Count).End(xlUp)).Value
      ReDim b(1 To UBound(a), 1 To 3)
      k = 0
      For i = 1 To UBound(a)
        If a(i, 3) = StatusOfInterest Then
          k = k + 1
          b(k, 1) = a(i, 1): b(k, 2) = a(i, 2): b(k, 3) = StatusOfInterest
        End If
      Next i
      If k > 0 Then Sheets("Summary").Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(k, 3).Value = b
    End If
  Next ws
End Sub