?
Solved

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

Posted on 2011-09-26
12
Medium Priority
?
186 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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
 
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 2000 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

764 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