+ Reply to Thread
Results 1 to 12 of 12

Help with formula: Concat All on Excel 2007

  1. #1
    Registered User
    Join Date
    06-28-2016
    Location
    Sydney, Australia
    MS-Off Ver
    2007
    Posts
    5

    Help with formula: Concat All on Excel 2007

    Hi,

    I have a list of products.
    Column A = Unique SKUS
    Column D = Product Family



    On a new column, I want to figure out a formula where I could get all the Sku of the same family separated by |

    So the result would be that all of the families are grouped together




    I have 1000+ products to sort out.
    Can someone please help?
    I tried:
    a version of Concat all that I found =ConcatAll(IF(IF($D$3:$D$1099=D3,$A$2:$A$1099,"")=A2,"",IF($C$3:$D$1099=C2,$A$2:$A$1099,"")),"|") but I just got a name error. I don't know how to make this work can anyone help?
    I've attached the data I'm working with
    Attached Files Attached Files

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Help with formula: Concat All on Excel 2007

    ConcatAll isn't a built-in Excel function it's a user defined function and if you Google it you'll find various versions kicking around.

    Actually I don't think you'll find a version that'll do exactly what you want.

    Here's some code that might work though it's not a UDF, just put it in a module and run it.
    Please Login or Register  to view this content.
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    06-28-2016
    Location
    Sydney, Australia
    MS-Off Ver
    2007
    Posts
    5

    Re: Help with formula: Concat All on Excel 2007

    I tried to insert this into a module by doing Alt then F11 and pasting the text under insert module, I then dragged down my formula and still got a name error. What am I doing wrong?

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Help with formula: Concat All on Excel 2007

    To run the code put it in a module in the workbook you posted and, making sure Sheet1 is active, press F5.

    You should end up with a new sheet with the family name in column A and the concatenated SKUs in column B.

  5. #5
    Registered User
    Join Date
    06-28-2016
    Location
    Sydney, Australia
    MS-Off Ver
    2007
    Posts
    5

    Re: Help with formula: Concat All on Excel 2007

    Is there a way to do this but keep the product Sku, the data in column A of the file that I uploaded earlier, in the final version after the macro runs?

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Help with formula: Concat All on Excel 2007

    I'm not quite sure what you mean.

    The destination of the output could be changed but I don't quite see how it would fit in with the existing data.

    Where exactly would the unique list of families and there associated SKUs go?

  7. #7
    Registered User
    Join Date
    06-28-2016
    Location
    Sydney, Australia
    MS-Off Ver
    2007
    Posts
    5

    Re: Help with formula: Concat All on Excel 2007

    Is it possible to have the data read Column A:Product Sku, Column B: Product Family Name, and Column C: Related Product Sku? So I can v look up against my CSV Product Import File. Sorry to be a bother.

  8. #8
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Help with formula: Concat All on Excel 2007

    Sorry I'm a bit confused.

    I thought you wanted to create a concatenated list of SKUs for each unique family listed in column D.

    Is that not what you want?

    If it is what you want, how would you use it in a VLOOKUP?

  9. #9
    Registered User
    Join Date
    06-28-2016
    Location
    Sydney, Australia
    MS-Off Ver
    2007
    Posts
    5

    Re: Help with formula: Concat All on Excel 2007

    That is what I want. I want the 3 columns that I described but the current data that I've attached ultimately needs to be pasted/merged with my much larger excel file. That's why my boss said the weird thing about v look up. All I need is a way to put the product sku column with the macro that gave the family name and related product sku. Sorry for the confusion.

  10. #10
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Help with formula: Concat All on Excel 2007

    Do you mean that you want a column on the original sheet that is a concatenated list of the SKUs for the family of each row?

    For example, for rows 3-20 this column would containing the concatenated SKU list for Aegean?

  11. #11
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,841

    Re: Help with formula: Concat All on Excel 2007

    Is this what you want?
    Attached Files Attached Files
    <----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left

    If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
    I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.

  12. #12
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,841

    Re: Help with formula: Concat All on Excel 2007

    Or rather this perhaps. The item on that row is not in the list here.
    Attached Files Attached Files

+ 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. How do I write a VBA to Bold text in a concat formula?
    By kimmiey in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-09-2016, 09:22 AM
  2. When I CopyFromRecordset a MYSQL CONCAT statement, it puts 256 blank rows into Excel table
    By Coastal_view in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-11-2015, 09:28 AM
  3. Replies: 6
    Last Post: 04-24-2014, 08:41 AM
  4. [SOLVED] Concat help
    By anuraag205 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-02-2013, 02:13 AM
  5. Replies: 1
    Last Post: 05-25-2012, 11:54 AM
  6. Replies: 1
    Last Post: 03-04-2012, 12:03 AM
  7. Excel VBA String Concat Problem
    By javajoemorgan in forum Excel General
    Replies: 3
    Last Post: 06-08-2009, 01:51 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