Hello All,
I import various banking payment details into my company's database. Often, banks agree to include our Supplier IDs on transaction statements so that we can more easily manage the data, match cases etc. However, when multiple partial payments are swiped, or when a payment is cancelled, there will be multiple line entries on the Excel report, and only one entry per Control Number will include the Supplier ID. The additional entries for the same Control Number will have blank cells where Supplier IDs should be present. It should also be noted that supplier ID is not always present in the earliest transaction for that control number, and is not always present in the top entry for each control number. A Supplier ID is present for all Control Numbers, but seems to be present at random. Below is the type of data I receive (this is not actual banking data, obviously - completely made up). Column letters and titles included, so the first control number listed is in cell A2. In case the below does not post correctly, I have included a Workbook. The workbook contains 2 sheets, one titled "Statement Received" and one title "Finished Product"
A B C
Control Number Supplier Name Supplier ID
503056 John's Computer Store 100
503056 John's Computer Store
503058 Susan's Law Office
503058 Susan's Law Office 200
503058 Susan's Law Office
503059 Kelly's Auto Repair
503059 Kelly's Auto Repair 300
Below is the finished product that I would like to see.
A B C
Control Number Supplier Name Supplier ID
503056 John's Computer Store 100
503056 John's Computer Store 100
503058 Susan's Law Office 200
503058 Susan's Law Office 200
503058 Susan's Law Office 200
503059 Kelly's Auto Repair 300
503059 Kelly's Auto Repair 300
These reports are growing, so finding a way to fill in blank cells based on the Supplier ID that is already present for each Control Number will help immensely. Thank you for taking the time to look!
Bookmarks