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
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
4,313
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:

tyija1995

Well-known Member
Joined
Feb 26, 2019
Messages
648
Or potentially this?

FGHI
1Monday100020003000
2Tuesday290011004000
3Wednesday150025004000
4Thurday225027505000
5Friday8450155010000

<tbody>
</tbody>
 

Feroz90

Board Regular
Joined
Apr 25, 2019
Messages
52
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
 

tyija1995

Well-known Member
Joined
Feb 26, 2019
Messages
648
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.
 

Feroz90

Board Regular
Joined
Apr 25, 2019
Messages
52
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
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
4,313
is it hard to post example of expected result in table form?
 

Feroz90

Board Regular
Joined
Apr 25, 2019
Messages
52
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
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
4,313
I give up, I lost logic in the first two steps for each day :(

maybe try Solver?

have a nice day
 
Last edited:

Forum statistics

Threads
1,086,030
Messages
5,387,389
Members
402,058
Latest member
disgracept

Some videos you may like

This Week's Hot Topics

Top