Hi,
i am using excel 2003.I have a spreadsheet with over 8,000 rows that I need to match invoices and payments (positive and negative that net 0) with the same invoice number.
The invoice number is in column b, the amounts are in column d.
Currently, I am going down the list selecting cells and clearing the ones that match. There are for types of matches.
1. The ones that have both payments and invoices with the exact same amount (highlighted in sample data with green color).
2. The ones that have two payments against one invoice(or viceversa) with net amount of zero.(highlighted in sample data with orange color).
3. The ones that have one payment and two or more invoices, and the payment can be cleared with one of these invoices.(highlighted in sample data with blue color).
4. The ones that have both payments and invoices but amounts are different (highlighted in sample data withe green color).
Is there any way that we can flag matches, based on these four match types? (for example for match type one we will flag those rows with 1, similarly 2 for match type2, 3 for match type 3 and 4 for match typer for.)
Thank you so much.
Sample Data:
1944815 R235 invoice 123.63
1944815 R236 invoice 121.77
1944815 R235 payment (123.63)
1944815 R236 payment (121.77)
1944816 R237 invoice 123.09
1944816 R237 payment (100.05)
1944816 R237 payment (20.04)
1944817 R238 invoice 116.17
1944817 R238 invoice 116.17
1944817 R238 payment (116.17)
1944818 R239 invoice 761.19
1944818 R239 invoice (760.05 )
( Data starts from cell A2).
i am using excel 2003.I have a spreadsheet with over 8,000 rows that I need to match invoices and payments (positive and negative that net 0) with the same invoice number.
The invoice number is in column b, the amounts are in column d.
Currently, I am going down the list selecting cells and clearing the ones that match. There are for types of matches.
1. The ones that have both payments and invoices with the exact same amount (highlighted in sample data with green color).
2. The ones that have two payments against one invoice(or viceversa) with net amount of zero.(highlighted in sample data with orange color).
3. The ones that have one payment and two or more invoices, and the payment can be cleared with one of these invoices.(highlighted in sample data with blue color).
4. The ones that have both payments and invoices but amounts are different (highlighted in sample data withe green color).
Is there any way that we can flag matches, based on these four match types? (for example for match type one we will flag those rows with 1, similarly 2 for match type2, 3 for match type 3 and 4 for match typer for.)
Thank you so much.
Sample Data:
1944815 R235 invoice 123.63
1944815 R236 invoice 121.77
1944815 R235 payment (123.63)
1944815 R236 payment (121.77)
1944816 R237 invoice 123.09
1944816 R237 payment (100.05)
1944816 R237 payment (20.04)
1944817 R238 invoice 116.17
1944817 R238 invoice 116.17
1944817 R238 payment (116.17)
1944818 R239 invoice 761.19
1944818 R239 invoice (760.05 )
( Data starts from cell A2).
