Create a list based on criteria in another list

[infinitedrifter]

I apologize in advance as I suspect this has been answered before but I'm just not searching for the correct terms.


I have a table in worksheet with a list of policy information (status, account #, name, date, etc). In a second worksheet, I'd like to autogenerate list of all accounts that have a status of "Cx". (I can't use filtering because the second spreadsheet is for someone else to use. Using a basic IF statement works as long as the first worksheet is sorted so all of the Cx accounts are at the top. VLOOKUP is a nogo, obviously...I tried INDEX w MATCH but it only returns the 1st account as well.


The list on the second worksheet can return just the account number or the whole row of data (if this isn't possible, I can always use VLOOKUP to pull that data in based on the account number).


This should be simple, right? I don't know why it is giving me such a hard time. Thanks for any help.

 

[dan_l]

A pivot table can work ok: Account Numbers should be your columns, and a count of account numbers could be your data. Set the filter to status and CX. Cheap workaround, I know.


Also you could do it with MS query or vba


Some VBA:

Sub GetAListBasedOnSomeCriteria()

Dim ticker As Integer

Dim cell As Range

ticker = 1


For Each cell In Sheets("sheet1").Range("A:A")

If cell.Value = "cx" Then

Sheets("sheet2").Range(Sheets("sheet2").Cells(ticker, 1), Sheets("sheet2").Cells(ticker, 1)).Value = cell.Offset(0, 1).Value

ticker = ticker + 1

End If

Next cell

End Sub

 

[infinitedrifter]

Thanks for two excellent solutions. I'm just starting to learn both pivottables & VBA so I will have fun playing with these a bit.


Thanks again!