Merge scramble data

bubblyjai1982

Hi All,

Can anyone please help me on below !!

The data is scrambled (not sequence order) and want to merge the cells based on Account and Payment date.

The Total Amount is with different sets and want to merge irrespective of no.of rows but with one condition Payment date and Account should match.

I have attached Input file where I'm looking for the column "Merged Cells"

Thanks

Merge Cells.xlsx

Workflow

Accepted answers

OllieClarke

Hi @bubblyjai1982

You can use a summarize tool to do this. Just group by Account and Date, and then sum amount. This will total the amount for each unique combination of Account and Date.

If you want the original structure of your data, then you can join this back on (I removed the nulls first)

Hope that helps,

Ollie

bubblyjai.yxzp

OllieClarke

@bubblyjai1982

Sorry, I didn't see your replies as you didn't tag me.

Here's 2 approaches which get what you want. The Top container will work if you don't have a total amount in your data. The bottom container relies on the total amount column and repeats it only on the first row

Hope that helps,

Ollie

merge2.yxmd

All comments

OllieClarke

Hi @bubblyjai1982

You can use a summarize tool to do this. Just group by Account and Date, and then sum amount. This will total the amount for each unique combination of Account and Date.

If you want the original structure of your data, then you can join this back on (I removed the nulls first)

Hope that helps,

Ollie

bubblyjai.yxzp

bubblyjai1982

Sorry I missed to add one more clause, I want to bifurcate the row based on Payment date and Account… don’t want to combine the data.

so without joining the rows, want to merge the Total Amount

bubblyjai1982

Sorry I missed to add one more clause, I want to bifurcate the row based on Payment date and Account… don’t want to combine the data. so without joining the rows, want to merge the Total Amount

bubblyjai1982

Let me reframe my query, sorry for the confusion

I have set of records scramble in different rows and want to merge without removing the null rows.

The null rows created based on common account, payment date and currency. Hence don't want to remove the Null rows.

If I use summarize and group tool, the output data will remove the Null rows which I want to keep as is. so without removing Null want to merge.

I have attached both input and output sheets

Thanks

Merge Cells.xlsx

OllieClarke

Hope that helps,

Ollie

merge2.yxmd

bubblyjai1982

Thank you so much, it is full proof solution. It helped me and my query is resolved.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703