0

I need to find a faster way to extract data form a list. I have a file i wanted to attach so you may have an idea of what I am actually referring to, but couldn't.

On the first tab I have a list with all the individuals and in each column a data referring to them. On this list individuals may appear more than once as they the data is for the whole year for every paydate ( 2x a month). From this list ( 8000 rows), I need only the data for certain individuals ( sheet 2 of the file).

I need a solution to extract all the rows from the list for these specific individuals. In the past I have had to manually use the filter list and filter them. I have tried vlookup but that brings up only the first entry it finds.

  • May be Pivot Table might help. – patkim Dec 28 '18 at 19:47
0

A VLOOKUP with a creative approach should do the trick. If you add a VLOOKUP formula on the first tab to lookup the names (or whatever the unique identifier is) from the second tab you will get a mix of VLOOKUP'd values and #N/A. If you then filter to exclude the #N/As you will be left with the records of the certain individuals and can modify them there or copy & paste them to a different worksheet/workbook.

| improve this answer | |
0

Try to approach it the other wat around: instead of showing the rows from tab 1 on tab 2, try to identify on tab 1 which persons should be shown.

Suppose the names are in column A of tab 1 and tab 2, add a column to to table of tab 1 with a vlookup to tab 2: =NOT(ISNA(VLOOKUP(A1, tab2!$A$1:$A$999, 0, FALSE))). This gives TRUE if the name is in tab 2 (not #NA) and FALSE if it isn’t.

Next, you can set a filter on the table in tab 1. Or a bit more fancy, create a pivot table from tab 1 with a page filter that selects only those persons that appear on tab 2.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.