VBA Code for VLOOKUP/Data Update

[GreatGemini]

Hi,
Its 4 part question, just want to start with part 1:
Here is what I badly need:

1. Lookup Pre Production Data's Product ID in Post Production Data
2. Check all Parts IDs data, if any of the Part Code has changed
3. If YES, insert "Upgraded" in Status column and if not changed than insert "Existing" ..... Continue...
3. If YES than in New Data under that part ID change existing value to "Upgraded" otherwise "Existing"
4. If Product ID is not found change part code to "Discontinue"

Excel 2016 (Windows) 32 bit
[TABLE="class: head"]
<tbody>[TR="bgcolor: #888888"]
[TH][/TH]
[TH]

B​

[/TH]
[TH]

C​

[/TH]
[TH]

D​

[/TH]
[TH]

E​

[/TH]
[TH]

F​

[/TH]
[TH]

G​

[/TH]
[TH]

H​

[/TH]
[TH]

I​

[/TH]
[TH]

J​

[/TH]
[TH]

K​

[/TH]
[TH]

L​

[/TH]
[TH]

M​

[/TH]
[TH]

N​

[/TH]
[TH]

O​

[/TH]
[TH]

P​

[/TH]
[TH]

Q​

[/TH]
[TH]

R​

[/TH]
[TH]

S​

[/TH]
[TH]

T​

[/TH]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

1​

[/TD]
[TD]Pre Production Data:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

2​

