VLOOKUP and extract #N/A rows

[realtoast]

Hello experts! I hope my question is as easy for you as it seems to me:

I've got two tables I'm comparing. Both tables have the index, StudentID, a 10-digit number. I'm using vlookup to compare the tables to find which students are missing from either table (Table A compared to Table B, and Table B compare to Table A). So far, so good. I now want to extract just those records of the students not found (#N/A rows). Ultimately, I want a pivot table of only #N/A rows. Seems easy enough. However, because there's no value in that #N/A field, I'm not able to find a way to identify those records and extract them.

One solution I've thought of would be to delete all rows with a value, leaving just the #N/A rows. What would be this process/VBA script?

Thanks!

 

[James006]

Hello,

To extract records based on a given criteria (#N/A) you could take a look at Autofilter ...

Hope this will help

 

[sturnusek]

The easiest way of doing this I can think of instead of using formulas to pull the N/A’s through, if you create an actual table in the range of where your information is (use CTRL+T to create) and go to Data tab and use Get Data>From other sources>From Table/Range (make sure you clicked on your actual table before you followed this). In the power query, just use the autofilter to leave only N/A on and click Close&Load and choose second option, and unload it in new tab or where you want it on original sheet. That will automatically pull all N/A’s through, once it offloads and you want it to refresh, just right click on it and refresh.

 

[sandy666]

Power Query is not necessary to do that, enough normal table and from the text filter: equals : #N/A

 

[realtoast]

Thanks for the filter and auto-filter ideas. This does work when I filter from the pivot table. Thank you!

 

[James006]

You are welcome