+ Reply to Thread
Results 1 to 4 of 4

Return a combined unique list into one from other two lists.

  1. #1
    Registered User
    Join Date
    08-19-2020
    Location
    Dubai
    MS-Off Ver
    Office365
    Posts
    82

    Return a combined unique list into one from other two lists.

    Hi,

    Required a formula help to combined a lists of data in to one list from 2 lists of data contains in Column A to D, list should be necessitate a unique combined list without any duplicates and blank cells.

    Sample sheet with desired results enclosed.


    Thanks for your kind help.

  2. #2
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL (COVID allowing)
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,377

    Re: Return a combined unique list into one from other two lists.

    Put this array formula in G6.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Important: this is an array formula so needs to be entered using Ctrl-Shift-Enter not just Enter. You will know the array is active when you see curly brackets { } appear around your formula - don't try to enter them yourself. If you do not Ctrl-Shift-Enter an array formula you will get an error or a clearly incorrect result (in this case, you'll get '^^end of list^^'). If that happens, just click into the formula bar and try again.

    Then drag down until you get '^^end of list^^'.

    To get the new serial numbers, enter this in F5 and drag down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    You can replace "^^end of list^^" with just "" if you'd prefer blanks. I only use this so that you can see easily where the list stops.

    I hope that does what you need.


    By the way, for future reference, please note that what you have asked for is a combined distinct list not unique list.
    Unique values are the items that appear in a dataset only one time.
    Distinct values are all the items in a list ignoring repeated values.
    For example, in this list:
    A B C A B A D
    the unique values are C and D (they only appear once)
    the distinct values are A B C and D.
    Regards,
    Aardigspook

    Recently moved house, internationally, during COVID (!) so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
    If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
    You don't need to give me rep if I helped, but a thank-you is nice.

  3. #3
    Registered User
    Join Date
    08-19-2020
    Location
    Dubai
    MS-Off Ver
    Office365
    Posts
    82

    Re: Return a combined unique list into one from other two lists.

    Thank you Aardigspook for your great formula solution.

  4. #4
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL (COVID allowing)
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,377

    Re: Return a combined unique list into one from other two lists.

    You're welcome, glad I could help and thanks for the rep.

    If you're satisfied that your question has been answered, then please take a moment to mark the thread as Solved so others know there's an answer here (instructions are in my sig). Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 7
    Last Post: 02-25-2020, 10:04 AM
  2. 2-Column List of Unique Distinct COMBINED Values
    By probladerunner in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-19-2016, 09:05 PM
  3. [SOLVED] Rearranging data from combined list to separate lists
    By VBADoofus in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-21-2014, 01:32 PM
  4. [SOLVED] Use selections from two data validation lists to lookup and return a combined result
    By lethonius2k in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 09-20-2013, 11:39 AM
  5. Import data from two lists into one combined list
    By ghladik in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-07-2012, 02:53 PM
  6. Combined list from two lists
    By kumawat_s in forum Excel General
    Replies: 4
    Last Post: 06-09-2010, 12:00 PM

Tags for this Thread

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