Jump to content

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


Recommended Posts

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.

Link to comment
Share on other sites

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 by Nice Guy Eddie
Link to comment
Share on other sites

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. 

  • Like 1
Link to comment
Share on other sites

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. 

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.



×
×
  • Create New...