Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 191
  • Last Modified:

Matching Two column in different workbook have multiple occurance of same value

I am sending u sample file in this file two sheet one is rib(Remitance into Bank) and another is Bank statment. I wish to match rib statement with bank statement. I put up vlookup formula for that. i getting problem with vlookup when same amount occuer in the rib statment. As u seen in the sheet rib there are 3 times 150 but in the bank statement only 1 times but vlookup matched the all three 150 in rib statement.   i want that it matched with one by one or u can say if one time 150 in that then matched with one time and not available show for another two. Please solve my problem to provide excel addin . if possible i want to put to want date check also. date of matched amount in bank statement cannot be less then the date of rib statement.
0
Manojtanwar
Asked:
Manojtanwar
  • 5
  • 4
  • 2
  • +1
1 Solution
 
SANTABABYCommented:
I do not see any attachment.
0
 
ManojtanwarAuthor Commented:
sorry

attach again
Sample.xlsx
0
 
viralypatelCommented:
Solution attached.
You have to create two temp columns before the vlookup i.e. concatenate and sl.no. (as seen in the attached file).
Then, write a vlookup on concatenate column as seen in the file .
Solution.xlsx
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
ManojtanwarAuthor Commented:
Dear viralypatel

Thanks for your reply.

But it will not work when date of bank statement is greater than rib statement. In this sample Rs. 150 is on dated 11.01.2010 if this amount matched in bank statement on dated 15.01.2010 then it will not match i think. I want to match Rs. 150 in bank statement of whole month condition bank statement date is greater then rib date.

Thanks
Manoj
0
 
viralypatelCommented:
In that case i guess you should like the vlookup in the bank statement sheet instead of the rib sheet and search in the rib sheet for matches against the values in the bank statement sheet.
0
 
ManojtanwarAuthor Commented:
Dear Viralypatel

It also not work in bank statement also.

Thanks
Manoj
0
 
GirardAndrewCommented:
Bank reconciliation - I would assume that data in RIB records should be greater than data in Bank Statement since remittances should be classified as "in transit".

A case like this requires a different index/reference column - which could be a transaction number. This requires that your RIB should also record the same reference number.

If that is not possible, a more appropriate method would be to pivot table both then compare balances - starting as a whole then by date.
0
 
ManojtanwarAuthor Commented:
Dear GirardAndrew

No dear data is rib is equal and less then from bank statement because rib is cash or cheque deposited in bank and bank date is date on the amount credit in the my account. so date  of rib is equal or less then bank statement or vise versa bank statement date is equal or greater then rib date.
i hope u understand.
0
 
GirardAndrewCommented:
Dear Manojtanwar,

I am sorry for the confusion but you mentioned that "date of RIB is equal or less than bank statement", when translated should be:

RIB Date <= Bank Statement Date

This should mean RIB data is recorded first, which would make RIB records more than that of the bank statement at some point.

You also mentioned that "bank statement date is equal or greater than RIB date", when translated should be:

Bank Statement Date >= RIB Date

Nevertheless, to make automation flawless, you still need a unique identifier as reference between the two records since date is expected to be inconsistent especially when handling cheque deposits. For cheque deposits, you can easily use check numbers as reference.

A pivot  table should work too, but there has to manual checking somewhere in the process.

Thanks,
Girard Andrew
0
 
ManojtanwarAuthor Commented:
can we match only match two column like amount in rib and amount in bank statement. and taken out which amount is not in the bank statment. do not consider date column

Thanks
Manoj
0
 
GirardAndrewCommented:
Hi Manoj,

That should work too. But my suggestion would be to create a report that list which values do not match. I would also suggest to use pivot tables so that it will be easier to double-click and pick up the information.

Please refer to the file attached for the solution. Each result of the VLOOKUP function represents the following:

0 = both matches
>0 = more records are found
<0 = less records are found
#N/A = the said records are not found in the other sheet

Thanks,
Girard Andrew Solution-27342880.xlsx
0
 
GirardAndrewCommented:
Thank you.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now