Organize table according to key number

[Pietro Di Micio]

Hey Guys, here is my challenge; looking to the table below you will see basically two sides, one of them VARIABLE (always updated by copy paste from another excel) and other FIXED; in common we have a KEY NUMBER.

1) B5, B6, B7... needs to bring red color if not match column F; I believe something easy thru conditional formatting
2) F5, F6, F7... needs to bring red color if not match column B; I believe something easy thru conditional formatting
3) I would like column F to be matching column B by KEY NUMBER and keeping the line content in both; for example, after I copied data in column B, C, D and E, column F, G H and I thru a button or not could be re organizer/filtered matching line by line and data by data.
4) By doing that I will be able to compare if we had changes (topic 1 and 2 - if not match) and keep historial infos on columns G, H and I


Thanks in advance.

1)

 

[DanteAmor]

On a copy of your sheet try the following macro:

Sub Organize_Table()
  Dim a As Variant, b As Variant, c As Variant, d As Variant
  Dim dic As Object
  Dim i&, j&, k&, m&, lr&
  
  lr = Range("B" & Rows.Count).End(3).Row
  a = Range("B4:B" & lr).Value
  b = Range("F4:I" & Range("F" & Rows.Count).End(3).Row).Value
  ReDim c(1 To UBound(a) + UBound(b), 1 To UBound(b, 2))
  ReDim d(1 To UBound(a) + UBound(b), 1 To UBound(b, 2))
  
  Set dic = CreateObject("Scripting.Dictionary")
  
  For i = 1 To UBound(a)
    dic(a(i, 1)) = i
  Next

  For i = 1 To UBound(b)
    If dic.exists(b(i, 1)) Then
      k = dic(b(i, 1))
      For j = 1 To UBound(b, 2)
        c(k, j) = b(i, j)
      Next
    Else
      m = m + 1
      For j = 1 To UBound(b, 2)
        d(m, j) = b(i, j)
      Next
    End If
  Next
  
  Range("F4").Resize(UBound(c, 1), UBound(c, 2)).Value = c
  Range("F" & lr + 1).Resize(UBound(d, 1), UBound(d, 2)).Value = d
End Sub

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --