Solved

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

Posted on 2011-09-26
12
174 Views
Last Modified: 2012-05-12
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
Comment
Question by:Manojtanwar
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 10

Expert Comment

by:SANTABABY
ID: 36707528
I do not see any attachment.
0
 

Author Comment

by:Manojtanwar
ID: 36707531
sorry

attach again
Sample.xlsx
0
 
LVL 12

Expert Comment

by:viralypatel
ID: 36707702
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
 

Author Comment

by:Manojtanwar
ID: 36707768
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
 
LVL 12

Expert Comment

by:viralypatel
ID: 36707789
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
 

Author Comment

by:Manojtanwar
ID: 36707821
Dear Viralypatel

It also not work in bank statement also.

Thanks
Manoj
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 5

Expert Comment

by:GirardAndrew
ID: 36708276
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
 

Author Comment

by:Manojtanwar
ID: 36708373
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
 
LVL 5

Expert Comment

by:GirardAndrew
ID: 36713039
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
 

Author Comment

by:Manojtanwar
ID: 36715974
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
 
LVL 5

Accepted Solution

by:
GirardAndrew earned 500 total points
ID: 36771164
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
 
LVL 5

Expert Comment

by:GirardAndrew
ID: 36895438
Thank you.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now