Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing 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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing 2 tables to find missing detail
Excel does not have lines. It has rows and columns.
Tyro wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing 2 tables to find missing detail
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. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- wrote in message ... 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing 2 tables to find missing detail
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 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing 2 tables to find missing detail
Sandy,
Guess the OP is apparently speechless with delight at our responses. It must have worked, it must have ... -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing 2 tables to find missing detail
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. -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Max" wrote in message ... Sandy, Guess the OP is apparently speechless with delight at our responses. It must have worked, it must have ... -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing 2 tables to find missing detail
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? -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comparing tables in access | Excel Discussion (Misc queries) | |||
Anyone seen Pivot Tables with incorrect detail but correct total? | Excel Discussion (Misc queries) | |||
Comparing 2 Tables | Excel Worksheet Functions | |||
Comparing two tables | Excel Discussion (Misc queries) | |||
Pivot Tables: Unable to Group and Show Detail | Excel Discussion (Misc queries) |