Combining rows with no reference into a single cell

Question

Hi all

Greetings from the UK Midlands!

Really hope you can help with this...

I have the following data in an Excel (note the random spacing):

Account numberCommentxxxx9999issues with rec1 revert fo X for guidance 24 May - transaction x failed, overpayment to sam $100      xxxx7985No issues  xxxx3241Rec complete no issue Escalate - $8000 27 July no history?    xxxx4963Transaction history sent for audit Reconciled - no issue Escalate to company sec - FX transaction Escalate to company sec - FX transaction Query charges with bank  xxxx4898no issues

and I am trying to write a script to combine the comments into one cell against the account number... the output would look something like this:

Account numberCommentxxxx9999issues with rec1 revert fo X for guidance, 24 May - transaction x failed, overpayment to sam $100xxxx7985No issuesxxxx3241Rec complete no issue, Escalate - $8000 27 July no history?xxxx4963Transaction history sent for audit, Reconciled - no issue, Escalate to controller - FX transaction, Escalate to controller - FX transaction, Query charges with bankxxxx4898no issues

I've been going round in circles with this all afternoon, so any help would be greatly appreciated...

Many thanks :)

DataNath · Accepted Answer

Hey @robin2021, I'd first of all use a Multi-Row Formula tool in order to fill down the missing account numbers, until a new one is found. After that, we can simply remove the empty rows and use a Summarize tool in order to Concatenate all of the (currently) scattered comments, into a single cell, separated by a comma:

Hope this helps!

flying008 · Accepted Answer

Hi, @robin2021

As your full want :

Input Account numberCommentxxxx9999issues with rec1 revert fo X for guidance 24 May - transaction x failed, overpayment to sam $100      xxxx7985No issues  xxxx3241Rec complete no issue Escalate - $8000 27 July no history?    xxxx4963Transaction history sent for audit Reconciled - no issue Escalate to company sec - FX transaction Escalate to company sec - FX transaction Query charges with bank  xxxx4898no issues  Output Account numberCommentxxxx9999issues with rec1 revert fo X for guidance,24 May - transaction x failed,overpayment to sam $100xxxx7985No issuesxxxx3241Rec complete no issue,Escalate - $8000 27 July no history?xxxx4963Transaction history sent for audit,Reconciled - no issue,Escalate to company sec - FX transaction,Escalate to company sec - FX transaction,Query charges with bankxxxx4898no issues

BTW, if you don't care the row order and multiple comma like ',,' in string after join,  you can use brief solution of @DataNath .