 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / March 2008Comparing 2 tables to find missing detail
I have a list of journal entries that is missing some detail (Table A). IN Another table (Table B) I have the year's listing of journal entries that contains this detail plus a lot more. What formula can I write in table A that will find the associated record in B (say based on journal number and dollar amount) that will bring across the correct detail for each line. They are both arranged in columns in a similar structure and contain both text and numbers. I know this but am having a brain fade! Thanks Excel does not have lines. It has rows and columns. Tyro >I have a list of journal entries that is missing some detail (Table > A). IN Another table (Table B) I have the year's listing of journal [quoted text clipped - 9 lines] > > Thanks In sheet: Table A, Assuming the journal # and amounts are listed in A2:B2 down, you could place this in C2's formula bar, then array-enter the formula by pressing CTRL+SHIFT+ENTER (instead of just pressing ENTER): =INDEX('Table B'!C$2:C$10,MATCH(1,('Table B'!$A$2:$A$10=$A2)*('Table B'!$B$2:$B$10=$B2),0)) Copy C2 across/fill down as far as required to extract cols C, D, etc from sheet: Table B. Adapt the ranges to suit.  SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- >I have a list of journal entries that is missing some detail (Table > A). IN Another table (Table B) I have the year's listing of journal [quoted text clipped - 9 lines] > > Thanks Max, I am not trying to put down your formula but my reading of the OP: >> write in table A that will find the associated record in B (say based >> on journal number and dollar amount) suggests that there is the possibility that there could be two or more journal # the same. If that is so then, if the dollar amounts are also by chance the same as well, your formula will bring across the details of the first match in the second journal # Row If there will never be two similar journal #'s then you don't need the second comparison in the MATCH() so simply: =INDEX('Table B'!C$2:C$10,MATCH($A2,'Table B'$A$2:$A$10,0)) normally entered will do the job - or am I misreading it? SignatureHTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk > In sheet: Table A, > Assuming the journal # and amounts are listed in A2:B2 down, [quoted text clipped - 17 lines] >> >> Thanks OP should try both index/match variations provided and feedback to us here Earlier, on the face of this part: > .. (say based on journal number and dollar amount) it suggests a dual criteria SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- Sandy, Guess the OP is apparently speechless with delight at our responses. It must have worked, it must have ... SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- I think that he/she/they, (deniseandgavin), would be satisfied with your formula because, now that I think about it, in my very limited experience of monarary matters, (God knows that I am not very good with money, that's why he doesn't give me very much), the only Journal entries that I encountered at work were all unique numbers. I assume now that in the *associated record in B (say based on journal number and dollar amount)* the dollar amount was and example of what the missing data could be but then you probably knew that already. SignatureRegards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk > Sandy, > Guess the OP is apparently speechless with delight at our responses. It > must have worked, it must have ... Sandy, the earlier dig was intended for the OP. In the sad absence of OPs reversion/participation in discussions (a general obs, btw), who knows? We care, but OPs themselves? SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.