+ Reply to Thread
Results 1 to 12 of 12

Array to concatenate columns and then return unique list

  1. #1
    Registered User
    Join Date
    12-21-2015
    Location
    United States
    MS-Off Ver
    2016
    Posts
    62

    Array to concatenate columns and then return unique list

    Hi everyone,

    Second time having to ask for help with something fairly unique. Maybe an array isn't even the solution and it would be more like a udf, but I thought it was worth a shot.

    Anyway, I am looking for something that would return a unique list of values after concatenation of columns. I am familiar with arrays to pull unique lists, even from multiple columns, but not one that would do the actual concatenation and then pull the unique values from that. The issue I am hitting is that I am not able to utilize helper columns in this scenario.

    Breakdown would be something similar to the attachment.

    Help is always appreciated,

    Thank you,
    Attached Files Attached Files
    Last edited by Skiptomylou; 11-01-2017 at 10:26 PM.

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,957

    Re: Array to concatenate columns and then return unique list

    Well, one way of doing it is with a helper column (that can be hidden) and a pivot table.
    Attached Files Attached Files
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Forum Expert
    Join Date
    10-06-2017
    Location
    drevni ruchadlo
    MS-Off Ver
    old
    Posts
    2,279

    Re: Array to concatenate columns and then return unique list

    so-so ... also with helper column ... remember about ctrl+shift+enter, then copy down

    conc-file.xlsx.jpg

  4. #4
    Registered User
    Join Date
    12-21-2015
    Location
    United States
    MS-Off Ver
    2016
    Posts
    62

    Re: Array to concatenate columns and then return unique list

    Sorry fellas, no helper columns in this scenario. I just realized I made a typo on my original post that said I could. I meant to say not able to use them.

    Post was edited to reflect this.
    Last edited by Skiptomylou; 11-01-2017 at 10:30 PM.

  5. #5
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Array to concatenate columns and then return unique list

    You can try with PowerQuery - no additional columns, no formulas, no vba

    (PowerQuery, PowerPivot - free add-ins from MS site)
    Attached Files Attached Files

  6. #6
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 V 2505
    Posts
    13,757

    Re: Array to concatenate columns and then return unique list

    One way to do this without helper columns would be with an array formula such as this.
    If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.
    Array entered in J5 and filled down.
    Formula: copy to clipboard
    Please Login or Register  to view this content.




    I
    J
    4
    Return List Staring here
    5
    Blue10square1
    Blue10square1
    6
    Red10square1
    Red10square1
    7
    Orange10square1
    Orange10square1
    8
    Green10square1
    Green10square1
    9
    Puple10square1
    Purple10square1
    10
    Silver10square1
    Silver10square1
    11
    12
    13
    Dave

  7. #7
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Array to concatenate columns and then return unique list

    @Dave
    It looks like a snake

  8. #8
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (Version 2503 Build 16.0.18604.20000) 64-bit
    Posts
    3,999

    Re: Array to concatenate columns and then return unique list

    or can try a non array formula

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

  9. #9
    Registered User
    Join Date
    12-21-2015
    Location
    United States
    MS-Off Ver
    2016
    Posts
    62

    Re: Array to concatenate columns and then return unique list

    Guys, both of these solutions work perfectly! Apparently it was the Proper function I was lacking the knowledge of, which seems to have made all the difference in my original thought on how to make this happen.

    Much appreciated from both of you! Marked as solved

  10. #10
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 V 2505
    Posts
    13,757

    Re: Array to concatenate columns and then return unique list

    Quote Originally Posted by sandy666 View Post
    @Dave
    It looks like a snake
    That's because it is!! I'm not real fond of concatenating ranges, but sometimes ...

  11. #11
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 V 2505
    Posts
    13,757

    Re: Array to concatenate columns and then return unique list

    Quote Originally Posted by shukla.ankur281190 View Post
    or can try a non array formula

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Nice one.

    For what it's worth also works non array ... MATCH(0,INDEX(COUNTIFS($L$4:L4,$B$2:$B$13&$C$2:$C$13&$D$2:$D$13&$E$2:$E$13),),0)

  12. #12
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 V 2505
    Posts
    13,757

    Re: Array to concatenate columns and then return unique list

    @ Skiptomylou,

    You are welcome. Thank you for the feedback, rep and marking your thread Solved.

+ 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. [SOLVED] Return and concatenate values array formula
    By BWellman in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 06-29-2017, 02:40 PM
  2. [SOLVED] vba to concatenate data and return multiples values from unique list
    By JEAN1972 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 01-30-2017, 11:27 AM
  3. Array formula to return list of unique items from selected columns only
    By jlawton1 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-23-2016, 04:53 AM
  4. [SOLVED] Look in 2 columns return a list of Difference Unique Text/Value
    By nickusami in forum Excel General
    Replies: 7
    Last Post: 10-29-2015, 03:22 PM
  5. [SOLVED] Concatenate Unique Columns
    By rebeccad in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 06-14-2013, 03:44 PM
  6. [SOLVED] Populate a unique list from two columns- array formulas to slow!
    By strud in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-08-2013, 11:44 AM
  7. Replies: 2
    Last Post: 04-12-2010, 12:35 PM

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