Need some assistance with nesting formulas. We perform reconciliation using an SQL-based system. We then use a MS Access based report system to view various reports. We don’t have access codes to perform any queries outside of the canned reports, so we use the closest canned report, save it as a text file, and open it up in Excel 2007. The purpose of the report is to find matching mistakes (department code differences and any amount differences within the matches).
We attached a file of the what the text file looks like when we open it up in Excel 2007.
MATCH NO ACCOUNT DEPT TRAN NO TYPE AMT
20000001 100000000000 AAA 12345678 LCr 50.00
100000000000 12345679 SDr 50.00
20000002 100000000000 BBB 12345680 LCr 1,200.00
100000000000 BBB 12345681 SDr 1,199.99
100000000000 BBB 12345682 SDr 0.01
20000003 100000000000 AAA 12345683 LCr 1,000.00
100000000000 AAA 12345684 SDr 999.00
100000000000 BBB 12345685 SDr 1.01
We also attached a file of what we would like the text file to look like when we run a macro (that we're having so much difficulty with.
MATCH NO ACCOUNT NO DEPT DEPT DIFF TRAN ID TYPE AMT AMT DIFF
20000001 100000000000 AAA NO 12345678 LCr 50.00 NO
20000001 100000000000 NO 12345679 SDr 50.00 NO
20000002 100000000000 BBB NO 12345680 LCr 1,200.00 NO
20000002 100000000000 BBB NO 12345681 SDr 1,199.99 NO
20000002 100000000000 BBB NO 12345682 SDr 0.01 NO
20000003 100000000000 AAA YES 12345683 LCr 1,000.00 YES
20000003 100000000000 AAA YES 12345684 SDr 999.00 YES
20000003 100000000000 BBB YES 12345685 SDr 1.01 YES
The problems we are having:
-The source document only has matched numbers for the first item of each match. To fill in the blank cells, we put the following formula in cell A3:
=A2
And then we pressed CTRL and dragged all the way down all 70,000 rows. Please also note that “Match Number” is automatically assigned after we make the matches. Anyone have a better way?
-The blank cells in the “DEPT” column are another matter. Items can be matched that have matching DEPT’s or an item with a blank DEPT code can be matched to another item with a DEPT code. DEPT codes that do not match need to be identified with a ‘YES’ in “DEPARTMENT DIFFERENCE”. We first tried the following formula:
=IF(AND(C3=C4,A3=A4),"NO","YES")
But this only worked for matches that consisted of two items. If there were more than two items, it didn’t work. So we tried unsuccessfully to nest the following the following two formulas:
=IF(EXACT(A2,A:A),"NO DIFFERENCE","DIFFERENCE")
=IF(C3=C2,C3=””)
Any ideas?
-To determine the amount difference, we had to come up with a formula that has SDr’s and LDr’s behave like negative numbers. The following formula worked:
=IF(AND(F2-F3=0,A2=A3),"NO","YES")
We got the Amount Difference column to work only if there was only two items in a match:
=IF(AND(G2-G3=0,A2=A3),"NO","YES")
But it didn’t work if there were more than two items per match. Any idea for a nested formula.
I know with is a lot. We are really stuck.
We attached a file of the what the text file looks like when we open it up in Excel 2007.
MATCH NO ACCOUNT DEPT TRAN NO TYPE AMT
20000001 100000000000 AAA 12345678 LCr 50.00
100000000000 12345679 SDr 50.00
20000002 100000000000 BBB 12345680 LCr 1,200.00
100000000000 BBB 12345681 SDr 1,199.99
100000000000 BBB 12345682 SDr 0.01
20000003 100000000000 AAA 12345683 LCr 1,000.00
100000000000 AAA 12345684 SDr 999.00
100000000000 BBB 12345685 SDr 1.01
We also attached a file of what we would like the text file to look like when we run a macro (that we're having so much difficulty with.
MATCH NO ACCOUNT NO DEPT DEPT DIFF TRAN ID TYPE AMT AMT DIFF
20000001 100000000000 AAA NO 12345678 LCr 50.00 NO
20000001 100000000000 NO 12345679 SDr 50.00 NO
20000002 100000000000 BBB NO 12345680 LCr 1,200.00 NO
20000002 100000000000 BBB NO 12345681 SDr 1,199.99 NO
20000002 100000000000 BBB NO 12345682 SDr 0.01 NO
20000003 100000000000 AAA YES 12345683 LCr 1,000.00 YES
20000003 100000000000 AAA YES 12345684 SDr 999.00 YES
20000003 100000000000 BBB YES 12345685 SDr 1.01 YES
The problems we are having:
-The source document only has matched numbers for the first item of each match. To fill in the blank cells, we put the following formula in cell A3:
=A2
And then we pressed CTRL and dragged all the way down all 70,000 rows. Please also note that “Match Number” is automatically assigned after we make the matches. Anyone have a better way?
-The blank cells in the “DEPT” column are another matter. Items can be matched that have matching DEPT’s or an item with a blank DEPT code can be matched to another item with a DEPT code. DEPT codes that do not match need to be identified with a ‘YES’ in “DEPARTMENT DIFFERENCE”. We first tried the following formula:
=IF(AND(C3=C4,A3=A4),"NO","YES")
But this only worked for matches that consisted of two items. If there were more than two items, it didn’t work. So we tried unsuccessfully to nest the following the following two formulas:
=IF(EXACT(A2,A:A),"NO DIFFERENCE","DIFFERENCE")
=IF(C3=C2,C3=””)
Any ideas?
-To determine the amount difference, we had to come up with a formula that has SDr’s and LDr’s behave like negative numbers. The following formula worked:
=IF(AND(F2-F3=0,A2=A3),"NO","YES")
We got the Amount Difference column to work only if there was only two items in a match:
=IF(AND(G2-G3=0,A2=A3),"NO","YES")
But it didn’t work if there were more than two items per match. Any idea for a nested formula.
I know with is a lot. We are really stuck.
