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

#### Feroz90

##### Board Regular
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:

 Monday 1000 2000 500 Monday 1000 2000 500 Monday 1000 2000 500 Monday 1000 2000 500 Monday 1000 2000 500 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

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

Thanks

### Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

#### sandy666

##### Well-known Member
like this?

 Day Amt1 Amt2 Amt3 Day Amt1 Amt2 Amt3 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:

 Day Amt1 Amt2 Amt3 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
Or potentially this?

 F G H I 1 Monday 1000 2000 3000 2 Tuesday 2900 1100 4000 3 Wednesday 1500 2500 4000 4 Thurday 2250 2750 5000 5 Friday 8450 1550 10000

<tbody>
</tbody>

#### sandy666

##### Well-known Member
@tyija1995

right, I forgot about third option

#### Feroz90

##### Board Regular
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
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
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
is it hard to post example of expected result in table form?

#### Feroz90

##### Board Regular
Hi All,

This is best I can do,

 Days Amt 1 Amt 2 Amt 3 Total Example Monday 1000 2000 500 2000 500 + 500 + 500 + 500 Monday 1000 2000 500 2000 500 + 500 + 500 + 500 Monday 1000 2000 500 2000 500 + 500 + 500 + 500 Monday 1000 2000 500 2000 500 + 500 + 500 + 500 Monday 1000 2000 500 1000 500 + 250 + 250 Monday 1000 2000 250 1000 500 + 250 + 250 Monday 1000 2000 250 1000 500 + 250 + 250 Tuesday 2900 1100 750 2900 750 + 750 + 500 + 450 +450 Tuesday 2900 1100 750 2900 750 + 750 + 500 + 450 +450 Tuesday 2900 1100 600 1100 600 + 500 Tuesday 2900 1100 500 1100 600 + 500 Tuesday 2900 1100 500 2900 750 + 750 + 500 + 450 +450 Tuesday 2900 1100 450 2900 750 + 750 + 500 + 450 +450 Tuesday 2900 1100 450 2900 750 + 750 + 500 + 450 +450 Wednesday 1500 2500 750 1500 750 + 750 Wednesday 1500 2500 750 1500 750 + 750 Wednesday 1500 2500 1000 2500 1000 + 1000 + 250 +250 Wednesday 1500 2500 1000 2500 1000 + 1000 + 250 +250 Wednesday 1500 2500 250 2500 1000 + 1000 + 250 +250 Wednesday 1500 2500 250 2500 1000 + 1000 + 250 +250 Thursday 2250 2750 1000 2250 1000 + 250 +1000 Thursday 2250 2750 750 2750 750 + 2000 Thursday 2250 2750 250 2250 1000 + 250 +1000 Thursday 2250 2750 2000 2750 750 + 2000 Thursday 2250 2750 1000 2250 1000 + 250 +1000 Friday 8450 1550 450 8450 450 + 2000 + 3000 + 1000 + 2000 Friday 8450 1550 550 1550 550 + 1000 Friday 8450 1550 2000 8450 450 + 2000 + 3000 + 1000 + 2000 Friday 8450 1550 3000 8450 450 + 2000 + 3000 + 1000 + 2000 Friday 8450 1550 1000 1550 550 + 1000 Friday 8450 1550 1000 8450 450 + 2000 + 3000 + 1000 + 2000 Friday 8450 1550 2000 8450 450 + 2000 + 3000 + 1000 + 2000

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

#### sandy666

##### Well-known Member
I give up, I lost logic in the first two steps for each day

maybe try Solver?

have a nice day

Last edited: