VBA: Comparing two tables and adding unique values from 1 table to another

[Coyotex3]

Looking for a simplified way of comparing two tables and adding the unique values from one table to another table.

I have these two tables:

Book1
	A	B	C	D	E
1	Name	Grade		Name	Grade
2	John	12th		John	12th
3	Jane	15th		Jane	15th
4	Jack	8th		Jack	8th
5	Mike	8th		Jane	15th
6	Susy	1st		Jack	8th
Sheet2

Ideally I would like to get one table that shows this:

Book1
	H	I
1	Name	Grade
2	John	12th
3	Jane	15th
4	Jack	8th
5	Jane	15th
6	Jack	8th
7	Mike	8th
8	Susy	1st
Sheet2

Open to doing this outside of an Excel Table, if that is easier? The results can be added to the second table, or in a new table/range altogether.

 

[Coyotex3]

@Peter_SSs Happy to start a new thread since this is a different question.

But would it be possible to perhaps show the revenue of all the items in Range 1 and add them to Range 2 Column J as such:

Book1
	H	I	J	K	L
1	Category	Item	Worker	Revenue
2	Plumbing	Item A	Jack	100
3	Electricity	Item A	Jane	500
4	Engineering	Item A	Jack	1000
5	Plumbing	Item B	Jane	1500
6	Electricity	Item B	Jack	200
7	Engineering	Item B	Jane	2000
8	Plumbing	Item C	Jane	400
9	Plumbing	Item A	Josh	100
10	Electricity	Item A	Mike	500
11	Engineering	Item A	Mike	1000
12	Plumbing	Item B	Mike	1500
13	Electricity	Item B	Susy	200
14	Engineering	Item B	Jack	2000
15	Plumbing	Item C	Jack	400
16	Accounting	Item B	No Name associated	2000
17	Electricity	Item C	No Name associated	1000
18
Sheet3

 

[Alex Blakenburg]

Are sure you want to do that ?
Range 1 are unique combinations for Category and Item. Range 2 has multiple occurences of those combinations split by Worker.
By assigning the "total value" for each Category and Item to the each worker you are effectively going to be double counting. The totals won't match.
This is going to be very misleading.

 

[Peter_SSs]

But would it be possible to perhaps show the revenue of all the items in Range 1 and add them to Range 2 Column J as such:

Try this

Sub Test_v2()
  Dim d1 As Object, d2 As Object
  Dim a As Variant, b As Variant
  Dim i As Long, k As Long, lrH As Long
  Dim s As String
  
  Set d1 = CreateObject("Scripting.Dictionary")
  Set d2 = CreateObject("Scripting.Dictionary")
  b = Range("A2", Range("C" & Rows.Count).End(xlUp)).Value
  For i = 1 To UBound(b)
    d2(b(i, 1) & "|" & b(i, 2)) = b(i, 3)
  Next i
  lrH = Range("H" & Rows.Count).End(xlUp).Row
  k = lrH - 1
  a = Range("H2:K" & k + 1 + UBound(b)).Value
  For i = 1 To k
    s = a(i, 1) & "|" & a(i, 2)
    d1(s) = 1
    a(i, 4) = d2(s)
  Next i
  For i = 1 To UBound(b)
    s = b(i, 1) & "|" & b(i, 2)
    If Not d1.exists(s) Then
      k = k + 1
      a(k, 1) = b(i, 1): a(k, 2) = b(i, 2): a(k, 3) = "No Name associated": a(k, 4) = d2(s)
    End If
  Next i
  Range("H2:K2").Resize(k).Value = a
End Sub

 

[Coyotex3]

Are sure you want to do that ?
Range 1 are unique combinations for Category and Item. Range 2 has multiple occurences of those combinations split by Worker.
By assigning the "total value" for each Category and Item to the each worker you are effectively going to be double counting. The totals won't match.
This is going to be very misleading.

Alex that is true!

The reason why I’d like to get the income displayed on the right column, is because later I will be multiplying this amount by the percentage of hours attributed to each employee to allocate the income proportionally.

This little piece of code is a small piece of a bigger code I’m working on revamping which is pulling different data from different sheets. It’s been a huge undertaking and I’ve been experimenting with trial and error to figure out what works and what doesn’t .

 

[Coyotex3]

Try this

Sub Test_v2()
  Dim d1 As Object, d2 As Object
  Dim a As Variant, b As Variant
  Dim i As Long, k As Long, lrH As Long
  Dim s As String
 
  Set d1 = CreateObject("Scripting.Dictionary")
  Set d2 = CreateObject("Scripting.Dictionary")
  b = Range("A2", Range("C" & Rows.Count).End(xlUp)).Value
  For i = 1 To UBound(b)
    d2(b(i, 1) & "|" & b(i, 2)) = b(i, 3)
  Next i
  lrH = Range("H" & Rows.Count).End(xlUp).Row
  k = lrH - 1
  a = Range("H2:K" & k + 1 + UBound(b)).Value
  For i = 1 To k
    s = a(i, 1) & "|" & a(i, 2)
    d1(s) = 1
    a(i, 4) = d2(s)
  Next i
  For i = 1 To UBound(b)
    s = b(i, 1) & "|" & b(i, 2)
    If Not d1.exists(s) Then
      k = k + 1
      a(k, 1) = b(i, 1): a(k, 2) = b(i, 2): a(k, 3) = "No Name associated": a(k, 4) = d2(s)
    End If
  Next i
  Range("H2:K2").Resize(k).Value = a
End Sub

@Peter_SSs this is phenomenal. Can't really say I understand much how this array is working, but I love the array approach since it is faster and the lines of code tend to be shorter. The bigger code I'm working on utilizes a lot of worksheet functions as I don't really know how to do calculations within an array.

With your approach, is it possible to do calculations such as sumifs? Or does that need to be accomplished with FormulaR1C1 & Worksheet Functions?

 

[Peter_SSs]

With your approach, is it possible to do calculations such as sumifs? Or does that need to be accomplished with FormulaR1C1 & Worksheet Functions?

I'm sure it would be possible but it is hard to say what the best approach would be without knowing the exact requirement(s).