You may have an occasion when you have two data lists that we want to “line up.” For instance, column A might be a customer account number, while column B displays the customer’s account balance. In columns C and D you then paste a listing of customer payments, with column C being the customer account number and column D being the payment amount. Both lists (A/B and C/D) are sorted by customer account number.
Since not all customers with balances made payments, the A/B list is not in synch with the C/D list. To get them in synch, you need to insert blank cells where needed in columns C/D (and sometimes columns A/B) so that the customer account number in column C matches the customer account number in column A.
If your goal is to match payments to balances, then there is a relatively easy way to do this, without the need to insert cells in the lists. Follow these steps:
- Insert three blank columns between the two lists. When done, you should have the account balances in A/B, blank columns in C/D/E, and the payments in F/G.
- Assuming the first account/balance combination is in cells A2:B2, enter the following formula in cell C2:
- Copy the formula down through the rest of column C.
=IF(ISNA(VLOOKUP(A2,F:G,2,FALSE)),0,VLOOKUP(A2,F:G,2,FALSE))
This formula looks in the payments columns (F/G) for any cells that match the account number in column A. If found, then the amount of the payment is returned by the formula. If a match is not located, then a zero value is returned.
The approach works well if you know that the payment columns contain only a single payment for each account. If it is possible that some accounts received multiple payments, then you need to change the formula you use in step 2:
=SUMIF(F:F, A2,G:G )
This formula, if it finds a match, adds all the payments together and returns the sum.
Of course, the example first described in this tip is just that-an example of a more pervasive problem. You may have a need to synch lists where there is only text in the lists, or where it is more difficult to do a lookup or you don’t need to return a sum. In those instances, it may be best to look for a third-party solution.