[/TD]
[TD]Product ID[/TD]
[TD]Part1 ID[/TD]
[TD]Part2 ID[/TD]
[TD]Part3 ID[/TD]
[TD]Part4 ID[/TD]
[TD]Par5 ID[/TD]
[TD]Part6 ID[/TD]
[TD]Part7 ID[/TD]
[TD]Part8 ID[/TD]
[TD]Part9 ID[/TD]
[TD]Part10 ID[/TD]
[TD]Part11 ID[/TD]
[TD]Part12 ID[/TD]
[TD]Part13 ID[/TD]
[TD]Part14 ID[/TD]
[TD]Part15 ID[/TD]
[TD]Part16 ID[/TD]
[TD]Part17 ID[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

3​

[/TD]
[TD]Multi Deco 12K[/TD]
[TD]IDK-6021[/TD]
[TD]IDK-6022[/TD]
[TD]IDK-6058[/TD]
[TD]IDK-6059[/TD]
[TD]IDK-6066[/TD]
[TD]IDK-6070[/TD]
[TD]IDK-6071[/TD]
[TD]IDK-6077[/TD]
[TD]IDK-6078[/TD]
[TD]IDK-6084[/TD]
[TD]IDK-6085[/TD]
[TD]IDK-6086[/TD]
[TD]IDL-2612[/TD]
[TD]IDL-2613[/TD]
[TD]IDL-2614[/TD]
[TD]IDL-2615[/TD]
[TD]IDL-2616[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

4​

[/TD]
[TD]Multi Deco 12K[/TD]
[TD]INL-6021[/TD]
[TD]INL-6022[/TD]
[TD]INL-6058[/TD]
[TD]INL-6059[/TD]
[TD]INL-6066[/TD]
[TD]INL-6070[/TD]
[TD]INL-6071[/TD]
[TD]INL-6077[/TD]
[TD]INL-6078[/TD]
[TD]INL-6084[/TD]
[TD]INL-6085[/TD]
[TD]INL-6086[/TD]
[TD]INO-2612[/TD]
[TD]INO-2613[/TD]
[TD]INO-2614[/TD]
[TD]INO-2615[/TD]
[TD]INO-2616[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

5​

[/TD]
[TD]Multi Deco 1E[/TD]
[TD]IDK-6021[/TD]
[TD]IDK-6022[/TD]
[TD]IDK-6058[/TD]
[TD]IDK-6059[/TD]
[TD]IDK-6066[/TD]
[TD]IDK-6070[/TD]
[TD]IDK-6071[/TD]
[TD]IDK-6077[/TD]
[TD]IDK-6078[/TD]
[TD]IDK-6084[/TD]
[TD]IDK-6085[/TD]
[TD]IDK-6086[/TD]
[TD]IDL-2612[/TD]
[TD]IDL-2613[/TD]
[TD]IDL-2614[/TD]
[TD]IDL-2615[/TD]
[TD]IDL-2616[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

6​

[/TD]
[TD]Multi Deco 1E[/TD]
[TD]INL-6021[/TD]
[TD]INL-6022[/TD]
[TD]INL-6058[/TD]
[TD]INL-6059[/TD]
[TD]INL-6066[/TD]
[TD]INL-6070[/TD]
[TD]INL-6071[/TD]
[TD]INL-6077[/TD]
[TD]INL-6078[/TD]
[TD]INL-6084[/TD]
[TD]INL-6085[/TD]
[TD]INL-6086[/TD]
[TD]INO-2612[/TD]
[TD]INO-2613[/TD]
[TD]INO-2614[/TD]
[TD]INO-2615[/TD]
[TD]INO-2616[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

7​

[/TD]
[TD]Multi Deco 12K[/TD]
[TD]IDK-6021[/TD]
[TD]IDK-6022[/TD]
[TD]IDK-6058[/TD]
[TD]IDK-6059[/TD]
[TD]IDK-6066[/TD]
[TD]IDK-6070[/TD]
[TD]IDK-6071[/TD]
[TD]IDK-6077[/TD]
[TD]IDK-6078[/TD]
[TD]IDK-6084[/TD]
[TD]IDK-6085[/TD]
[TD]IDK-6086[/TD]
[TD]IDL-2612[/TD]
[TD]IDL-2613[/TD]
[TD]IDL-2614[/TD]
[TD]IDL-2615[/TD]
[TD]IDL-2616[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

8​

[/TD]
[TD]Multi Deco 12K[/TD]
[TD]INL-6021[/TD]
[TD]INL-6022[/TD]
[TD]INL-6058[/TD]
[TD]INL-6059[/TD]
[TD]INL-6066[/TD]
[TD]INL-6070[/TD]
[TD]INL-6071[/TD]
[TD]INL-6077[/TD]
[TD]INL-6078[/TD]
[TD]INL-6084[/TD]
[TD]INL-6085[/TD]
[TD]INL-6086[/TD]
[TD]INO-2612[/TD]
[TD]INO-2613[/TD]
[TD]INO-2614[/TD]
[TD]INO-2615[/TD]
[TD]INO-2616[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

9​

[/TD]
[TD]Old Product[/TD]
[TD]IDK-6021[/TD]
[TD]IDK-6022[/TD]
[TD]IDK-6058[/TD]
[TD]IDK-6059[/TD]
[TD]IDK-6066[/TD]
[TD]IDK-6070[/TD]
[TD]IDK-6071[/TD]
[TD]IDK-6077[/TD]
[TD]IDK-6078[/TD]
[TD]IDK-6084[/TD]
[TD]IDK-6085[/TD]
[TD]IDK-6086[/TD]
[TD]IDL-2612[/TD]
[TD]IDL-2613[/TD]
[TD]IDL-2614[/TD]
[TD]IDL-2615[/TD]
[TD]IDL-2616[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

10​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

11​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

12​

[/TD]
[TD]Post Production Data:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

13​

[/TD]
[TD]Product ID[/TD]
[TD]Part1 ID[/TD]
[TD]Part2 ID[/TD]
[TD]Part3 ID[/TD]
[TD]Part4 ID[/TD]
[TD]Par5 ID[/TD]
[TD]Part6 ID[/TD]
[TD]Part7 ID[/TD]
[TD]Part8 ID[/TD]
[TD]Part9 ID[/TD]
[TD]Part10 ID[/TD]
[TD]Part11 ID[/TD]
[TD]Part12 ID[/TD]
[TD]Part13 ID[/TD]
[TD]Part14 ID[/TD]
[TD]Part15 ID[/TD]
[TD]Part16 ID[/TD]
[TD]Part17 ID[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

14​

[/TD]
[TD]Multi Deco 12K[/TD]
[TD]IDK-6021[/TD]
[TD]IDK-6022[/TD]
[TD]IDK-6058[/TD]
[TD]IDK-6059[/TD]
[TD]IDK-6066[/TD]
[TD]IDK-6070[/TD]
[TD]IDK-6071[/TD]
[TD]IDK-6077[/TD]
[TD]IDK-6078[/TD]
[TD]IDK-6084[/TD]
[TD]IDK-6085[/TD]
[TD]IDK-6086[/TD]
[TD]IDL-2612[/TD]
[TD]IDL-2613[/TD]
[TD]IDL-2614[/TD]
[TD]IDL-2615[/TD]
[TD]IDL-2616[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

15​

[/TD]
[TD]Multi Deco 12K[/TD]
[TD]INL-6020[/TD]
[TD]INL-6022[/TD]
[TD]INL-6058[/TD]
[TD]INL-6059[/TD]
[TD]INL-6066[/TD]
[TD]INL-6070[/TD]
[TD]INL-6071[/TD]
[TD]INL-6077[/TD]
[TD]INL-6078[/TD]
[TD]INL-6084[/TD]
[TD]INL-6085[/TD]
[TD]INL-6086[/TD]
[TD]INO-2612[/TD]
[TD]INO-2613[/TD]
[TD]INO-2614[/TD]
[TD]INO-2615[/TD]
[TD]INO-2616[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

16​

[/TD]
[TD]Multi Deco 1E[/TD]
[TD]IDK-6021[/TD]
[TD]IDK-6022[/TD]
[TD]IDK-6058[/TD]
[TD]IDK-6059[/TD]
[TD]IDK-6066[/TD]
[TD]IDK-6070[/TD]
[TD]IDK-6071[/TD]
[TD]IDK-6077[/TD]
[TD]IDK-6078[/TD]
[TD]IDK-6084[/TD]
[TD]IDK-6085[/TD]
[TD]IDK-6086[/TD]
[TD]IDL-2612[/TD]
[TD]IDL-2613[/TD]
[TD]IDL-2614[/TD]
[TD]IDL-2615[/TD]
[TD]IDL-2616[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

17​

[/TD]
[TD]Multi Deco 1E[/TD]
[TD]INL-6020[/TD]
[TD]INL-6022[/TD]
[TD]INL-6059[/TD]
[TD]INL-6059[/TD]
[TD]INL-6066[/TD]
[TD]INL-6070[/TD]
[TD]INL-6071[/TD]
[TD]INL-6077[/TD]
[TD]INL-6078[/TD]
[TD]INL-6084[/TD]
[TD]INL-6085[/TD]
[TD]INL-6086[/TD]
[TD]INO-2612[/TD]
[TD]INO-2613[/TD]
[TD]INO-2614[/TD]
[TD]INO-2615[/TD]
[TD]INO-2616[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

18​

[/TD]
[TD]Multi Deco 12K[/TD]
[TD]IDK-6021[/TD]
[TD]IDK-6022[/TD]
[TD]IDK-6058[/TD]
[TD]IDK-6059[/TD]
[TD]IDK-6066[/TD]
[TD]IDK-6070[/TD]
[TD]IDK-6071[/TD]
[TD]IDK-6077[/TD]
[TD]IDK-6078[/TD]
[TD]IDK-6084[/TD]
[TD]IDK-6085[/TD]
[TD]IDK-6086[/TD]
[TD]IDL-2612[/TD]
[TD]IDL-2613[/TD]
[TD]IDL-2614[/TD]
[TD]IDL-2615[/TD]
[TD]IDL-2616[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

19​

[/TD]
[TD]Multi Deco 12K[/TD]
[TD]INL-6021[/TD]
[TD]INL-6022[/TD]
[TD]INL-6058[/TD]
[TD]INL-6059[/TD]
[TD]INL-6066[/TD]
[TD]INL-6070[/TD]
[TD]INL-6071[/TD]
[TD]INL-6077[/TD]
[TD]INL-6078[/TD]
[TD]INL-6084[/TD]
[TD]INL-6085[/TD]
[TD]INL-6086[/TD]
[TD]INO-2612[/TD]
[TD]INO-2613[/TD]
[TD]INO-2614[/TD]
[TD]INO-2615[/TD]
[TD]INO-2616[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

20​

[/TD]
[TD]New Product[/TD]
[TD]IDK-6021[/TD]
[TD]IDK-6022[/TD]
[TD]IDK-6058[/TD]
[TD]IDK-6059[/TD]
[TD]IDK-6066[/TD]
[TD]IDK-6070[/TD]
[TD]IDK-6071[/TD]
[TD]IDK-6077[/TD]
[TD]IDK-6078[/TD]
[TD]IDK-6084[/TD]
[TD]IDK-6085[/TD]
[TD]IDK-6086[/TD]
[TD]IDL-2612[/TD]
[TD]IDL-2613[/TD]
[TD]IDL-2614[/TD]
[TD]IDL-2615[/TD]
[TD]IDL-2616[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

21​

[/TD]
[TD]Multi Deco 1E[/TD]
[TD]INL-6021[/TD]
[TD]INL-6022[/TD]
[TD]INL-6058[/TD]
[TD]INL-6059[/TD]
[TD]INL-6066[/TD]
[TD]INL-6070[/TD]
[TD]INL-6071[/TD]
[TD]INL-6077[/TD]
[TD]INL-6078[/TD]
[TD]INL-6084[/TD]
[TD]INL-6085[/TD]
[TD]INL-6086[/TD]
[TD]INO-2612[/TD]
[TD]INO-2613[/TD]
[TD]INO-2614[/TD]
[TD]INO-2615[/TD]
[TD]INO-2616[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

22​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

23​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

24​

[/TD]
[TD][/TD]
[TD]

sample result​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

25​

[/TD]
[TD][/TD]
[TD]New Data[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

26​

[/TD]
[TD="bgcolor: #D9D9D9"]Product ID[/TD]
[TD="bgcolor: #D9D9D9"]Status[/TD]
[TD="bgcolor: #D9D9D9"]Part1 ID[/TD]
[TD="bgcolor: #D9D9D9"]Part2 ID[/TD]
[TD="bgcolor: #D9D9D9"]Part3 ID[/TD]
[TD="bgcolor: #D9D9D9"]Part4 ID[/TD]
[TD="bgcolor: #D9D9D9"]Par5 ID[/TD]
[TD="bgcolor: #D9D9D9"]Part6 ID[/TD]
[TD="bgcolor: #D9D9D9"]Part7 ID[/TD]
[TD="bgcolor: #D9D9D9"]Part8 ID[/TD]
[TD="bgcolor: #D9D9D9"]Part9 ID[/TD]
[TD="bgcolor: #D9D9D9"]Part10 ID[/TD]
[TD="bgcolor: #D9D9D9"]Part11 ID[/TD]
[TD="bgcolor: #D9D9D9"]Part12 ID[/TD]
[TD="bgcolor: #D9D9D9"]Part13 ID[/TD]
[TD="bgcolor: #D9D9D9"]Part14 ID[/TD]
[TD="bgcolor: #D9D9D9"]Part15 ID[/TD]
[TD="bgcolor: #D9D9D9"]Part16 ID[/TD]
[TD="bgcolor: #D9D9D9"]Part17 ID[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

27​

[/TD]
[TD]Multi Deco 12K[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

28​

[/TD]
[TD]Multi Deco 12K[/TD]
[TD]Upgraded[/TD]
[TD="bgcolor: #FFFF00"]Upgraded[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

29​

[/TD]
[TD]Multi Deco 1E[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

30​

[/TD]
[TD]Multi Deco 1E[/TD]
[TD]Upgraded[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD="bgcolor: #FFFF00"]Upgraded[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

31​

[/TD]
[TD]Multi Deco 12K[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

32​

[/TD]
[TD]Multi Deco 12K[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[TD]Existing[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

33​

[/TD]
[TD]Old Product[/TD]
[TD]Discontinue[/TD]
[TD]Discontinue[/TD]
[TD]Discontinue[/TD]
[TD]Discontinue[/TD]
[TD]Discontinue[/TD]
[TD]Discontinue[/TD]
[TD]Discontinue[/TD]
[TD]Discontinue[/TD]
[TD]Discontinue[/TD]
[TD]Discontinue[/TD]
[TD]Discontinue[/TD]
[TD]Discontinue[/TD]
[TD]Discontinue[/TD]
[TD]Discontinue[/TD]
[TD]Discontinue[/TD]
[TD]Discontinue[/TD]
[TD]Discontinue[/TD]
[TD]Discontinue[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

34​

[/TD]
[TD]Multi Deco 1E[/TD]
[TD]New Product[/TD]
[TD]New Product[/TD]
[TD]New Product[/TD]
[TD]New Product[/TD]
[TD]New Product[/TD]
[TD]New Product[/TD]
[TD]New Product[/TD]
[TD]New Product[/TD]
[TD]New Product[/TD]
[TD]New Product[/TD]
[TD]New Product[/TD]
[TD]New Product[/TD]
[TD]New Product[/TD]
[TD]New Product[/TD]
[TD]New Product[/TD]
[TD]New Product[/TD]
[TD]New Product[/TD]
[TD]New Product[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Sheet1[/TD]
[/TR]
</tbody>[/TABLE]

Any help would be much appreciated.

 

[Jeffrey Mahoney]

You have duplicate Product ID's. I was going to create formulas that gave you what you needed, but the product ID column must contain no duplicate values for that to work. I think if I were to create a macro to do the same, the Product ID's would still have to be changed also.

 

[GreatGemini]

I can change product IDs to unique no problem so please assume all Product IDs are unique.

 

[GreatGemini]

There are around 650,000 records so I think Macro would be better than formula. Just a thought!

 

[Joe4]

There are around 650,000 records so I think Macro would be better than formula. Just a thought!

650,000 records?!?!

Do you know what would even better? A database program, since that is what you are really dealing with (something like Access, SQL, MySQL, or Oracle).
You can manipulate Excel to work like a database, but you often will find that the performance will be pretty poor (as that is not really what Excel was designed for).

 

[GreatGemini]

Unfortunately here I have to stick with Excel.

 

[Jeffrey Mahoney]

Try this. You will need to create some named ranges. These are based on your example, but the named ranges can be changed to reflect your true data set.

PreHeaders = C2:S2 (the headers for pre production)
PrePID = B3:B9 (the product ID's for pre production
PostHeaders = C13:S13
PostPID = B14:B21
PreTable = C3:S9 (the pre production table range not including the headers and product ID's)
PostTable = C14:S21
StatusTable = B26:T34 (The entire status table including the headers and product ID's)

We may have to change some logic, but this is a start.

Sub StatusTableUpdate()
  Dim PreHeaders As Range
  Dim PrePID As Range
  Dim PostHeaders As Range
  Dim PostPID As Range
  Dim PreTable As Range
  Dim PostTable As Range
  Dim StatusTable As Range
  Dim PIDCol As Long
  Dim StatusCol As Long
  Dim HdrsRow As Long
  Dim X As Long
  Dim Y As Long
  Dim FirstCol As Long
  Dim LastCol As Long
  Dim FirstRow As Long
  Dim LastRow As Long
  Dim TL As Range
  Dim PID As String
  Dim PartID As String
  Dim PrePart As String
  Dim PostPart As String
  Dim NewDataStat As String
  
  Application.EnableEvents = False
  Application.Calculation = xlCalculationManual
  Application.ScreenUpdating = False
  
  Set PreHeaders = Range("PreHeaders")
  Set PrePID = Range("PrePID")
  Set PostHeaders = Range("PostHeaders")
  Set PostPID = Range("PostPID")
  Set PreTable = Range("PreTable")
  Set PostTable = Range("PostTable")
  Set StatusTable = Range("StatusTable")
  
  Set TL = StatusTable.Resize(1, 1)
  PIDCol = TL.Column
  StatusCol = PIDCol + 1
  HdrsRow = TL.Row
  FirstCol = PIDCol + 2
  LastCol = StatusTable.Columns.Count + PIDCol - 1
  FirstRow = TL.Row + 1
  LastRow = StatusTable.Rows.Count + HdrsRow - 1
  
  For Y = FirstRow To LastRow
    NewDataStat = "Existing"
    PID = Cells(Y, PIDCol).Value
    For X = FirstCol To LastCol
      
      PartID = Cells(HdrsRow, X).Value
      PrePart = "NA"
      PostPart = "NA"
      On Error Resume Next
      PrePart = Application.Index(PreTable, Application.Match(PID, PrePID, 0), Application.Match(PartID, PreHeaders, 0))
      PostPart = Application.Index(PostTable, Application.Match(PID, PostPID, 0), Application.Match(PartID, PostHeaders, 0))
      On Error GoTo 0
      
      If PrePart = PostPart Then
        Cells(Y, X).Value = "Existing"
      ElseIf PrePart = "NA" Or PostPart = "NA" Then
        Cells(Y, X).Value = "Discontinue"
        NewDataStat = "Discontinue"
      ElseIf PrePart <> PostPart Then
        Cells(Y, X).Value = "Upgraded"
        NewDataStat = "Upgraded"
      End If
    Next X
    
    Cells(Y, StatusCol) = NewDataStat
  Next Y
  
      
  Application.EnableEvents = True
  Application.Calculation = xlCalculationAutomatic
  Application.ScreenUpdating = True
  
  
End Sub

 

[Jeffrey Mahoney]

The nice part about using named ranges is that you can change the focus of the range without having to alter the macro. I would definitely consider using dynamic named ranges.

Since your data set is so large, you may want to put each of those tables on a different sheet. Using Workbook level named ranges will aid in this process.