+ Reply to Thread
Results 1 to 12 of 12

[solved]combine 4 columns into one non-repeating list

  1. #1
    Forum Contributor
    Join Date
    01-06-2011
    Location
    cleveland
    MS-Off Ver
    Excel 2007
    Posts
    223

    [solved]combine 4 columns into one non-repeating list

    in columns d through g i have the following data:

    Please Login or Register  to view this content.
    what would be an efficient way to make a non repeating concise list off all of these components? i have been able to do similar tasks before but i have only looked at once column and i dont know the best way to tackle this one. any help would be appreciated. thank you!
    Last edited by kamelkid2; 04-09-2012 at 02:05 PM.

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,406

    Re: combine 4 columns into one non-repeating list

    Hi,

    First concatenate all the four columns into a single new helper column. i.e. =D1&"|"&E1&"/"&F1&"\"&G1
    Then use Data Filter Advanced to extract a unique list from the helper column
    Finally use string slicing to split the cells in the unique single column back into four columns with formulae like: - assuming J is the column with the unique values

    =LEFT(J1,FIND("|",J1)-1)
    =MID(J1,FIND("|",J1)+1,FIND("\",J1)-1-FIND("|",J1)-2)
    =MID(J1,FIND("/",J1)+1,FIND("\",J1)-1-FIND("|",J1)-2)
    =RIGHT($J$1,LEN(J1)-FIND("\",$J$1))
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Contributor
    Join Date
    01-06-2011
    Location
    cleveland
    MS-Off Ver
    Excel 2007
    Posts
    223

    Re: combine 4 columns into one non-repeating list

    as the list will be updated and expanded with new variables on a daily basis i dont want the end user to have to data filter. i would prefer to make this idiot proof as possible and just compile the list for them automatically if possible.

  4. #4
    Forum Contributor
    Join Date
    01-06-2011
    Location
    cleveland
    MS-Off Ver
    Excel 2007
    Posts
    223

    Re: combine 4 columns into one non-repeating list

    i have been able to use helper columns to extract a unique number for the variable for all four of the columns in 4 separate cells: so now im faced with:

    Please Login or Register  to view this content.
    each of the values corresponds to a particular variable. is there a way i can match these into a unique list?

  5. #5
    Forum Expert icestationzbra's Avatar
    Join Date
    01-07-2004
    MS-Off Ver
    2007, 2010
    Posts
    1,417

    Re: combine 4 columns into one non-repeating list

    i had a similar requirement and i utilised a formula provided on another website to derive unique values.

    see if the attached file helps...

  6. #6
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,406

    Re: combine 4 columns into one non-repeating list

    Hi,

    It might have helped if you had mentioned that in your original. Looking at you post # 4 I don;t understand what you mean. Upload an example workbook and manually add the results that you expect to see. We can then see what's needed.

  7. #7
    Forum Contributor
    Join Date
    01-06-2011
    Location
    cleveland
    MS-Off Ver
    Excel 2007
    Posts
    223

    Re: combine 4 columns into one non-repeating list

    this is a pretty cool piece of code but when i put it into my sheet i would get #name errors. i believe it is because i have those cells populated as a product of other if statements so i will have to try something else apparently. thank you for help though

  8. #8
    Forum Expert icestationzbra's Avatar
    Join Date
    01-07-2004
    MS-Off Ver
    2007, 2010
    Posts
    1,417

    Re: combine 4 columns into one non-repeating list

    if you are talking about post #5, then that code runs on the basis of a Named Range called data_columns.

    you will have to create such a Named Range on your spreadsheet.

    upload a sample workbook if you are not able to figure out how.

    the positioning of the Uniques column, as also the formula underneath, is KEY.

  9. #9
    Forum Contributor
    Join Date
    01-06-2011
    Location
    cleveland
    MS-Off Ver
    Excel 2007
    Posts
    223

    Re: combine 4 columns into one non-repeating list

    logistics2.xlsx

    this is what i am currently working off of. i put in my desired result (it doesnt have to be in that exact order - just an equivalent)

    worksheet calcsheet is where the question arises. columns a through d is where i did my unique name list so those can be disregarded. i have updated my original code to only display unique variables by looking in the previous columns for the e though h columns, but my issue is that i need to populate each of the entries across those 4 rows into a concise list using non vba code.

    i apologize to the clarity of this request. is it possible to do this?

  10. #10
    Forum Contributor
    Join Date
    01-06-2011
    Location
    cleveland
    MS-Off Ver
    Excel 2007
    Posts
    223

    Re: combine 4 columns into one non-repeating list

    Quote Originally Posted by icestationzbra View Post
    if you are talking about post #5, then that code runs on the basis of a Named Range called data_columns.

    you will have to create such a Named Range on your spreadsheet.

    upload a sample workbook if you are not able to figure out how.

    the positioning of the Uniques column, as also the formula underneath, is KEY.

    i see. i have got a small scale working now. thank you very much. my only question is how well will this perform when the list becomes increasingly large (meaning upwards of several thousand rows). this is a large scale trending observation that this will be assisting in so efficiency is very much desired

  11. #11
    Forum Expert icestationzbra's Avatar
    Join Date
    01-07-2004
    MS-Off Ver
    2007, 2010
    Posts
    1,417

    Re: combine 4 columns into one non-repeating list

    here you go...

    the list of unique values in column O will keep on burgeoning until row 101...

    if you reach that point, just drag the formula further south.

  12. #12
    Forum Contributor
    Join Date
    01-06-2011
    Location
    cleveland
    MS-Off Ver
    Excel 2007
    Posts
    223

    Re: combine 4 columns into one non-repeating list

    Quote Originally Posted by icestationzbra View Post
    here you go...

    the list of unique values in column O will keep on burgeoning until row 101...

    if you reach that point, just drag the formula further south.
    this is a more elegant way of completing what i needed, however it just became too slow and bogged down (i tested it at 250 rows) so i came up with a series of helper columns to finally get what i was looking for. it doesnt look pretty but the end user wont be seeing this tab so it is what it is. in case you were interested:

    logistics2.xlsx

    thank you all for your help though. i learned something very handy today that im sure ill need in the future.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1