Previous Topic Next Topic

Match requests sent to bank with requests processed by bank

Closed
VSK - Oct 22, 2015 at 07:36 AM
 VSK - Oct 22, 2015 at 06:04 PM
Hello,

I am trying to match the requests processed by the bank with the requests sent to the bank for processing. The bank could process part of the refund requests that were sent yesterday and part from days before. I have all the information in the same spreadsheet. The first few columns are for what we receive from the bank and the next few columns are for what we send to the bank. I am looking for a way to have matching records on the same rows and push the ones that do not match to the bottom of the list. The merchant #, card # and amount have to match. Any advice is highly appreciated.


Requests sent to bank Bank Online processed requests
COLUMN HEADINGS (A-F) COLUMN HEADINGS (H...)
Merchant # Merchant #
Card# Card#
Card type Card type
Refund Refund
Date Date
Auth # Auth #

1 response

Answer 1 / 1
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Oct 22, 2015 at 11:03 AM
in my opinion, most efficient and simple way would be have two additional columns where merchant, account and amount are combined (one for "from bank" and one "to bank"). then you can use match formula and match the value
0
VSK
Oct 22, 2015 at 11:15 AM
Thank you for your response.

Both sets of data are not in any particular order. I want them matched so that each matching items for "sent to bank" and "processed by bank" are on the same row ( so I can clearly see they match). I also want the unmatched items t be pushed down to the bottom of the list so they can carried forward to the next day. Please advise.

Thanks,
VSK
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766 > VSK
Oct 22, 2015 at 11:36 AM
If you use the option i mentioned, you would then use MATCH formula. Any place there is no match, you would have seen #N/A. Filtering those would give you information you seek
0
VSK > rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022
Oct 22, 2015 at 06:04 PM
Hi,
Thank you for the clarification. Yes "Match" does work well. I am just wondering could we have a macro to sort the results of the match formula in ascending order? This would enable me to have the matching item for "processed by bank" on the same row as the item in "Sent to Bank". Please advise.


Processed by bank Column Sent to Bank Column
A - Merchant M - Merchant
D - Card # O - Card #
E - Card Type N - Card Type
G - Amount T - Amount

Formula in Column AD =A2&D2&E2&G2 (to combine merchant. card, type and amount for "Processed by Bank")

Formula in Column AE ="200-0"&IF(LEFT(M2,3)="OMS",MID(M2,4,8),MID(M2,3,8))&O2&N2&-T2 ( (to combine merchant. card, type and amount for "Sent to Bank")

The results of MATCH formula are in Column AF

Please advise. Thanks in advance for all the help. Appreciate it!

VSK
0

Similar discussions

It is showing that you can not use this feature right now
Jaatshab -
HelpiOS -

1 response
Sorry, this feature isn't available right now. An error occurred while processin
maginco -
purchauditoday -

10 responses
"Sorry, this feature isn't available ... " how to fix this?
Anonymous User -
heclam -

3 responses
App Password "This Feature isn't Available Right Now"
DavidS -
Enduroelm -

11 responses
an error occured, please try again later
adrian -
Farman -

35 responses