Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup and compare rows
We have a file with two worksheets
the first sheet contains data in the following order A B C D date order-id document quantity the second sheet contains the data as follows: A B C D E F unique-id date order-id document something quantity sheet 1 contains 40.788 rows, sheet 2 40.799 both sheets _should_ contain the exact same number of rows, but obviously, we're missing 11 rows in sheet 1 and we're trying to find out WHICH rows are the ones we're looking for. Any1 has a suggestion as to how we could do this? We tried with a vlookup, but since every column can contain duplicates, this is nearly undoable, we tried to concat Sheet 1!A,B,C,D & Sheet 2!B,C,D,F in order to create unique numbers, but even then, we still have duplicates...and we're not very eager to check row by row by hand :| thanks in advance for any formula that would help us :) -- flame dragon! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup and compare rows
G being your concatenated column in Sheet2
E " " " " " Sheet1 In Sheet2, You could use =COUNTIF(G:G,G2) - COUNTIF(Sheet1!E:E,G2) and mark non-zero entries HTH -- AP "setsuna" a écrit dans le message de ... We have a file with two worksheets the first sheet contains data in the following order A B C D date order-id document quantity the second sheet contains the data as follows: A B C D E F unique-id date order-id document something quantity sheet 1 contains 40.788 rows, sheet 2 40.799 both sheets _should_ contain the exact same number of rows, but obviously, we're missing 11 rows in sheet 1 and we're trying to find out WHICH rows are the ones we're looking for. Any1 has a suggestion as to how we could do this? We tried with a vlookup, but since every column can contain duplicates, this is nearly undoable, we tried to concat Sheet 1!A,B,C,D & Sheet 2!B,C,D,F in order to create unique numbers, but even then, we still have duplicates...and we're not very eager to check row by row by hand :| thanks in advance for any formula that would help us :) -- flame dragon! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup and compare rows
THX!
and to say I was actually already using a countif but in the wrong way :) -- flame dragon! "Ardus Petus" wrote: G being your concatenated column in Sheet2 E " " " " " Sheet1 In Sheet2, You could use =COUNTIF(G:G,G2) - COUNTIF(Sheet1!E:E,G2) and mark non-zero entries HTH -- AP "setsuna" a écrit dans le message de ... We have a file with two worksheets the first sheet contains data in the following order A B C D date order-id document quantity the second sheet contains the data as follows: A B C D E F unique-id date order-id document something quantity sheet 1 contains 40.788 rows, sheet 2 40.799 both sheets _should_ contain the exact same number of rows, but obviously, we're missing 11 rows in sheet 1 and we're trying to find out WHICH rows are the ones we're looking for. Any1 has a suggestion as to how we could do this? We tried with a vlookup, but since every column can contain duplicates, this is nearly undoable, we tried to concat Sheet 1!A,B,C,D & Sheet 2!B,C,D,F in order to create unique numbers, but even then, we still have duplicates...and we're not very eager to check row by row by hand :| thanks in advance for any formula that would help us :) -- flame dragon! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup and compare rows
Hi,
There may ne another wao of doing it. In column G of sheet2, enter the following formula (Ctrl+Shift+Enter). =OR(EXACT(A1,sheet1!$A$1:A40788)) Regards, "setsuna" wrote: We have a file with two worksheets the first sheet contains data in the following order A B C D date order-id document quantity the second sheet contains the data as follows: A B C D E F unique-id date order-id document something quantity sheet 1 contains 40.788 rows, sheet 2 40.799 both sheets _should_ contain the exact same number of rows, but obviously, we're missing 11 rows in sheet 1 and we're trying to find out WHICH rows are the ones we're looking for. Any1 has a suggestion as to how we could do this? We tried with a vlookup, but since every column can contain duplicates, this is nearly undoable, we tried to concat Sheet 1!A,B,C,D & Sheet 2!B,C,D,F in order to create unique numbers, but even then, we still have duplicates...and we're not very eager to check row by row by hand :| thanks in advance for any formula that would help us :) -- flame dragon! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formating not recognised in lookup formula | Excel Discussion (Misc queries) | |||
Limiting the range of a lookup function | Excel Worksheet Functions | |||
Conditional Formatting - Lookup Range | Excel Discussion (Misc queries) | |||
Lookup and compare to (today) + time and get result | Excel Discussion (Misc queries) | |||
lookup in colum a and compare values in colum b | Excel Worksheet Functions |