Help with Excel

U_Fig · 18-09-2014 7:30am #1

I have a bit of an issue at the moment on a project I'm working on.

Basically in the project I take a list of data and and thought various stages extract parts of it.

I get in the raw data and using a table of the data I need use vlookup to extract data corresponding data into a new sheet. (Example I have a list of names and using the vlookup to extract and age for each of the name)

The problem I am having is that some of the data doesn't have a values as its not in the list (Ie the name is in the raw data but not on my list) and I get an error #N/A in the cell where the age should be)

At the moment I am manually filterin out the cells that contain #N/A and pasting the values into a new sheet to continue

Is there any way to use a process to do this. I'm trying to take as much manual work out of the file as I can.

I tried using a macro with anvanced filtering but cannot seem to get it working.

Is there a way to either extract rows that do not contain errors in any column in that row and Copt it to a new sheet or remove the rows that have an error in any colum.

Thanks

MatureStudent · 18-09-2014 11:27am

There is a function "IFERROR", put your VLOOKUP in the value, and an error message in the Value_if_error.

18-09-2014 11:29am

if(isna((vlookup blablabla)),0,vlookup blablabla))

returns 0 if there would have been an n/a, otherwise returns normal vlookup

U_Fig · 18-09-2014 6:12pm

bluewolf wrote: »

if(isna((vlookup blablabla)),0,vlookup blablabla))

returns 0 if there would have been an n/a, otherwise returns normal vlookup

Thanks I'll try that

MatureStudent · 18-09-2014 6:55pm

Could I suggest you use IFERROR which will replace any error found rather than ISNA which will replace only N/A. Its also easier to enter and read since the VLOOKUP is entered once rather than twice with ISNA.

Have you tried Advanced Filter to select the required rows and copy to a new location? If you have an error message on any row where the VLOOKUP fails, you can select only rows which do not contain this message for each column.

U_Fig · 18-09-2014 8:56pm

MatureStudent wrote: »

Could I suggest you use IFERROR which will replace any error found rather than ISNA which will replace only N/A. Its also easier to enter and read since the VLOOKUP is entered once rather than twice with ISNA.

Have you tried Advanced Filter to select the required rows and copy to a new location? If you have an error message on any row where the VLOOKUP fails, you can select only rows which do not contain this message for each column.

thanks i'll try that.

i tried advanced filtering but i don't know how this works with error codes

MatureStudent · 18-09-2014 9:25pm

Hope this makes sense :

Your VLOOKUP will be something like this :
=IFERROR(VLOOKUP(A2,Range1,2,FALSE),"Not found")
If the VLOOKUP is unsuccessful "Not found" will appear, otherwise age in your example.

Then in the Advanced Filter Criteria row, under the Age heading, enter <>Not found. This will select any value other than Not found. You can do this in as many columns as necessary.

Try it and see how you get on.

U_Fig · 18-09-2014 10:36pm

thanks yeah i think that will work great.

Help with Excel

Comments