Excel data Analyis

[gphillips]

Hi Guys,

Any heinsteins out there - a useful code for anyone to know.

Must be straight forward, can someoneprovide soem code detaling how do the analysis of these sets of data - compare and contrast to perform a change analysis:-

Ref Part qty Ref part qty

900 ab 5 900 ab 4 Change qty
900 csd
900 chd
900 fh

Two sets of data 1st set Ref, Part, Qty 2nd set Ref Part Qty

Stuff to remember.

1. I need to sort the two sets of data by ref + part no in ascending order matching the data up by ref where possible.

2. Different ref could use common part numbers - since same part numbers could be use used in more than 1 ref.

3. When the refs are aligned up to each other - the part numbers withhin that specific ref need to be aligned on the same row where possible to perform a change analysis.

4. Notice I say where possible - two identical ref in columns could contain different part numbers as well as same part part numbers. But also there may well be unique refs - where the ref only exist in only one of the data sets.

5. The change indicators include - add, delete, change

1. add -

i.e shown when the 2nd data set (same ref) contains a part number
number not in the 1st data set (same ref) - p.s. this will involve inserting a row where appropriate to show the statment add - and moving that the 2nd data set (mismatch only) in that blank row showing - add. Notice below part jj shows add the others show ok, since theire parts/qtys match.

1stdataset
Ref part qty Ref part qty
1 as 2 1 as 2 OK
1 jj 1 add
1 ab 3 1 ab 3



2. delete - would be the opposite - if the 1st data set (same ref) contains a part not in the 2nd data set (same ref) - then delete would show.

Ref part qty Ref part qty
1 as 2 1 as 2 OK
1 jj 1 Delete
1 ab 3 1 ab 3


3. change - If both part and ref between the data sets are the same but the qty is differenet then change would show.



6. As stated - this code may not be as straight forward as one thinks since it needs to be intelligent enough to know where to add rows and moving dat aroud within data sets.

7. There might even be unique refs - therefore the change indictor is dependent on whether its unique to to the 1st data set ( delete) or unique to be 2nd data set (add). This will involve series of row insert, moving data around to show a series of adds or deletes in a row.

8. Possibility of Duplicate parts with same ref - if this is case the code needs to be inteligent enough to delete part until only one is shown and add the qtys up to make it unique.


9. There could be any number of rows so the code needs to be intellignet enough to know here to stop - i,e when theres blank rows

10. Change summary sheet:-

The change summary will provide a concise summary of the two data sets:-
telling you exactly what needs to be delted, added, or changed within a ref.

Brick Ref Part number qty Change

1 as 2 Delete
1 abc 4 add
1 ad 3 change
1 ddc 4 add
1

Thanks,

Boris

[gphillips]

Hello

Looks like the the two data sets got squashed together below.

It should be clear from the writing what I meant any question just ask.

Ref Part qty Ref part qty

900 ab 5 900 ab 4 Change qty
900 csd
900 chd
900 fh

Boris