+ Reply to Thread
Results 1 to 9 of 9

merge unique column values

  1. 09-06-2019, 04:36 PM #1
    max_max
    max_max is offline
    Forum Contributor max_max's Avatar
    Join Date
    06-28-2013
    Location
    italy - venice
    MS-Off Ver
    Excel 2007
    Posts
    1,693

    merge unique column values

    Hi everyone,
    in columns A / B / C / B / D there are repeated values is possible to unify them in a single column F?
    thank you
    max
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 09-06-2019, 05:07 PM #2
    CK76
    CK76 is offline
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    MS365 Apps for enterprise
    Posts
    5,885

    Re: merge unique column values

    Personally, I'd just use PowerQuery or some other tools.

    But if you must do it in formula... you could adopt method outlined in link below. It takes advantage of R1C1 reference style.
    https://www.get-digital-help.com/200...umns-in-excel/

    Ex: In F2...
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    Confirmed as array (CTRL + SHIFT + ENTER)
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein
    Reply With QuoteRegister To Reply
  3. 09-06-2019, 05:07 PM #3
    jeffreybrown
    jeffreybrown is offline
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: merge unique column values

    How about a modification from a source found here by a fellow forum member, MickG.

    Please Login or Register  to view this content.
    HTH
    Regards, Jeff
    Reply With QuoteRegister To Reply
  4. 09-06-2019, 05:38 PM #4
    CAABYYC
    CAABYYC is offline
    Forum Contributor
    Join Date
    12-13-2013
    Location
    Calgary,AB,Canada
    MS-Off Ver
    2016,O365
    Posts
    236

    Re: merge unique column values

    Try following formula in F2, thne copy down

    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  5. 09-06-2019, 07:22 PM #5
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: merge unique column values

    With a helper cell in G1 of the attached formula =
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    Then array enter this formula in F2 and fill down.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Dave
    Reply With QuoteRegister To Reply
  6. 09-07-2019, 02:30 AM #6
    Bosco
    Bosco is offline
    Forum Contributor
    Join Date
    10-30-2003
    Location
    Singapore
    MS-Off Ver
    Excel 2019
    Posts
    197

    Re: merge unique column values

    Or try this shorter non-array formula, the result will return as same as Dave's formula.

    In F2, copied down :

    =IFERROR(SMALL($A$2:$E$20,1+SUMPRODUCT(COUNTIF($A$2:$E$20,F$1:F1))),"")

    Regards
    Bosco
    Reply With QuoteRegister To Reply
  7. 09-07-2019, 11:44 AM #7
    max_max
    max_max is offline
    Forum Contributor max_max's Avatar
    Join Date
    06-28-2013
    Location
    italy - venice
    MS-Off Ver
    Excel 2007
    Posts
    1,693

    Re: merge unique column values

    Hi, I'm trying the formulas on a macro now
    then I see which one works
    Reply With QuoteRegister To Reply
  8. 09-07-2019, 11:46 AM #8
    jeffreybrown
    jeffreybrown is offline
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: merge unique column values

    Both the formulas and the macro work, but it does not work with your other thread as it is expanded from what you are asking here.

    You probably need to stick with one thread with all the requirements.

    https://www.excelforum.com/excel-pro...ml#post5190363
    Reply With QuoteRegister To Reply
  9. 09-07-2019, 03:03 PM #9
    alansidman
    alansidman is offline
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2405 Win 11 Home 64 Bit
    Posts
    23,882

    Re: merge unique column values

    @Max
    If you have access to Excel 2010 or later then you can do this quickly using Power Query as suggested by CK76. Here is the Mcode 

    Please Login or Register  to view this content.
    Your file is also attached.
    Attached Files Attached Files
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
    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)

Similar Threads

  1. Count unique values in one column based on conditions and unique values in another column
    By margaretanne in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-19-2015, 10:22 AM
  2. Merge Duplicate Values with a Unique ID
    By afullerton in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-05-2014, 12:51 PM
  3. Merge rows according to unique IDs in column A- with multiple columns
    By inbars in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 03-26-2013, 08:03 PM
  4. Help with macro to merge/clear duplicate values and leave unique
    By Jharris360 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-18-2012, 11:38 AM
  5. Merge duplicate row cells, but combine unique cell values (Macro)
    By jcornale in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-08-2012, 06:40 PM
  6. Count Unique Values In One Column Basis Unique Values in Another Column
    By shez_raz in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 04-07-2012, 01:55 AM
  7. Merge two tables / unique values
    By ElmerS in forum Excel General
    Replies: 2
    Last Post: 07-14-2010, 11:32 AM

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