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

	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​	L​	M​	N​	O​	P​	Q​	R​	S​	T​
1​	Pre Production Data:
2​	Product ID	Part1 ID	Part2 ID	Part3 ID	Part4 ID	Par5 ID	Part6 ID	Part7 ID	Part8 ID	Part9 ID	Part10 ID	Part11 ID	Part12 ID	Part13 ID	Part14 ID	Part15 ID	Part16 ID	Part17 ID
3​	Multi Deco 12K	IDK-6021	IDK-6022	IDK-6058	IDK-6059	IDK-6066	IDK-6070	IDK-6071	IDK-6077	IDK-6078	IDK-6084	IDK-6085	IDK-6086	IDL-2612	IDL-2613	IDL-2614	IDL-2615	IDL-2616
4​	Multi Deco 12K	INL-6021	INL-6022	INL-6058	INL-6059	INL-6066	INL-6070	INL-6071	INL-6077	INL-6078	INL-6084	INL-6085	INL-6086	INO-2612	INO-2613	INO-2614	INO-2615	INO-2616
5​	Multi Deco 1E	IDK-6021	IDK-6022	IDK-6058	IDK-6059	IDK-6066	IDK-6070	IDK-6071	IDK-6077	IDK-6078	IDK-6084	IDK-6085	IDK-6086	IDL-2612	IDL-2613	IDL-2614	IDL-2615	IDL-2616
6​	Multi Deco 1E	INL-6021	INL-6022	INL-6058	INL-6059	INL-6066	INL-6070	INL-6071	INL-6077	INL-6078	INL-6084	INL-6085	INL-6086	INO-2612	INO-2613	INO-2614	INO-2615	INO-2616
7​	Multi Deco 12K	IDK-6021	IDK-6022	IDK-6058	IDK-6059	IDK-6066	IDK-6070	IDK-6071	IDK-6077	IDK-6078	IDK-6084	IDK-6085	IDK-6086	IDL-2612	IDL-2613	IDL-2614	IDL-2615	IDL-2616
8​	Multi Deco 12K	INL-6021	INL-6022	INL-6058	INL-6059	INL-6066	INL-6070	INL-6071	INL-6077	INL-6078	INL-6084	INL-6085	INL-6086	INO-2612	INO-2613	INO-2614	INO-2615	INO-2616
9​	Old Product	IDK-6021	IDK-6022	IDK-6058	IDK-6059	IDK-6066	IDK-6070	IDK-6071	IDK-6077	IDK-6078	IDK-6084	IDK-6085	IDK-6086	IDL-2612	IDL-2613	IDL-2614	IDL-2615	IDL-2616
10​
11​
12​	Post Production Data:
13​	Product ID	Part1 ID	Part2 ID	Part3 ID	Part4 ID	Par5 ID	Part6 ID	Part7 ID	Part8 ID	Part9 ID	Part10 ID	Part11 ID	Part12 ID	Part13 ID	Part14 ID	Part15 ID	Part16 ID	Part17 ID
14​	Multi Deco 12K	IDK-6021	IDK-6022	IDK-6058	IDK-6059	IDK-6066	IDK-6070	IDK-6071	IDK-6077	IDK-6078	IDK-6084	IDK-6085	IDK-6086	IDL-2612	IDL-2613	IDL-2614	IDL-2615	IDL-2616
15​	Multi Deco 12K	INL-6020	INL-6022	INL-6058	INL-6059	INL-6066	INL-6070	INL-6071	INL-6077	INL-6078	INL-6084	INL-6085	INL-6086	INO-2612	INO-2613	INO-2614	INO-2615	INO-2616
16​	Multi Deco 1E	IDK-6021	IDK-6022	IDK-6058	IDK-6059	IDK-6066	IDK-6070	IDK-6071	IDK-6077	IDK-6078	IDK-6084	IDK-6085	IDK-6086	IDL-2612	IDL-2613	IDL-2614	IDL-2615	IDL-2616
17​	Multi Deco 1E	INL-6020	INL-6022	INL-6059	INL-6059	INL-6066	INL-6070	INL-6071	INL-6077	INL-6078	INL-6084	INL-6085	INL-6086	INO-2612	INO-2613	INO-2614	INO-2615	INO-2616
18​	Multi Deco 12K	IDK-6021	IDK-6022	IDK-6058	IDK-6059	IDK-6066	IDK-6070	IDK-6071	IDK-6077	IDK-6078	IDK-6084	IDK-6085	IDK-6086	IDL-2612	IDL-2613	IDL-2614	IDL-2615	IDL-2616
19​	Multi Deco 12K	INL-6021	INL-6022	INL-6058	INL-6059	INL-6066	INL-6070	INL-6071	INL-6077	INL-6078	INL-6084	INL-6085	INL-6086	INO-2612	INO-2613	INO-2614	INO-2615	INO-2616
20​	New Product	IDK-6021	IDK-6022	IDK-6058	IDK-6059	IDK-6066	IDK-6070	IDK-6071	IDK-6077	IDK-6078	IDK-6084	IDK-6085	IDK-6086	IDL-2612	IDL-2613	IDL-2614	IDL-2615	IDL-2616
21​	Multi Deco 1E	INL-6021	INL-6022	INL-6058	INL-6059	INL-6066	INL-6070	INL-6071	INL-6077	INL-6078	INL-6084	INL-6085	INL-6086	INO-2612	INO-2613	INO-2614	INO-2615	INO-2616
22​
23​
24​		sample result​
25​		New Data
26​	Product ID	Status	Part1 ID	Part2 ID	Part3 ID	Part4 ID	Par5 ID	Part6 ID	Part7 ID	Part8 ID	Part9 ID	Part10 ID	Part11 ID	Part12 ID	Part13 ID	Part14 ID	Part15 ID	Part16 ID	Part17 ID
27​	Multi Deco 12K	Existing	Existing	Existing	Existing	Existing	Existing	Existing	Existing	Existing	Existing	Existing	Existing	Existing	Existing	Existing	Existing	Existing	Existing
28​	Multi Deco 12K	Upgraded	Upgraded	Existing	Existing	Existing	Existing	Existing	Existing	Existing	Existing	Existing	Existing	Existing	Existing	Existing	Existing	Existing	Existing
29​	Multi Deco 1E	Existing	Existing	Existing	Existing	Existing	Existing	Existing	Existing	Existing	Existing	Existing	Existing	Existing	Existing	Existing	Existing	Existing	Existing
30​	Multi Deco 1E	Upgraded	Existing	Existing	Upgraded	Existing	Existing	Existing	Existing	Existing	Existing	Existing	Existing	Existing	Existing	Existing	Existing	Existing	Existing
31​	Multi Deco 12K	Existing	Existing	Existing	Existing	Existing	Existing	Existing	Existing	Existing	Existing	Existing	Existing	Existing	Existing	Existing	Existing	Existing	Existing
32​	Multi Deco 12K	Existing	Existing	Existing	Existing	Existing	Existing	Existing	Existing	Existing	Existing	Existing	Existing	Existing	Existing	Existing	Existing	Existing	Existing
33​	Old Product	Discontinue	Discontinue	Discontinue	Discontinue	Discontinue	Discontinue	Discontinue	Discontinue	Discontinue	Discontinue	Discontinue	Discontinue	Discontinue	Discontinue	Discontinue	Discontinue	Discontinue	Discontinue
34​	Multi Deco 1E	New Product	New Product	New Product	New Product	New Product	New Product	New Product	New Product	New Product	New Product	New Product	New Product	New Product	New Product	New Product	New Product	New Product	New Product

<tbody>
</tbody>

Sheet: Sheet1

<tbody>
</tbody>

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.