I have two separate worksheets from two different sources that I am trying to merge together. I'm trying to reconcile statements but the excel reports I can export are limited. In short, a sale is made and the credit card company deducts a fee for every order. But, each order can have multiple items. The bank only charges fees to the order total. One data sheet shows the order amounts. The second data sheet shows the fees charged. The only common denominator is the Transaction Id. So, I used a simple sumif formula to try and match up each order in Data 1 with the corresponding bank fees in Data 2. BUT, there are duplicates which I have highlighted. Using the first two rows in Data 1 as an example, you'll see there are two transactions under the same order. So, while the sumif works well to match up the transaction IDs, it's obviously not filtering out the duplicates. The total bank fee for the entire order in the first two rows is $21.18 and NOT $42.36. Asides from scanning each row to delete the duplicate fees manually, does anyone have any suggestions to improve the formula in the FEES column to help me filter out the duplicates? Or any other method to accomplish the same thing? Thanks!
Bookmarks