How to Identify Changes In Two Work Sheets

[la90292]

I have two different dated inventory lists with 3,074 of identical line
items. I want to determine the "items" that have "sold units" from two lists
since they reflect ending inventory on separate dates. I want to create a
new work sheet with those "items" only whose ending units are different from
the units on the first ending report. The items are in the Desc 1 column in
both work sheets along with their vendor code, size & quantity.

Assume worksheet 1 is dated 10-18-06 and worksheet 2 is dated 10-22-06.

Worksheet 1:
Columns: C = VC (vendor code) , D = Desc 1, E= Size, F= Qty

Worksheet 2:
Columns: B = VC, C= Desc 1, D= Size, E = Qty

Worksheet 3:
This sheet will show the "items" matching the VC, Desc 1 & Size parameters
of the sold units since worksheet 1, as negative #. In other words, I will
be able to determine how many units were sold since the time worksheet 1 was
generated.

Columns: A= VC, B = Desc 1, C = Size, D = Qty, i.e. -2

Thank you for your time and consideration.

Best regards,
victor

 

[Roger Govier]

Hi

Is there any reason why the data is in different columns on different
sheets?
I find I make fewer mistakes when similar data is located in similar
cells on different sheets so it might ease things to insert a new column
B on Sheet 2 and insert columns A&B on sheet3 - just a suggestion (if
you do, amend the formulae below to refer to the identical cells from
other sheets).

With regard to your problem. Can we assume that the data is in the same
order on both sheets?
If so, then with your existing layout sheet3 would have the following
formulae
in cell A2
=Sheet2!B2
copy across through B22
in cell E2
=Sheet1!F2-Sheet2!E2

If the data is not in the same order, can you sort sheets 1 and 2 to get
them in the same order first?
If not post back for an alternative solution.

 

[Max]

Assuming data is identical* for all 3074 lines in both Sheet1 and Sheet2
from row2 down
*except for the numbers in the Qty col (& the 4 col placements', viz.: C to
F in Sheet1, B to E in Sheet2)

In Sheet3,
Col headers in A11 are: VC, Desc 1, Size, Qty

Put in A2:
=IF(ROW(A1)>COUNT($E:$E),"",INDEX(Sheet1!C:C,SMALL($E:$E,ROW(A1))))
Copy A2 to C2

Put in D2:
=IF(ROW(A1)>COUNT($E:$E),"",INDEX(Sheet2!E:E,SMALL($E:$E,ROW(A1)))-INDEX(Sheet1!F:F,SMALL($E:$E,ROW(A1))))

Put in E2:
=IF(Sheet2!E2-Sheet1!F2<0,ROW(),"")
(Leave E1 empty)

Then just select A2:E2, copy down to cover the max expected extent of all
data lines, say down to E3200. Hide away col E. Sheet3 should return the
required results, ie only the lines where Sheet2's Qty shows figs less than
those in the corresponding Sheet1's Qty, with all result lines neatly
bunched at the top.

 

[la90292]

Thanks for your suggestions. Today is my day to tackle this project and the
reason for not replying sooner.

What I've learned since my initial post is that Worksheet 1 has 7 items that
are not on Worksheet 2. Probably, because those units were sold and thus not
reflected on Worksheet 2. In addition, I discovered one new item (size 4)
that wasn't on Worksheet 1, but now is mysteriously in Worksheet 2.

How does this affect the formulas you provided? I can reformat the data
columns so that they are congruent. What I am looking for now is to know
the items that are not duplicated between the two files where the data
columns will have the same parameters.

Thanks again for your help.

Best regards,
Victor

 

[Max]

Victor,
Suggest you put in a fresh posting, re-defining clearly the query.
Perhaps other would have insights to offer you. I'm out here. Good luck!