I have two inventories downloaded into Excel and need to reconcile them.
There 500 items in each worksheet. Is there some kind on add in or function
to use that will compair them and tell me what the differences are.

"Greg" wrote
I have two inventories downloaded into Excel and need to reconcile them.
There 500 items in each worksheet. Is there some kind on add in or
function
to use that will compare them and tell me what the differences are.

Here's a way using relatively simple formulas to drive out all 3 scenarios
in 3 separate sheets in comparing A vs B, viz:

a. In A not in B
b. In B not in A
c. In A & B

Illustrated in this sample:
http://www.freefilehosting.net/download/3bg9d
Compare A vs B n extract all scenarios.xls

Source data to be compared assumed in sheets: A, B, data in A2 down

In A not in B,

In A2:
=IF(A!A2="","",IF(ISNUMBER(MATCH(A!A2,B!A:A,0)),"" ,ROW()))

In B2:
=IF(ROWS($1:1)COUNT($A:$A),"",INDEX(A!A:A,SMALL($ A:$A,ROWS($1:1))))
Select A2:B2, copy down to cover the max expected extent of data in A's col
A. Minimize/hide away col A. Col B returns items in A not in B, all neatly
bunched at the top.
-----------
In B not in A,

In A2:
=IF(B!A2="","",IF(ISNUMBER(MATCH(B!A2,A!A:A,0)),"" ,ROW()))

In B2:
=IF(ROWS($1:1)COUNT($A:$A),"",INDEX(B!A:A,SMALL($ A:$A,ROWS($1:1))))
Select A2:B2, copy down to cover the max expected extent of data in B's col
A. Minimize/hide away col A. Col B returns items in B not in A, all neatly
bunched at the top.
-----------

In A & B,

Do a one-time copy n paste of the data from A and B into A2 down. Fill B2
down correspondingly with the source sheetname: A, B

Then place

In C2:
=IF(A2="","",IF(COUNTIF(A$2:A2,A2)1,"",ROW()))

In D2:
=IF(ROWS($1:1)COUNT(C:C),"",INDEX(A:A,SMALL(C:C,R OWS($1:1))))

In E2:
=IF(D2="","",IF(COUNTIF(A:A,D2)=2,ROW(),""))

In F2:
=IF(ROWS($1:1)COUNT(E:E),"",INDEX(D:D,SMALL(E:E,R OWS($1:1))))
Select C2:F2, copy down to cover the max expected extent of data in col A.
Minimize/hide away cols C to E. Col F returns items found in both A & B, all
neatly bunched at the top.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

In these two work sheets I will need to compare material number, batch number
and quatity. Will what you are suggesting work for this.

Material Batch QTY
18336 TF7C12H000 7


"Max" wrote:

"Greg" wrote
I have two inventories downloaded into Excel and need to reconcile them.
There 500 items in each worksheet. Is there some kind on add in or
function
to use that will compare them and tell me what the differences are.

Here's a way using relatively simple formulas to drive out all 3 scenarios
in 3 separate sheets in comparing A vs B, viz:

a. In A not in B
b. In B not in A
c. In A & B

Illustrated in this sample:
http://www.freefilehosting.net/download/3bg9d
Compare A vs B n extract all scenarios.xls

Source data to be compared assumed in sheets: A, B, data in A2 down

In A not in B,

In A2:
=IF(A!A2="","",IF(ISNUMBER(MATCH(A!A2,B!A:A,0)),"" ,ROW()))

In B2:
=IF(ROWS($1:1)COUNT($A:$A),"",INDEX(A!A:A,SMALL($ A:$A,ROWS($1:1))))
Select A2:B2, copy down to cover the max expected extent of data in A's col
A. Minimize/hide away col A. Col B returns items in A not in B, all neatly
bunched at the top.
-----------
In B not in A,

In A2:
=IF(B!A2="","",IF(ISNUMBER(MATCH(B!A2,A!A:A,0)),"" ,ROW()))

In B2:
=IF(ROWS($1:1)COUNT($A:$A),"",INDEX(B!A:A,SMALL($ A:$A,ROWS($1:1))))
Select A2:B2, copy down to cover the max expected extent of data in B's col
A. Minimize/hide away col A. Col B returns items in B not in A, all neatly
bunched at the top.
-----------

In A & B,

Do a one-time copy n paste of the data from A and B into A2 down. Fill B2
down correspondingly with the source sheetname: A, B

Then place

In C2:
=IF(A2="","",IF(COUNTIF(A$2:A2,A2)1,"",ROW()))

In D2:
=IF(ROWS($1:1)COUNT(C:C),"",INDEX(A:A,SMALL(C:C,R OWS($1:1))))

In E2:
=IF(D2="","",IF(COUNTIF(A:A,D2)=2,ROW(),""))

In F2:
=IF(ROWS($1:1)COUNT(E:E),"",INDEX(D:D,SMALL(E:E,R OWS($1:1))))
Select C2:F2, copy down to cover the max expected extent of data in col A.
Minimize/hide away cols C to E. Col F returns items found in both A & B, all
neatly bunched at the top.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Think so. You could concat the 3 cols together to define it as a single col
"item",
then run the "items" through in the manner prescribed.

Eg put in D2: =A2&"#"&B2&"#"&C2, then copy down.
Then use col D as the items in the sample's col A.
Finally deconcat the results col in the 3 sheets,
using DataText to Columns, delimiter: #
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Greg" wrote in message
...
In these two work sheets I will need to compare material number, batch
number
and quatity. Will what you are suggesting work for this.

