How to Reconcile two values splitted in multiple rows in same column

Feroz90

Board Regular
Joined
Apr 25, 2019
Messages
52
Hi Excel Gurus,

How to Reconcile two values splitted in multiple rows in same column

In the below example you may see, for each day, we have same amount in two columns but these amount are splitted in third column, how can we match these. Any suggestions.

Example:

Monday10002000500
Monday10002000500
Monday10002000500
Monday10002000500
Monday10002000500
Monday10002000250
Monday10002000250
Tuesday29001100750
Tuesday29001100750
Tuesday29001100600
Tuesday29001100500
Tuesday29001100500
Tuesday29001100450
Tuesday29001100450
Wednesday15002500750
Wednesday15002500750
Wednesday150025001000
Wednesday150025001000
Wednesday15002500250
Wednesday15002500250
Thurday225027501000
Thurday22502750750
Thurday22502750250
Thurday225027502000
Thurday225027501000
Friday84501550450
Friday84501550550
Friday845015502000
Friday845015503000
Friday845015501000
Friday845015501000
Friday845015502000

<colgroup><col><col span="3"></colgroup><tbody>
</tbody>

Thanks
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
like this?

DayAmt1Amt2Amt3DayAmt1Amt2Amt3
Monday
1000​
2000​
500​
Monday
7000​
14000​
3000​
Monday
1000​
2000​
500​
Tuesday
20300​
7700​
4000​
Monday
1000​
2000​
500​
Wednesday
9000​
15000​
4000​
Monday
1000​
2000​
500​
Thurday
11250​
13750​
5000​
Monday
1000​
2000​
500​
Friday
59150​
10850​
10000​
Monday
1000​
2000​
250​
Monday
1000​
2000​
250​
Tuesday
2900​
1100​
750​
Tuesday
2900​
1100​
750​
Tuesday
2900​
1100​
600​
Tuesday
2900​
1100​
500​
Tuesday
2900​
1100​
500​
Tuesday
2900​
1100​
450​
Tuesday
2900​
1100​
450​
Wednesday
1500​
2500​
750​
Wednesday
1500​
2500​
750​
Wednesday
1500​
2500​
1000​
Wednesday
1500​
2500​
1000​
Wednesday
1500​
2500​
250​
Wednesday
1500​
2500​
250​
Thurday
2250​
2750​
1000​
Thurday
2250​
2750​
750​
Thurday
2250​
2750​
250​
Thurday
2250​
2750​
2000​
Thurday
2250​
2750​
1000​
Friday
8450​
1550​
450​
Friday
8450​
1550​
550​
Friday
8450​
1550​
2000​
Friday
8450​
1550​
3000​
Friday
8450​
1550​
1000​
Friday
8450​
1550​
1000​
Friday
8450​
1550​
2000​

or like this:

DayAmt1Amt2Amt3
Monday
1000​
2000​
500​
Monday
1000​
2000​
250​
Tuesday
2900​
1100​
750​
Tuesday
2900​
1100​
600​
Tuesday
2900​
1100​
500​
Tuesday
2900​
1100​
450​
Wednesday
1500​
2500​
750​
Wednesday
1500​
2500​
1000​
Wednesday
1500​
2500​
250​
Thurday
2250​
2750​
1000​
Thurday
2250​
2750​
750​
Thurday
2250​
2750​
250​
Thurday
2250​
2750​
2000​
Friday
8450​
1550​
450​
Friday
8450​
1550​
550​
Friday
8450​
1550​
2000​
Friday
8450​
1550​
3000​
Friday
8450​
1550​
1000​

if not show example of expected result
 
Last edited:
Upvote 0
Or potentially this?

FGHI
1Monday100020003000
2Tuesday290011004000
3Wednesday150025004000
4Thurday225027505000
5Friday8450155010000

<tbody>
</tbody>
 
Upvote 0
Hi All,

What I need should be like, If we calculate First 4 values in 3rd column, we get 2000, the next 3 values we will get 1000, same as that you can see for other days too, please assist how to do that.

Thanks
 
Upvote 0
Hey,

Can you manually write up a table of what you expect Monday to look like? I don't understand the criteria if I'm honest.
 
Upvote 0
Hi,

If you can see the above example, I need to sum the Amt 3 column to match Amt 1 & Amt 2 Column, lets assume, the first 4 rows (500,500,500,500) totals and gives 2000 which matches with Amt 2 of Monday, the next 3 rows (500,250,250) totals and gives 1000 which matches with Amt 1 of Monday, the same way every days will match either of the column total.

Please let me know if you do not understand, since I dont know how to insert the excel table, I had to explain like this.

Thanks
 
Upvote 0
Hi All,

This is best I can do,

DaysAmt 1Amt 2Amt 3TotalExample
Monday100020005002000500 + 500 + 500 + 500
Monday100020005002000500 + 500 + 500 + 500
Monday100020005002000500 + 500 + 500 + 500
Monday100020005002000500 + 500 + 500 + 500
Monday100020005001000500 + 250 + 250
Monday100020002501000500 + 250 + 250
Monday100020002501000500 + 250 + 250
Tuesday290011007502900750 + 750 + 500 + 450 +450
Tuesday290011007502900750 + 750 + 500 + 450 +450
Tuesday290011006001100600 + 500
Tuesday290011005001100600 + 500
Tuesday290011005002900750 + 750 + 500 + 450 +450
Tuesday290011004502900750 + 750 + 500 + 450 +450
Tuesday290011004502900750 + 750 + 500 + 450 +450
Wednesday150025007501500750 + 750
Wednesday150025007501500750 + 750
Wednesday15002500100025001000 + 1000 + 250 +250
Wednesday15002500100025001000 + 1000 + 250 +250
Wednesday1500250025025001000 + 1000 + 250 +250
Wednesday1500250025025001000 + 1000 + 250 +250
Thursday22502750100022501000 + 250 +1000
Thursday225027507502750750 + 2000
Thursday2250275025022501000 + 250 +1000
Thursday2250275020002750750 + 2000
Thursday22502750100022501000 + 250 +1000
Friday845015504508450450 + 2000 + 3000 + 1000 + 2000
Friday845015505501550550 + 1000
Friday8450155020008450450 + 2000 + 3000 + 1000 + 2000
Friday8450155030008450450 + 2000 + 3000 + 1000 + 2000
Friday8450155010001550550 + 1000
Friday8450155010008450450 + 2000 + 3000 + 1000 + 2000
Friday8450155020008450450 + 2000 + 3000 + 1000 + 2000

<colgroup><col><col span="4"><col></colgroup><tbody>
</tbody>

Please check
 
Upvote 0
I give up, I lost logic in the first two steps for each day :(

maybe try Solver?

have a nice day
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,947
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top