Reconcile Inventories Using excel

  • Thread starter Thread starter Greg
  • Start date Start date
G

Greg

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.
 
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.
Click to expand...

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,ROWS($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,ROWS($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.

---
 
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
 
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 Data>Text to Columns, delimiter: #
 
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
 
Concat is shorthand for concatenate, means join together

What do you mean by "reconcile inventories"
 
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
 
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")=$A2)*(INDIRECT("'"&C$1&"'!C2:C500")=$B2),0)),0
INDEX(INDIRECT("'"&C$1&"'!D2:D500"),MATCH(1,(INDIRECT("'"&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.
 
Back
Top