Solved

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

Posted on 2011-09-26
12
178 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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

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

ScreenConnect 6.0 Free Trial

Check out the updates in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI that improves session organization and overall user experience. See the enhancements for yourself!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Word and Excel 2013 - Disabled Items List 3 62
New Criteria Prototype 3 19
how to transpose my example data using VBA 9 34
Rather Simple Formatting Question 6 23
A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
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…

773 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