Jump to content
View in the app

A better way to browse. Learn more.

Surly Horns

A full-screen app on your home screen with push notifications, badges and more.

To install this app on iOS and iPadOS
  1. Tap the Share icon in Safari
  2. Scroll the menu and tap Add to Home Screen.
  3. Tap Add in the top-right corner.
To install this app on Android
  1. Tap the 3-dot menu (⋮) in the top-right corner of the browser.
  2. Tap Add to Home screen or Install app.
  3. Confirm by tapping Install.
Football ... Basketball ... Baseball ... Other Sports ... Futbol ... 🤫995🤫 ... Gambling ... Movies & TV ... Music ... Hobbies ... Lulz ... Food & Travel ... Daily Texan ... Business & Markets ... Cloak Room ... Help ... For Sale ... Board Discussion ... Advertise... Tailgate Donations

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

Featured Replies

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.

  • Author
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

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. 

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. 

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...
Football ... Basketball ... Baseball ... Other Sports ... Futbol ... 🤫995🤫 ... Gambling ... Movies & TV ... Music ... Hobbies ... Lulz ... Food & Travel ... Daily Texan ... Business & Markets ... Cloak Room ... Help ... For Sale ... Board Discussion ... Advertise... Tailgate Donations

Configure browser push notifications

Chrome (Android)
  1. Tap the lock icon next to the address bar.
  2. Tap Permissions → Notifications.
  3. Adjust your preference.
Chrome (Desktop)
  1. Click the padlock icon in the address bar.
  2. Select Site settings.
  3. Find Notifications and adjust your preference.