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!

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!

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!

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!