Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie
Hi there,
There is an issue with role permissions that is being worked on at the moment.
If you are having trouble with access or permissions on regional forums please post here to get access: https://www.boards.ie/discussion/2058365403/you-do-not-have-permission-for-that#latest

Help with Excel

  • 18-09-2014 6:30am
    #1
    Registered Users, Registered Users 2 Posts: 893 ✭✭✭U_Fig


    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


Comments

  • Registered Users, Registered Users 2 Posts: 33 MatureStudent


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


  • Posts: 0 CMod ✭✭✭✭ Layla Limited Rumba


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

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


  • Registered Users, Registered Users 2 Posts: 893 ✭✭✭U_Fig


    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


  • Registered Users, Registered Users 2 Posts: 33 MatureStudent


    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.


  • Registered Users, Registered Users 2 Posts: 893 ✭✭✭U_Fig


    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


  • Advertisement
  • Registered Users, Registered Users 2 Posts: 33 MatureStudent


    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.


  • Registered Users, Registered Users 2 Posts: 893 ✭✭✭U_Fig


    thanks yeah i think that will work great.


Advertisement