Combining rows with no reference into a single cell

[robin2021]

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 number	Comment
xxxx9999	issues with rec1 revert fo X for guidance
	24 May - transaction x failed,
	overpayment to sam $100
xxxx7985	No issues
xxxx3241	Rec complete no issue
	Escalate - $8000 27 July no history?
xxxx4963	Transaction history sent for audit
	Reconciled - no issue
	Escalate to company sec - FX transaction
	Escalate to company sec - FX transaction
	Query charges with bank
xxxx4898	no 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 number	Comment
xxxx9999	issues with rec1 revert fo X for guidance, 24 May - transaction x failed, overpayment to sam $100
xxxx7985	No issues
xxxx3241	Rec complete no issue, Escalate - $8000 27 July no history?
xxxx4963	Transaction history sent for audit, Reconciled - no issue, Escalate to controller - FX transaction, Escalate to controller - FX transaction, Query charges with bank
xxxx4898	no issues

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

Many thanks :)

[sp210708]

Probably a few different ways to do this,

Can you provide the excel document? that would be helpful,

Shane

[DataNath · Accepted solution]

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 solution]

Hi, @robin2021 

As your full want :

Input
Account number	Comment
xxxx9999	issues with rec1 revert fo X for guidance
	24 May - transaction x failed,
	overpayment to sam $100
xxxx7985	No issues
xxxx3241	Rec complete no issue
	Escalate - $8000 27 July no history?
xxxx4963	Transaction history sent for audit
	Reconciled - no issue
	Escalate to company sec - FX transaction
	Escalate to company sec - FX transaction
	Query charges with bank
xxxx4898	no issues
Output
Account number	Comment
xxxx9999	issues with rec1 revert fo X for guidance,24 May - transaction x failed,overpayment to sam $100
xxxx7985	No issues
xxxx3241	Rec complete no issue,Escalate - $8000 27 July no history?
xxxx4963	Transaction history sent for audit,Reconciled - no issue,Escalate to company sec - FX transaction,Escalate to company sec - FX transaction,Query charges with bank
xxxx4898	no 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 .

[robin2021]

Hi @DataNath & @flying008 

Many thanks for responding on this... both solutions worked really well and I was able to integrate it into the larger script i am working on.

Looking back on yesterdays afternoon of 'pain' - it was the summarize tool which I was missing! 

Thanks again!