Wednesday, December 03, 2008   
  Search   
 
 
Register  Login  
Forums  
     Minimize  

Welcome to MeraWindows forums.

Thank you for being at the Microsoft Windows Community Site. You may have to register before posting in forums. It's absolutely free. After registering, you can get all the benefits available to our registered members, you can access our Downloads section, you can participate in contests, etc. You can post in forums in English as well as in Hindi, in fact we encourage you to use Hindi in your posts. If you have any problem with registration or login, please contact us.
     
  


 
  Microsoft Windows Forums  Microsoft Offic...  Microsoft Excel  Matching Column in one workbook with column in in another workbook
Previous Previous
 
Next Next
New Post 9/25/2008 1:51 PM
User is offline Shashank
2 posts
Member


Matching Column in one workbook with column in in another workbook  
I have 2 sets of tables containing data of different period.

 Ex :The amount withdrawn in an ATM  of a bank in  a particular period  by cardholders of 
different banks in an  ATM sharing arrangement. These amount are reimbursed in 
a settlement procedure in a subsequent period to the concerned bank. I have 
got withdrawl  as well as reimbursement files with card no , date, amount, 
txn no. I want to generate a separate file matching each entry in one file 
with the other one to ascetain at a particular time as to how many entries 
are pending for reimbursement. Pl suggest the procedure in excel 2007 to arrive at the result.

Shashank
 
New Post 9/26/2008 7:04 AM
User is offline Ashish Mathur
89 posts
www.ashishmathur.com
Member




Re: Matching Column in one workbook with column in in another workbook 

Hi,

In sheet "Debit", assuming that your data is set up like this:

CREDIT CARD  TXN
DATE NO  AMT
07-04-2008 46956804600048 3,000.00
07-04-2008 52641902029600 1,000.00
07-04-2008 40279018120045 4,000.00
07-04-2008 42149203023428 1,000.00
07-04-2008 52670111900313 500
07-04-2008 52641902029030 1,200.00

In sheet "Credit", the data is set up like this:

TXN/DEBIT TXN CARD  TXN
DATE SNO NO  AMT
16/07/2008 18 52670100902801 1000
16/07/2008 21 53180400022382 5000
16/07/2008 42 46673000023456 5000
16/07/2008 50 52670100905899 1500

In cell F3 of sheet 1, array enter (Ctrl+Shift+Enter) the following formula

=INDEX(CREDIT!$A$3:$C$572,MATCH(DEBIT!$C3&DEBIT!$D3,CREDIT!$B$3:$B$572&CREDIT!$C$3:$C$572,0),1)

Hope tis helps.

 

 

 

For Microsoft Excel trainings in India, please visit http://ashishmathur.com/training.aspx

Regards,

Ashish Mathur - View Ashish Mathur's profile on LinkedIn
Microsoft Excel MVP
www.ashishmathur.com
 
 Page 1 of 1
Previous Previous
 
Next Next
  Microsoft Windows Forums  Microsoft Offic...  Microsoft Excel  Matching Column in one workbook with column in in another workbook


   Get Your Own E-Mail Account @MeraWindows.com Minimize  
New Page 1 New Page 1
Show your cool quotient with @merawindows.com email account
     
  
 
 
Terms Of Use   Privacy Statement  
© 2008 MeraWindows.com