MegaSack DRAW - This year's winner is user - rgwb
We will be in touch
I have to create a filter for this:
Column A has ID numbers
Column B has any 1 of 20 variables
So column A has multiple duplicates, as the variables in column B may or may not correspond to specific IDs listed in Column A
How can I produce a list of column A where varialble 1, 5 and 17 ( say) exist in isolation ( as in, as a set)?
I'm not quite sure what you want.
But don't you just want to sort your data AtoZ by one of the columns?
When you say "in isolation", are you also implying "not duplicated"?
I can't quite picture what you're trying to achieve, but have you looked at Pivot tables?
do you want to remove duplicates or sort?
I want column A to show me which IDs have variable 1, 5 and 17 together.
The raw list has duplicates of the ID in column A as it has variable 1, 5 and 17.
Does that make it any clearer?
Filtering and pivot tables only show me which IDs have any of the variables, and I have a very long list, so I don't want to have to go through it manually removing single variable entries. I just want the instances where the ID has all 3 variables.
vlookup or hlookup functions might sort it. Have a look at help for details on how to combine queries.
cheers
In column C this formula will give you the ID's that match 1,5 or 17
=IF(OR(B1=1,B1=5,B1=17)=TRUE,A1,"")
Copy that down you data and filter from that column to hid blanks. It's a start, and there's probably a way of consolidating the list of ID's (which I figure will contain duplicates which I haven't thought of yet...
IanB...thanks a mighty bunch
If you only want to look at duplicates, use a pivot table to identify instances of your IDs. Copy the results & paste speical; values on sheet 2 of your workbook. Delete out IDs from this list with an instance of 1.
in Column C of your original worksheet enter =VLOOKUP(A1,Sheet2!A$1:B$35,2,0), where A2:B35 = the boundaries of the copied pivot table. Copy & paste special; values column C.
Sort data by column c, then column a. Your duplicate IDs will all be at the top of your spreadsheet. Then use Ianb's formula...
Can you post a subset of your data and example of desired results?
Can't follow your description...