Marcster
05-18-2009, 11:44 AM
Hi all,
I'm trying to see if it's possible to create a macro (or maybe even a formula?) that automatically carrys out a reconciliation.
The data is in rows and is not split into two tables.
I've been trying to come up with a solution but just end up going round in circles :( .
This is how the reconciliation should work:
The matched rows should have the same Desc 2 (Col H), where there are numbers in these cells,one of the two rows will have .1 on the end (Col H)
The matched rows selected must equal zero (Col G)
The matched rows Trans type should also be the same (Col I)
The data range is C18:J 'LastRow' - 'LastRow' will change with each reconciliation.
Cannot use other columns or rows including the blank cols B, C and D. But can use another sheet if needed.
Columns E, F, H and I cells should be 'trimmed' before use as spaces before and after do appear in these cells.
In column A show what can be matched against what, i.e. MATCH - 1, MATCH - 2, MATCH - 3 etc
No colours, conditional formatting or data validation to be used on the data area.
Data row starts on row 18.
So going on the above, this is what should happen:
In column A:
IF
TRIM(H18) & TRIM (I18) ABS(G18)
Matches another row, match these off against each other
OR
TRIM(H18) & TRIM(I18) & ABS(G18)
Matches another row where one of these rows has
TRIM(H18) extra .1 in cell, then match these off against each other
ELSE
blank, these are the unreconciled items.
The attached spreadsheet has sample data and more info.
Thanks.
I'm trying to see if it's possible to create a macro (or maybe even a formula?) that automatically carrys out a reconciliation.
The data is in rows and is not split into two tables.
I've been trying to come up with a solution but just end up going round in circles :( .
This is how the reconciliation should work:
The matched rows should have the same Desc 2 (Col H), where there are numbers in these cells,one of the two rows will have .1 on the end (Col H)
The matched rows selected must equal zero (Col G)
The matched rows Trans type should also be the same (Col I)
The data range is C18:J 'LastRow' - 'LastRow' will change with each reconciliation.
Cannot use other columns or rows including the blank cols B, C and D. But can use another sheet if needed.
Columns E, F, H and I cells should be 'trimmed' before use as spaces before and after do appear in these cells.
In column A show what can be matched against what, i.e. MATCH - 1, MATCH - 2, MATCH - 3 etc
No colours, conditional formatting or data validation to be used on the data area.
Data row starts on row 18.
So going on the above, this is what should happen:
In column A:
IF
TRIM(H18) & TRIM (I18) ABS(G18)
Matches another row, match these off against each other
OR
TRIM(H18) & TRIM(I18) & ABS(G18)
Matches another row where one of these rows has
TRIM(H18) extra .1 in cell, then match these off against each other
ELSE
blank, these are the unreconciled items.
The attached spreadsheet has sample data and more info.
Thanks.