Material Batch QTY
18336 TF7C12H000 7

Just a clarification that the "3 cols" in:
.. concat the 3 cols together
refers to your: Material, Batch, QTY

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Max,

I am going to start trying this but I am not sure what you mean by "concat"
the 3 cols together. What does contact mean? Group or merge?

Thanks

"Max" wrote:

Just a clarification that the "3 cols" in:
.. concat the 3 cols together
refers to your: Material, Batch, QTY

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Max,

Here is a true sample of what I need to compare. Can you take this a set it
up so I can have something in true form to work with on my other inventories?

link to my sample file http://www.savefile.com/files/1364605

Thanks

"Max" wrote:

"Greg" wrote
I have two inventories downloaded into Excel and need to reconcile them.
There 500 items in each worksheet. Is there some kind on add in or
function
to use that will compare them and tell me what the differences are.

Here's a way using relatively simple formulas to drive out all 3 scenarios
in 3 separate sheets in comparing A vs B, viz:

a. In A not in B
b. In B not in A
c. In A & B

Illustrated in this sample:
http://www.freefilehosting.net/download/3bg9d
Compare A vs B n extract all scenarios.xls

Source data to be compared assumed in sheets: A, B, data in A2 down

In A not in B,

In A2:
=IF(A!A2="","",IF(ISNUMBER(MATCH(A!A2,B!A:A,0)),"" ,ROW()))

In B2:
=IF(ROWS($1:1)COUNT($A:$A),"",INDEX(A!A:A,SMALL($ A:$A,ROWS($1:1))))
Select A2:B2, copy down to cover the max expected extent of data in A's col
A. Minimize/hide away col A. Col B returns items in A not in B, all neatly
bunched at the top.
-----------
In B not in A,

In A2:
=IF(B!A2="","",IF(ISNUMBER(MATCH(B!A2,A!A:A,0)),"" ,ROW()))

In B2:
=IF(ROWS($1:1)COUNT($A:$A),"",INDEX(B!A:A,SMALL($ A:$A,ROWS($1:1))))
Select A2:B2, copy down to cover the max expected extent of data in B's col
A. Minimize/hide away col A. Col B returns items in B not in A, all neatly
bunched at the top.
-----------

In A & B,

Do a one-time copy n paste of the data from A and B into A2 down. Fill B2
down correspondingly with the source sheetname: A, B

Then place

In C2:
=IF(A2="","",IF(COUNTIF(A$2:A2,A2)1,"",ROW()))

In D2:
=IF(ROWS($1:1)COUNT(C:C),"",INDEX(A:A,SMALL(C:C,R OWS($1:1))))

In E2:
=IF(D2="","",IF(COUNTIF(A:A,D2)=2,ROW(),""))

In F2:
=IF(ROWS($1:1)COUNT(E:E),"",INDEX(D:D,SMALL(E:E,R OWS($1:1))))
Select C2:F2, copy down to cover the max expected extent of data in col A.
Minimize/hide away cols C to E. Col F returns items found in both A & B, all
neatly bunched at the top.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

I'm afraid I don't have xl2007
Save it as xl2003, then re-upload
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Greg" wrote in message
...
Max,

Here is a true sample of what I need to compare. Can you take this a set
it
up so I can have something in true form to work with on my other
inventories?

link to my sample file http://www.savefile.com/files/1364605

Thanks

Concat is shorthand for concatenate, means join together

What do you mean by "reconcile inventories"
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Greg" wrote in message
...
Max,

I am going to start trying this but I am not sure what you mean by
"concat"
the 3 cols together. What does contact mean? Group or merge?

Thanks

Here you go Max and Thanks again. This is just a small list of the items I
need to reconcile. I am looking for a list of the material numbers and
batches showing a side by side comparison of worksheet "UTI and SAP" and any
differences. If you can come up something close to what I made in my sample
it would be a huge help!

http://www.savefile.com/files/1364800

"Max" wrote:

I'm afraid I don't have xl2007
Save it as xl2003, then re-upload
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Greg" wrote in message
...
Max,

Here is a true sample of what I need to compare. Can you take this a set
it
up so I can have something in true form to work with on my other
inventories?

link to my sample file http://www.savefile.com/files/1364605

Thanks

A multi-criteria index/match (array-entered) should do it for you
as implemented here in your sample:
http://www.freefilehosting.net/download/3bhk1
Reconcile_Inventory_Index_Match.xls

In "comparison",

In C2, array-entered**:
=IF(ISNA(MATCH(1,(INDIRECT("'"&C$1&"'!A2:A500")=$A 2)*(INDIRECT("'"&C$1&"'!C2:C500")=$B2),0)),0,
INDEX(INDIRECT("'"&C$1&"'!D2:D500"),MATCH(1,(INDIR ECT("'"&C$1&"'!A2:A500")=$A2)*(INDIRECT("'"&C$1&"' !C2:C500")=$B2),0)))
Copy C2 to D2
**Press CTRL+SHIFT+ENTER to confirm the formula

In E2: =D2-C2
Select C2:E2, copy down

Adapt the ranges to suit. Also, ensure your sheetname labels in C1:D1 match
exactly (except for case) with what's on the tabs. The "UTI" in the original
had an extra space.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Greg" wrote:
Here you go Max and Thanks again. This is just a small list of the items I
need to reconcile. I am looking for a list of the material numbers and
batches showing a side by side comparison of worksheet "UTI and SAP" and any
differences. If you can come up something close to what I made in my sample
it would be a huge help!

http://www.savefile.com/files/1364800