Excel or Access question. Combine row data into one field with delimiter

[Nice Guy Eddie 13515]

Data basically looks like this:

Product ID  Attribute

1                   ABC

1                   DEF

2                   ABC

3                   DEF

3                   GHI

 

I want to see the following:

Product ID    Attribute

1                     ABC,DEF

2                     ABC

3                     DEF,GHI

Note: Its not quite this simple as there can be hundreds of attributes per ID, that are well over 256 characters.   Turns into thousands of characters.  I've tried using some Access custom functions and they would only produce the first 255 characters of attributes.

[shnsajax 3591]

Have you tried using the Concatenate function?

[Nice Guy Eddie 13515]

14 minutes ago, shnsajax said:

Have you tried using the Concatenate function?

Yes but the number of rows can differ for each ID and I want to add a special delimiter character between each one.   I was hoping that a formula could 1) identify when all of the Product IDs are the same, and 2) concatenate/transpose all of the attributes in that range, and then start over with the next ID.

EDI: I've discovered a formula called TEXTJOIN in Excel that allows me to concatenate and create the delimiter but just not sure how to only run it for a dynamic range of values.

Edited July 18, 2019 by Nice Guy Eddie

[SDG 3319]

 

https://www.extendoffice.com/documents/excel/5018-excel-group-and-concatenate.html#a1

 

or you can use sql in access.  

[SDG 3319]

The best easiest option would be SAS:

Quote

data test;

     input Attribute $ ProdID ;

     datalines;

ABC 1

DEF 1

ABC 2

DEF 3

GHI 3

;

 

data test; set test;

retain ProdId Attribute;

run;

 

data want (drop=Attribute_old);

     set test (rename=(Attribute=Attribute_old));

     by ProdID;

     retain Attribute;

     length Attribute $ 255;

     if first.ProdID then Attribute = '';

           Attribute = catx(',',trim(Attribute),Attribute_old);

     if last.ProdID then output;

run;

 

I was bored during a conference call. 

[Nice Guy Eddie 13515]

thanks for the replies.

[Dahobbs 17627]

20 hours ago, Nice Guy Eddie said:

thanks for the replies.

You can merge columns using a custom query with power query (get data in excel). That may be able to get you what you want. You can always use if statements and &s in a regular excel formula. That would allow you to add your delimeters and categories wherever you like. 

 

Eg, =if(isblank(attribute1),"",attribute1&"[delimeter]")&if(isblank(attribute2),"",attribute2&"[delimeter]") ...

Just repeat for however many columns are needed for the identifier. You can use whatever logical function you want, but based on what you were saying I thought isblank may work. 

[retread 2951]

Another ides is to use INDEX and MATCH together. I've used that before to search multiple rows with the same value.

Tey this

https://stackoverflow.com/a/43352940