software

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 amp; 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!

G being your concatenated column in Sheet2
E quot; quot; quot; quot; quot; Sheet1

In Sheet2,
You could use =COUNTIF(G:G,G2) - COUNTIF(Sheet1!E:E,G2)
and mark non-zero entries

HTH
--
AP

quot;setsunaquot; gt; a écrit dans le message de
...
gt; We have a file with two worksheets
gt; the first sheet contains data in the following order
gt; A B C D
gt; date order-id document quantity
gt;
gt; the second sheet contains the data as follows:
gt; A B C D E
gt; F
gt; unique-id date order-id document something quantity
gt;
gt; sheet 1 contains 40.788 rows, sheet 2 40.799
gt; both sheets _should_ contain the exact same number of rows, but obviously,
gt; we're missing 11 rows in sheet 1 and we're trying to find out WHICH rows
are
gt; the ones we're looking for.
gt; Any1 has a suggestion as to how we could do this? We tried with a vlookup,
gt; but since every column can contain duplicates, this is nearly undoable, we
gt; tried to concat Sheet 1!A,B,C,D amp; Sheet 2!B,C,D,F in order to create
unique
gt; numbers, but even then, we still have duplicates...and we're not very
eager
gt; to check row by row by hand :|
gt; thanks in advance for any formula that would help us
gt;
gt; --
gt; flame dragon!
THX!
and to say I was actually already using a countif but in the wrong way
--
flame dragon!quot;Ardus Petusquot; wrote:

gt; G being your concatenated column in Sheet2
gt; E quot; quot; quot; quot; quot; Sheet1
gt;
gt; In Sheet2,
gt; You could use =COUNTIF(G:G,G2) - COUNTIF(Sheet1!E:E,G2)
gt; and mark non-zero entries
gt;
gt; HTH
gt; --
gt; AP
gt;
gt; quot;setsunaquot; gt; a écrit dans le message de
gt; ...
gt; gt; We have a file with two worksheets
gt; gt; the first sheet contains data in the following order
gt; gt; A B C D
gt; gt; date order-id document quantity
gt; gt;
gt; gt; the second sheet contains the data as follows:
gt; gt; A B C D E
gt; gt; F
gt; gt; unique-id date order-id document something quantity
gt; gt;
gt; gt; sheet 1 contains 40.788 rows, sheet 2 40.799
gt; gt; both sheets _should_ contain the exact same number of rows, but obviously,
gt; gt; we're missing 11 rows in sheet 1 and we're trying to find out WHICH rows
gt; are
gt; gt; the ones we're looking for.
gt; gt; Any1 has a suggestion as to how we could do this? We tried with a vlookup,
gt; gt; but since every column can contain duplicates, this is nearly undoable, we
gt; gt; tried to concat Sheet 1!A,B,C,D amp; Sheet 2!B,C,D,F in order to create
gt; unique
gt; gt; numbers, but even then, we still have duplicates...and we're not very
gt; eager
gt; gt; to check row by row by hand :|
gt; gt; thanks in advance for any formula that would help us
gt; gt;
gt; gt; --
gt; gt; flame dragon!
gt;
gt;
gt;

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,

quot;setsunaquot; wrote:

gt; We have a file with two worksheets
gt; the first sheet contains data in the following order
gt; A B C D
gt; date order-id document quantity
gt;
gt; the second sheet contains the data as follows:
gt; A B C D E
gt; F
gt; unique-id date order-id document something quantity
gt;
gt; sheet 1 contains 40.788 rows, sheet 2 40.799
gt; both sheets _should_ contain the exact same number of rows, but obviously,
gt; we're missing 11 rows in sheet 1 and we're trying to find out WHICH rows are
gt; the ones we're looking for.
gt; Any1 has a suggestion as to how we could do this? We tried with a vlookup,
gt; but since every column can contain duplicates, this is nearly undoable, we
gt; tried to concat Sheet 1!A,B,C,D amp; Sheet 2!B,C,D,F in order to create unique
gt; numbers, but even then, we still have duplicates...and we're not very eager
gt; to check row by row by hand :|
gt; thanks in advance for any formula that would help us
gt;
gt; --
gt; flame dragon!