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 :)
Solved! Go to Solution.
Probably a few different ways to do this,
Can you provide the excel document? that would be helpful,
Shane
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!
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 .
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!