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

Excel Issue

Options
  • 31-10-2014 10:45am
    #1
    MisterCadbury
    Registered Users Posts: 140 ✭✭


    I am trying to analyse and sort a couple of arrays of data but I can't get one piece of the formula to work out, so any help you could offer would be greater appreciated.

    Basically I have a 3 columns of data. Column A is a list of each data point name and ranges from xx0001 to xx6000. Column B contains 100 random entries that correspond to the data names in column A but they are unordered. Column C then contains values that correspond to the data point names in column B. What I want to do is arrange the data in column B and C so that it is in the same order as column A. So for example data point name ( Column B) and data point value ( Column C) for the 100 values would be matched to the corresponding data point names in column A.

    Any help with this would be much appreciated


Comments

  • Options
    #2
    FSL
    Registered Users Posts: 1,456 ✭✭✭FSL


    You need to be a little more explicit.

    I read it as columns A and C are data point names, column B contains a value relating to the data point name in column C.

    Consequently column A is redundant and sorting columns B,C on column C will sort the data into data point name order.

    If that was the case then you wouldn't be asking the question hence my request for more explicit information.

    Or are you saying column A is a complete list of data point names. Columns B and C are the value, name pair in random order but there are not necessarily a one for one match in the names and you want to order B,C in name order with blank rows where there is a name in column A but not a matching one in C?


  • Options
    #3
    MisterCadbury
    Registered Users Posts: 140 ✭✭MisterCadbury


    My apologies I am not explaining it well, I have 6000 names in column A (xx0001 to xx6000), and in column be I have a random collection of 100 of the names in column B ( xx0008, xx0156 etc). Column C feature numbers that correspond to the names in column B. I have done the first part myself which was to align the names in column B to the corresponding name in column A in a new column , column D( So for example in cell name xx0667 in A is now matched to cell xx0667 in column D as XX0667 featured in the random list in column B) using the following =IF(ISNA(MATCH(A51,$B$2:$B$353,0)),"",INDEX($A$2:$B$353,MATCH(A51,$B$2:$B$353,0),2))

    The trouble arises when I want the values in column C to be spread out correctly in such a was that a specific cell in column A has the name xx0999 which if it is present in the random list in column B then it and its corresponding value in column C now features in the newly created columns D and E.

    I have attached an imaged to hopefully make it easier to get across what I am trying to. Thanks very much for having a look.
    34imdmh.jpg


  • Options
    #4
    Procasinator
    Registered Users Posts: 1,311 ✭✭✭Procasinator


    I don't fully understand what you are trying to do here. It kind of sounds like you want a VLOOKUP.


  • Options
    #5
    MisterCadbury
    Registered Users Posts: 140 ✭✭MisterCadbury


    Procasinator wrote: »
    I don't fully understand what you are trying to do here. It kind of sounds like you want a VLOOKUP.

    In the screen grab, I want in column E, the value that corresponds xx0130 in column C.

    Any advice much appreciated


  • Options
    #6
    MagicMarker
    Banned (with Prison Access) Posts: 32,865 ✭✭✭✭MagicMarker


    I've read this about 10 times and still can't figure out what you're trying to do.


  • Advertisement
  • Options
    #7
    MisterCadbury
    Registered Users Posts: 140 ✭✭MisterCadbury


    MagicMarker wrote: »
    I've read this about 10 times and still can't figure out what you're trying to do.

    I guess I am not great at explaining but If you pinpoint where I am losing you I might be able to clarify abit better or am I losing you from the start??

    I want to essentially align the data in Column B and C (Which are linked, name in column B, values in column C) to that of column A .


  • Options
    #8
    Muppet Man
    Registered Users Posts: 1,650 ✭✭✭Muppet Man


    Crikey :( have to admit, I am not getting it either.

    Show is a screen shot with just 5 or 6 rows of data with columns A to E filled in by hand....

    Forget about trying to describe the problem in terms of formulas, and just show us what you need it to look like ;)

    Muppet man


  • Options
    #9
    croo
    Moderators, Technology & Internet Moderators Posts: 1,334 Mod ✭✭✭✭croo


    try using the vlookup function, though it might not be very efficient it should work.

    ps. e.g. vlookup($A2,B:C,1,FALSE) in the D column & vlookup($A2,B:C,2,FALSE) in the E colunm


  • Options
    #10
    MisterCadbury
    Registered Users Posts: 140 ✭✭MisterCadbury


    Croo, genuinely thank you very much, your vlookups work exactly how I want only issue is I get alot of #N/A but I can get rid of them easily with the replace function.

    You have shaved hours off what I was trying to do.

    Thanks again


  • Options
    #11
    croo
    Moderators, Technology & Internet Moderators Posts: 1,334 Mod ✭✭✭✭croo


    MisterCadbury wrote: »
    Croo, genuinely thank you very much, your vlookups work exactly how I want only issue is I get alot of #N/A but I can get rid of them easily with the replace function.

    You have shaved hours off what I was trying to do.

    Thanks again

    Wrap the vlookup in an iferror with a blank ("") value to get rid of the #n/a

    PS. Procrastinator did suggest a vlookup earlier!


  • Advertisement
  • Options
    #12
    MagicMarker
    Banned (with Prison Access) Posts: 32,865 ✭✭✭✭MagicMarker


    I'm actually puzzled how you know about IF, ISNA, INDEX, MATCH functions but VLOOKUP didn't occur to you??


Advertisement