Trying to Create macro that will merge rows/columns

**mochmaster** · 11-15-2022, 11:54 AM

I am trying to create a macro that emulates some functionality found in the ablebits/kutools add-ons.

Essentially I have a table in which many of the columns (can be transposed to rows if needed) are the same except one.

What I want to do is combine all the columns/rows that share a common value (lets say claim ID) and merge that unlike column into one delimited by a comma [space].

Note: Sometimes the columns will have 0, sometimes it will have like 5 that need to be merged. Can vary from entry to entry.

Edit: Included a brief example.
Example Doc.xlsx

**MarvinP** · 11-15-2022, 11:58 AM

Hi mochmaster and welcome to the forum,

We need a sample workbook to really solve your question. See the yellow area above to attach a file.

**mochmaster** · 11-15-2022, 11:59 AM

Yup that was my bad, just added one. Took me a second to figure out the attachment system.

**MarvinP** · 11-15-2022, 12:26 PM

Hi mochmaster,

I'd try a Transpose on your data to work with it easier. See the attached to get my possible answer.
Example Doc Transpose is Better.xlsx

If you really needed the items in a single cell then a TextJoin might help with that.

**mochmaster** · 11-15-2022, 12:36 PM

Yeah as it stands right now, I am using the ablebits add-on. I transpose the data, run the tool, then transpose it back. Unfortunately I need the data all in one cell as i have it laid out (it makes more sense in the actually doc).

Textjoin does seem handy, but in my brain I'm not sure how to get around the circular referencing. Like if i need rows 2-5 to be concatenated, how do i have it include row 2 in row 2?

**MarvinP** · 11-16-2022, 12:37 AM

Hey mochmaster,

What alebits formula are you using? Would you accept a VBA solution?
Maybe one of the smart Gurus can come up with a better answer than mine.

**mochmaster** · 11-16-2022, 12:42 AM

Hey Marvin,

VBA would be perfect. The name of the ablebits functionality is called merge duplicates. More info can be found here : (www)ablebits.com/excel-combine-rows/index.php (too new to post link properly)

Thanks

**HansDouwe** · 11-16-2022, 01:04 AM

Or try a formula:

Formula:

Please Login or Register  to view this content.

**mochmaster** · 11-17-2022, 11:13 AM

Thanks for the input. I could see this being a potential work around. Ideally I'd have it filter in place. Also I'm going to need to disect this a little to figure out how to adapt it for variable amounts of columns and or include more rows than just the two.