+ Reply to Thread
Results 1 to 13 of 13

Extracting data from multiple columns to one column without duplicates

  1. 12-31-2012, 01:43 PM #1
    blue_clouds_mountain
    blue_clouds_mountain is offline
    Registered User
    Join Date
    12-29-2012
    Location
    Latakia, Syria
    MS-Off Ver
    Excel 2010
    Posts
    61

    Extracting data from multiple columns to one column without duplicates

    HI EVERYBODY
    Here is the problem:
    I have MULTIPLE and SEPARATEd columns each contains hundreds of data some thing like:

    column A
    egg
    bread
    apple
    milk
    butter

    column c
    bread
    milk
    jam

    column e
    apple
    orange
    banana

    and i need to create column that represents ALL that data WITHOUT duplicates

    i.e like this:

    column g
    egg
    bread
    apple
    milk
    butter
    jam
    orange
    banana
    is that possible using an excel formula? honestly, i know very very little about vba...
    is it possible to make the last column (g) dynamic? i mean to refersh automatically when adding data to the source columns (a, c and e)?
    thanks a lot!
    Reply With QuoteRegister To Reply
  2. 12-31-2012, 01:54 PM #2
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,944

    Re: Extracting data from multiple columns to one column without duplicates

    try this in G...
    =IFERROR(INDEX($AC$1:$A$31,MATCH(0,COUNTIF($G$1:G1,$A$1:$A$31),0)),IFERROR(INDEX($B$1:$B$31,MATCH(0,COUNTIF($G$1:G1,$B$1:$B$31),0)),IFERROR(INDEX($C$1:$C$31,MATCH(0,COUNTIF($G$1:G1,$C$1:$C$31),0)),"")))

    if you adjust the ranges to cater for additional data, it should update automatically
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  3. 12-31-2012, 02:14 PM #3
    blue_clouds_mountain
    blue_clouds_mountain is offline
    Registered User
    Join Date
    12-29-2012
    Location
    Latakia, Syria
    MS-Off Ver
    Excel 2010
    Posts
    61

    Re: Extracting data from multiple columns to one column without duplicates

    thank you very much for reply
    that formula is too complex for me to understand! i cann't even dare to dream about writing formula like this!
    yet i tried but excel is telling me "circular refrences" and always returns "0" in column g...
    so, please, is there any suggestion?
    Reply With QuoteRegister To Reply
  4. 12-31-2012, 03:13 PM #4
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,944

    Re: Extracting data from multiple columns to one column without duplicates

    oops I just noticed your ranges atr not A, B, C....try this instead...
    =IFERROR(INDEX($AC$1:$A$31,MATCH(0,COUNTIF($G$1:G1,$A$1:$A$31),0)),IFERROR(INDEX($C$1:$C$31,MATCH(0,COUNTIF($G$1:G1,$C$1:$C$31),0)),IFERROR(INDEX($E$1:$E$31,MATCH(0,COUNTIF($G$1:G1,$E$1:$E$31),0)),""))

    if that still doesnt work for you, perhaps it would be easier to help you if you uploaded a sample workbook, showing what data you are working with, a few examples of what your expected outcome would be, and how you would arrive at that (remove any confidential info if necessary).

    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    Reply With QuoteRegister To Reply
  5. 12-31-2012, 04:01 PM #5
    djapigo
    djapigo is offline
    Valued Forum Contributor
    Join Date
    09-10-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    929

    Re: Extracting data from multiple columns to one column without duplicates

    FD,

    Your formula is brilliant... (little correction: =IFERROR(INDEX($A$1:$A$31,MATCH... )

    However, I only get 0's... for some reason, when I break down your formula (highlight and F9), I get the correct info, but if entering the formula, I also only see 0's...

    Any ideas? Is there an option setting that we should be using? add-in? sorry, I'm grasping at straws here...

    Dennis
    Reply With QuoteRegister To Reply
  6. 12-31-2012, 04:11 PM #6
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,944

    Re: Extracting data from multiple columns to one column without duplicates

    the formula is set up for it to start in G2, so make sure that is where it starts
    Reply With QuoteRegister To Reply
  7. 12-31-2012, 04:24 PM #7
    djapigo
    djapigo is offline
    Valued Forum Contributor
    Join Date
    09-10-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    929

    Re: Extracting data from multiple columns to one column without duplicates

    FD,

    Sorry, now I'm simply getting "blank" results...

    This is how I have it set up, A1=egg, A2=bread, A3=apple, etc. C1=bread, C2=milk, C3=jam, E1=apple, E2=orange, E3=banana, G2=your formula, which I'm getting "blank" results...

    If I simplify your formula and remove the 2 other IFERRORs, it's still giving blanks, but if I highlight and press F9, I get "eggs"... really strange... =IFERROR(INDEX($A$1:$A$31,MATCH(0,COUNTIF($G$1:G1,$A$1:$A$31),0)),"")

    I have a feeling I doing something silly, but it's really confusing me...
    Reply With QuoteRegister To Reply
  8. 12-31-2012, 04:28 PM #8
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,944

    Re: Extracting data from multiple columns to one column without duplicates

    ummm i think i forgot 1 tint lil matter....that is an array formula and has to be entered using CRTL shift enter, not just enter, sorry (
    Reply With QuoteRegister To Reply
  9. 12-31-2012, 04:33 PM #9
    djapigo
    djapigo is offline
    Valued Forum Contributor
    Join Date
    09-10-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    929

    Re: Extracting data from multiple columns to one column without duplicates

    Gosh, I thought I tried that... but that was when I had the formula in G1... oops...

    It works now... great formula!
    Reply With QuoteRegister To Reply
  10. 12-31-2012, 04:42 PM #10
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,944

    Re: Extracting data from multiple columns to one column without duplicates

    Happy to help. If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below)
    Reply With QuoteRegister To Reply
  11. 12-31-2012, 04:50 PM #11
    djapigo
    djapigo is offline
    Valued Forum Contributor
    Join Date
    09-10-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    929

    Re: Extracting data from multiple columns to one column without duplicates

    Hey FD,

    Sorry I didn't start the thread... I figured if I couldn't get it to work, I had a feeling blue_clouds was gonna a harder time figuring it out... I'll let them mark it "solved" if they can get it to work... sorry, I just got curious about your formula...
    Reply With QuoteRegister To Reply
  12. 12-31-2012, 04:52 PM #12
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,944

    Re: Extracting data from multiple columns to one column without duplicates

    lol sorry, I guess i lost track of who the OP was, thought it was you... thanks for the assist tho
    Reply With QuoteRegister To Reply
  13. 12-31-2012, 05:46 PM #13
    blue_clouds_mountain
    blue_clouds_mountain is offline
    Registered User
    Join Date
    12-29-2012
    Location
    Latakia, Syria
    MS-Off Ver
    Excel 2010
    Posts
    61

    Re: Extracting data from multiple columns to one column without duplicates

    I'm blue_clouds_mountain and I'm the one who started the thread! that great formula worked with me like magic!! and let me admit it, I'd never figure it out my self !!
    I've to thank you all specially FDibbins !!
    Merry Christmas and Happy New Year
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1