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

[OllieClarke · Accepted solution]

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

[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

[OllieClarke · Accepted solution]

@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

[bubblyjai1982]

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