Excel - extracting data based on another list

Question

I have an Excel worksheet with two columns  name ID  and then another list that is a subset of the names only from the larger aforementioned list  I want to go through the subset list and then pull the data from the larger list  name ID  and put it somewhere else   essentially only grabbing the data from the larger list if the name is on the subset   I tried using Filters  but couldn t get it work  Thoughts   Thanks

User · Answer

Have you tried Advanced Filter  Using your short list as the  Criteria  and long list as the  List Range   Use the options   Filter in Place  and  Unique Values    You should be presented with the list of unique values that only appear in your short list   Alternatively  you can paste your Unique list to another location  on the same sheet   if you prefer  Choose the option  Copy to another Location  and in the  Copy to  box enter the cell reference  say F1  where you want the Unique list   Note  this will work with the two columns  name ID  too  if you select the two columns as both  Criteria  and  List Range

User · Answer

I have been hasseling with that as other folks have   I used the criteria    countif matchingList C2  0  where matchingList is the list that i am using as a filter   have a look at this  http   www youtube com watch v x47VFMhRLnM amp list PL63A7644FE57C97F4 amp index 30  The trick i found is that normally you would have the column heading in the criteria matching the data column heading  this will not work for criteria that is a formula   What I found was if I left the column heading blank for only the criteria that has the countif formula in the advanced filter works  If I have the column heading i e  the column heading for column C2 in my formula example then the filter return no output   Hope this helps

User · Answer

I couldn t get the first method to work  and I know this is an old topic  but this is what I ended up doing for a solution    IF ISNA MATCH A1 B B 0    Not Matched   A1    Basically  MATCH A1 to Column B exactly  the 0 stands for match exactly to a value in Column B    ISNA tests for  N A response which match will return if the no match is found   Finally  if ISNA is true  write  Not Matched  to the selected cell  otherwise write the contents of the matched cell

User · Answer

New Excel versions   IF ISNA VLOOKUP A1 B B 1 FALSE      A1   Older Excel versions   IF ISNA VLOOKUP A1 B B 1 FALSE      A1   That is   If the value of A1 exists in the B column  display it here  If it doesn t exist  leave it empty

[excel] Excel - extracting data based on another list

Examples related to excel

Examples related to extract