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.

 

[kevin9999]

Open to doing this outside of an Excel Table

That being the case, this is one suggestion:

Book1
	A	B	C	D	E	F	G	H	I	J
1	Name	Grade		Name	Grade			Name	Grade
2	John	12th		John	12th			John	12th
3	Jane	15th		Jane	15th			Jane	15th
4	Jack	8th		Jack	8th			Jack	8th
5	Mike	8th		Jane	15th			Mike	8th
6	Susy	1st		Jack	8th			Susy	1st
7
Sheet1

Cell Formulas
Range	Formula
H1:I6	H1	=UNIQUE(VSTACK(Table1[#All],Table2[#All]))
Dynamic array formulas.

 

[Peter_SSs]

That being the case, this is one suggestion:

That doesn't produce the stated expected outcome though.

@Coyotex3
Can you explain more about the logic of why those particular values are in the result?

 

[kevin9999]

That doesn't produce the stated expected outcome though.

Doh!

Perhaps this then, although not in the same stated order:

Book1
	A	B	C	D	E	F	G	H	I	J
1	Name	Grade		Name	Grade			Name	Grade
2	John	12th		John	12th			John	12th
3	Jane	15th		Jane	15th			Jane	15th
4	Jack	8th		Jack	8th			Jack	8th
5	Mike	8th		Jane	15th			Mike	8th
6	Susy	1st		Jack	8th			Susy	1st
7								John	12th
8								Jane	15th
9								Jack	8th
10
Sheet1

Cell Formulas
Range	Formula
H1:I1	H1	=Table1[#Headers]
H2:I9	H2	=VSTACK(UNIQUE(Table1),UNIQUE(Table2))
Dynamic array formulas.

 

[Coyotex3]

Kevin, thanks for the answer. Ideally I’d like VBA for this one. As Peter mentioned the formula does not replicate the results desired.

@Peter_SSs sure thing. This was just some made up data.

On The real data the second table has three columns which contain some items, subitems, and a name.

The first table contains items and subitems which were not attributed to any of the names on the second table.

I’d like to get a list of those items and subitems in order to add it to the end of the second table, and then add something like “No name associated.” On the name column.

Hope this makes sense, if not, happy to share an example with data that makes more sense

 

[Peter_SSs]

Perhaps this then, although not in the same stated order:

.. but still different anyway (unless the full result was not shown in post 1). Stated result shows 8 rows including heading row. Yours has 9 rows.

Hope this makes sense,

Not to me. I still don't understand the sample results in post 1.

In any case it seems the structure of the sample data in post 1 is not like the structure of your real data. Perhaps a new set of sample data and expected results with the actual structure & explain again in relation to that?

 

[Coyotex3]

Sorry! I will try again.

Here is Range 1:

Book1
	A	B	C	D
1	Category	Item	Revenue
2	Plumbing	Item A	100
3	Electricity	Item A	500
4	Engineering	Item A	1000
5	Plumbing	Item B	1500
6	Electricity	Item B	200
7	Engineering	Item B	2000
8	Accounting	Item B	2000
9	Plumbing	Item C	400
10	Electricity	Item C	1000
11
12
Sheet3

And here is range 2:

Book1
	H	I	J	K
1	Category	Item	Worker
2	Plumbing	Item A	Jack
3	Electricity	Item A	Jane
4	Engineering	Item A	Jack
5	Plumbing	Item B	Jane
6	Electricity	Item B	Jack
7	Engineering	Item B	Jane
8	Plumbing	Item C	Jane
9	Plumbing	Item A	Josh
10	Electricity	Item A	Mike
11	Engineering	Item A	Mike
12	Plumbing	Item B	Mike
13	Electricity	Item B	Susy
14	Engineering	Item B	Jack
15	Plumbing	Item C	Jack
16
Sheet3

Notice the highlighted rows, how Accounting Item B and Electricity Item C are not showing up on Range 2? However, there is income associated with those items, but no worker reported being the one responsible for the income. Therefore, I'd like to add those two rows to the bottom of Range 2 to get something like this.

Book1
	H	I	J
1	Category	Item	Worker
2	Plumbing	Item A	Jack
3	Electricity	Item A	Jane
4	Engineering	Item A	Jack
5	Plumbing	Item B	Jane
6	Electricity	Item B	Jack
7	Engineering	Item B	Jane
8	Plumbing	Item C	Jane
9	Plumbing	Item A	Josh
10	Electricity	Item A	Mike
11	Engineering	Item A	Mike
12	Plumbing	Item B	Mike
13	Electricity	Item B	Susy
14	Engineering	Item B	Jack
15	Plumbing	Item C	Jack
16	Accounting	Item B	No Name associated
17	Electricity	Item C	No Name associated
Sheet3

Hopes this makes a little more sense. This will go inside of a larger code that is helping gather financial data.

PS. I left these as Ranges. Happy to work with either Range or Table.

 

[Peter_SSs]

Hopes this makes a little more sense.

It does.

Try this with a copy of your data.

Sub Test()
  Dim d As Object
  Dim a As Variant
  Dim i As Long
  
  Set d = CreateObject("Scripting.Dictionary")
  a = Range("H2", Range("J" & Rows.Count).End(xlUp)).Value
  For i = 1 To UBound(a)
    d(a(i, 1) & "|" & a(i, 2)) = 1
  Next i
  a = Range("A2", Range("B" & Rows.Count).End(xlUp)).Value
  Application.ScreenUpdating = False
  For i = 1 To UBound(a)
    If Not d.exists(a(i, 1) & "|" & a(i, 2)) Then
      d(a(i, 1) & "|" & a(i, 2)) = 1
      With Range("H" & Rows.Count).End(xlUp).Offset(1).Resize(, 3)
        .Cells(1).Value = a(i, 1): .Cells(2).Value = a(i, 2): .Cells(3).Value = "No Name associated"
      End With
    End If
  Next i
  Application.ScreenUpdating = True
End Sub

 

[Coyotex3]

It does.

Try this with a copy of your data.

Sub Test()
  Dim d As Object
  Dim a As Variant
  Dim i As Long
 
  Set d = CreateObject("Scripting.Dictionary")
  a = Range("H2", Range("J" & Rows.Count).End(xlUp)).Value
  For i = 1 To UBound(a)
    d(a(i, 1) & "|" & a(i, 2)) = 1
  Next i
  a = Range("A2", Range("B" & Rows.Count).End(xlUp)).Value
  Application.ScreenUpdating = False
  For i = 1 To UBound(a)
    If Not d.exists(a(i, 1) & "|" & a(i, 2)) Then
      d(a(i, 1) & "|" & a(i, 2)) = 1
      With Range("H" & Rows.Count).End(xlUp).Offset(1).Resize(, 3)
        .Cells(1).Value = a(i, 1): .Cells(2).Value = a(i, 2): .Cells(3).Value = "No Name associated"
      End With
    End If
  Next i
  Application.ScreenUpdating = True
End Sub

Thanks Peter. I’ll give this code a run once I’m back on the laptop in a few hours and let you know!

 

[Coyotex3]

It does.

Try this with a copy of your data.

Sub Test()
  Dim d As Object
  Dim a As Variant
  Dim i As Long
 
  Set d = CreateObject("Scripting.Dictionary")
  a = Range("H2", Range("J" & Rows.Count).End(xlUp)).Value
  For i = 1 To UBound(a)
    d(a(i, 1) & "|" & a(i, 2)) = 1
  Next i
  a = Range("A2", Range("B" & Rows.Count).End(xlUp)).Value
  Application.ScreenUpdating = False
  For i = 1 To UBound(a)
    If Not d.exists(a(i, 1) & "|" & a(i, 2)) Then
      d(a(i, 1) & "|" & a(i, 2)) = 1
      With Range("H" & Rows.Count).End(xlUp).Offset(1).Resize(, 3)
        .Cells(1).Value = a(i, 1): .Cells(2).Value = a(i, 2): .Cells(3).Value = "No Name associated"
      End With
    End If
  Next i
  Application.ScreenUpdating = True
End Sub

Thank you Peter. This works as expected!