combine 2 tables of data into one based on date

[Guest]

I get two downloads from my bank - one with credit information the other with
debits. Both tables contain columns that include the date, description, and
dollar amount. The number of rows differs each day, for example one day may
have 3 deposits while another day only has 1. Also the number of rows on the
debit table also varies each day and may have more or less rows than the
corresponding day on the credit table.
I would like to combine the two tables into one based on date. All the data
can be on different rows and in different columns if that is easier. For
example if the credit table for 8/29/06 looks like this:
A B C
8/29/06 Lockbox 100.00
8/29/06 Brnch Dep 150.00
8/29/06 Lockbox 200.00
8/30/06 Lockbox 175.00

and the debit table looks like this:
A B C
8/29/06 check cleared 95.00
8/30/06 check cleared 75.00
8/30/06 misc withdrawal 200.00

The combined table can look like this:
A B C D
8/29/06 Lockbox 100.00
8/29/06 Brnch Dep 150.00
8/29/06 Lockbox 200.00
8/29/06 check cleared 95.00
8/30/06 Lockbox 175.00
8/30/06 check cleared 75.00
8/30/06 misc withdrawal 200.00

Or All the amounts can be in column C - whatever is easier.

Does anyone have any ideas?

Thanks in advance

 

[Guest]

Here's a non-array formulas set-up which can deliver the requested results ..

A sample construct is available at:
http://cjoint.com/?iEiD3gbIZw
Auto-combine 2 tables into 1 n auto-sort by date.xls
[Link above is good for 14 days]

Assume the source credit table is in sheet: C, cols A to C, data from row2
down. Likewise for the source debit table in sheet: D

In a new sheet: X (say),

In A2:
=IF('C'!A2="","",'C'!A2+ROW()/10^10)

In B2:
=IF(D!A2="","",D!A2+ROW()/10^10+ROW())

In C2
=IF(ROW(A1)>COUNT($A:$A),IF(ROW(A1)-COUNT($A:$A)>COUNT($B:$B),"",INDEX(D!A:A,MATCH(SMALL($B:$B,ROW(A1)-COUNT($A:$A)),$B:$B,0))),INDEX('C'!A:A,MATCH(SMALL($A:$A,ROW(A1)),$A:$A,0)))
Copy C2 to E2

In F2:
=IF(C2="","",C2+ROW()/10^10)

(Leave A1:F1 blank)

In G2:
=IF(ROW(A1)>COUNT($F:$F),"",INDEX(C:C,MATCH(SMALL($F:$F,ROW(A1)),$F:$F,0)))
Copy G2 to I2

Then just select A2:I2 and copy down to cover the max aggregate number of
rows expected in both the credit and debit tables. If we expect a max of 100
lines per table (say), then copy down by at least 200 lines. Format col G as
date, col I as currency to taste. Hide away cols A to F, or mask it by
formatting the font in white.

Cols G to I will return the required results, ie an auto-combination of
lines from both the credit and debit source tables, with lines sorted in
ascending order by the dates. All results will appear neatly bunched at the
top.

See sheet: X (2) in the sample:
If desired, Debit values can be returned in col I as negative values for
easier distinction with credit values. Just amend the formula in E2 (after
copying across from C2) to make the debit values returned from the part: ..
INDEX(D!C:C,.. as negative values, viz. make it in E2 as:

=IF(ROW(C1)>COUNT($A:$A),IF(ROW(C1)-COUNT($A:$A)>COUNT($B:$B),"",
-INDEX(D!C:C,MATCH(SMALL($B:$B,ROW(C1)-COUNT($A:$A)),$B:$B,0))),
INDEX('C'!C:C,MATCH(SMALL($A:$A,ROW(C1)),$A:$A,0)))

[ Insert a negative sign in front: ...,-INDEX(D!C:C ... ]

(No change to the rest of the construct)

---

 

[Guest]

Thanks, Max. I'll give this a try. Tim

Here's a non-array formulas set-up which can deliver the requested results ..

A sample construct is available at:
http://cjoint.com/?iEiD3gbIZw
Auto-combine 2 tables into 1 n auto-sort by date.xls
[Link above is good for 14 days]

Assume the source credit table is in sheet: C, cols A to C, data from row2
down. Likewise for the source debit table in sheet: D

In a new sheet: X (say),

In A2:
=IF('C'!A2="","",'C'!A2+ROW()/10^10)

In B2:
=IF(D!A2="","",D!A2+ROW()/10^10+ROW())

In C2:
=IF(ROW(A1)>COUNT($A:$A),IF(ROW(A1)-COUNT($A:$A)>COUNT($B:$B),"",INDEX(D!A:A,MATCH(SMALL($B:$B,ROW(A1)-COUNT($A:$A)),$B:$B,0))),INDEX('C'!A:A,MATCH(SMALL($A:$A,ROW(A1)),$A:$A,0)))
Copy C2 to E2

In F2:
=IF(C2="","",C2+ROW()/10^10)

(Leave A1:F1 blank)

In G2:
=IF(ROW(A1)>COUNT($F:$F),"",INDEX(C:C,MATCH(SMALL($F:$F,ROW(A1)),$F:$F,0)))
Copy G2 to I2

Then just select A2:I2 and copy down to cover the max aggregate number of
rows expected in both the credit and debit tables. If we expect a max of 100
lines per table (say), then copy down by at least 200 lines. Format col G as
date, col I as currency to taste. Hide away cols A to F, or mask it by
formatting the font in white.

Cols G to I will return the required results, ie an auto-combination of
lines from both the credit and debit source tables, with lines sorted in
ascending order by the dates. All results will appear neatly bunched at the
top.

See sheet: X (2) in the sample:
If desired, Debit values can be returned in col I as negative values for
easier distinction with credit values. Just amend the formula in E2 (after
copying across from C2) to make the debit values returned from the part: ..
INDEX(D!C:C,.. as negative values, viz. make it in E2 as:

=IF(ROW(C1)>COUNT($A:$A),IF(ROW(C1)-COUNT($A:$A)>COUNT($B:$B),"",
-INDEX(D!C:C,MATCH(SMALL($B:$B,ROW(C1)-COUNT($A:$A)),$B:$B,0))),
INDEX('C'!C:C,MATCH(SMALL($A:$A,ROW(C1)),$A:$A,0)))

[ Insert a negative sign in front: ...,-INDEX(D!C:C ... ]

(No change to the rest of the construct)

---

I get two downloads from my bank - one with credit information the other with
debits. Both tables contain columns that include the date, description, and
dollar amount. The number of rows differs each day, for example one day may
have 3 deposits while another day only has 1. Also the number of rows on the
debit table also varies each day and may have more or less rows than the
corresponding day on the credit table.
I would like to combine the two tables into one based on date. All the data
can be on different rows and in different columns if that is easier. For
example if the credit table for 8/29/06 looks like this:
A B C
8/29/06 Lockbox 100.00
8/29/06 Brnch Dep 150.00
8/29/06 Lockbox 200.00
8/30/06 Lockbox 175.00

and the debit table looks like this:
A B C
8/29/06 check cleared 95.00
8/30/06 check cleared 75.00
8/30/06 misc withdrawal 200.00

The combined table can look like this:
A B C D
8/29/06 Lockbox 100.00
8/29/06 Brnch Dep 150.00
8/29/06 Lockbox 200.00
8/29/06 check cleared 95.00
8/30/06 Lockbox 175.00
8/30/06 check cleared 75.00
8/30/06 misc withdrawal 200.00

Or All the amounts can be in column C - whatever is easier.

Does anyone have any ideas?

Thanks in advance

 

[Guest]

You're welcome.
Thanks for posting back ..

 

[Guest]

Max -
It worked perfectly. Thanks for your help.
Tim

 

[Guest]

Delighted to hear that!
My pleasure`, Tim.