Combining Tables into one

[dhunited12]

Hello!

I have one table with states on the left hand side going down and odd numbers on the top going across.
I have another tables with states on the left hand side going down and even numbers on the top going across.
It looks like:

1 3 5 7 ...
AK x y z a
AL b c d e
. . . . .


2 4 6 8 ...
AK f g h i
AL j k l m
. . . . .



I am trying to combine the tables and make it as such:


1 2 3 4 5 ...
AK x f y g z
AL b j c k d


I tried using HLOOKUP along with INDEXMATCH but could not figure it out. Any suggestions?

Thank you in advance!

 

[MickG]

Code Option !!!
Based on your data as below, the results should start in "Q1".

	A	B	C	D	E	F	G	H	I	J	K
1		1	3	5	7			2	4	6	8
2	AK	x	y	z	a		AK	f	g	h	i
3	AL	b	c	d	e		AL	j	k	l	m

<colgroup><col width="27" style="width: 20pt; mso-width-source: userset; mso-width-alt: 967;"> <col width="45" style="width: 34pt; mso-width-source: userset; mso-width-alt: 1592;" span="11"> <tbody> </tbody>

[COLOR="Navy"]Sub[/COLOR] MG27Jun53
[COLOR="Navy"]Dim[/COLOR] Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] a [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] nRng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dic [COLOR="Navy"]As[/COLOR] Object
[COLOR="Navy"]Dim[/COLOR] Q [COLOR="Navy"]As[/COLOR] Variant, Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Rng1 [COLOR="Navy"]As[/COLOR] Range, Rng2 [COLOR="Navy"]As[/COLOR] Range
   [COLOR="Navy"]Set[/COLOR] Rng1 = Range("A2", Range("A" & Rows.Count).End(xlUp))
   [COLOR="Navy"]Set[/COLOR] Rng2 = Range("G2", Range("G" & Rows.Count).End(xlUp))
    [COLOR="Navy"]Set[/COLOR] nRng = Union(Rng1, Rng2)
        [COLOR="Navy"]Set[/COLOR] Dic = CreateObject("Scripting.Dictionary")
            Dic.CompareMode = 1
                [COLOR="Navy"]With[/COLOR] CreateObject("System.Collections.ArrayList")
        
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] nRng
          [COLOR="Navy"]If[/COLOR] Not Dic.exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
                [COLOR="Navy"]Set[/COLOR] Dic(Dn.Value) = CreateObject("Scripting.Dictionary")
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]For[/COLOR] Ac = 1 To 4
            [COLOR="Navy"]If[/COLOR] Dn.Row = 2 [COLOR="Navy"]Then[/COLOR] .Add Dn.Offset(-Dn.Row + 1, Ac).Value
              Dic(Dn.Value).Add (Dn.Offset(-Dn.Row + 1, Ac).Value), Dn.Offset(, Ac).Value
        [COLOR="Navy"]Next[/COLOR] Ac
   [COLOR="Navy"]Next[/COLOR] Dn
   .Sort
   a = .toarray
  
   [COLOR="Navy"]Dim[/COLOR] k [COLOR="Navy"]As[/COLOR] Variant, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
   ReDim ray(1 To nRng.Rows.Count + 1, 1 To .Count + 1)
   c = 1
  [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] k [COLOR="Navy"]In[/COLOR] Dic.keys
        c = c + 1: ray(c, 1) = k
            [COLOR="Navy"]For[/COLOR] n = 0 To .Count - 1
                ray(1, a(n) + 1) = a(n)
                ray(c, a(n) + 1) = Dic(k).Item(a(n))
            [COLOR="Navy"]Next[/COLOR] n
    [COLOR="Navy"]Next[/COLOR] k
Range("P1").Resize(c, .Count + 1).Value = ray
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick