Merge Two Tables and Convert to Data List

G

Gimics

Board Regular
Joined
Jan 29, 2014
Messages
164
Office Version
  1. 365
Platform
  1. Windows
Hello,

I have two tables that are maintained separately, but share common elements. Based on those common elements, I would like to merge the tables and extend them into a full data list.

The first table has headings across the top (let's call them location groups) that need to be looked up in the second table, which will contain multiple records (let's called them locations) of those headings associated with additional fields. After looking the value up, I want to create a third table that's a list of all of the source data and looked up values. Much easier to explain with visuals:

Table 1(source data):
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Fruit Types[/TD]
[TD="align: center"]Stores[/TD]
[TD="align: center"]Ecommerce[/TD]
[TD="align: center"]Corporate[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Apple[/TD]
[TD]Gala[/TD]
[TD]Gala[/TD]
[TD]Fuji[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Orange[/TD]
[TD]Tangerine[/TD]
[TD]Mandarin[/TD]
[TD]Madarin[/TD]
[/TR]
</tbody>[/TABLE]


Table 2(mapping table):
[TABLE="class: grid, width: 400"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Location Groups[/TD]
[TD]Locations[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Stores[/TD]
[TD]Store 100[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Stores[/TD]
[TD]Store 200[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Stores[/TD]
[TD]Store 300[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Ecommerce[/TD]
[TD]Canada Website[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Ecommerce[/TD]
[TD]USA Website[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Ecommerce[/TD]
[TD]Global Website[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Corporate[/TD]
[TD]Head Office[/TD]
[/TR]
</tbody>[/TABLE]


Table 3(output; for each fruit type and fruit variety, lookup location group and create a record for each location):
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Fruit Types[/TD]
[TD="align: center"]Fruit Variety[/TD]
[TD="align: center"]Location Group[/TD]
[TD="align: center"]Location[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Apple[/TD]
[TD]Gala[/TD]
[TD]Stores[/TD]
[TD]Store 100[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Apple[/TD]
[TD]Gala[/TD]
[TD]Stores[/TD]
[TD]Store 200[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Apple[/TD]
[TD]Gala[/TD]
[TD]Stores[/TD]
[TD]Store 300[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Apple[/TD]
[TD]Gala[/TD]
[TD]Ecommerce[/TD]
[TD]Canada Website[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Apple[/TD]
[TD]Gala[/TD]
[TD]Ecommerce[/TD]
[TD]USA Website[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Apple[/TD]
[TD]Gala[/TD]
[TD]Ecommerce[/TD]
[TD]Global Website[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Apple[/TD]
[TD]Fuji[/TD]
[TD]Corporate[/TD]
[TD]Head Office[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Orange[/TD]
[TD]Tangerine[/TD]
[TD]Stores[/TD]
[TD]Store 100[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Orange[/TD]
[TD]Tangerine[/TD]
[TD]Stores[/TD]
[TD]Store 200[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Orange[/TD]
[TD]Tangerine[/TD]
[TD]Stores[/TD]
[TD]Store 300[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Orange[/TD]
[TD]Mandarin[/TD]
[TD]Ecommerce[/TD]
[TD]Canada Website[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Orange[/TD]
[TD]Mandarin[/TD]
[TD]Ecommerce[/TD]
[TD]USA Website[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]Orange[/TD]
[TD]Mandarin[/TD]
[TD]Ecommerce[/TD]
[TD]Global Website[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]Orange[/TD]
[TD]Mandarin[/TD]
[TD]Corporate[/TD]
[TD]Head Office[/TD]
[/TR]
</tbody>[/TABLE]

I have many more columns and rows of data, but this basic layout would solve my problem. I know how to look through the source table and then how to update the output table with those values, but I don't know how to find multiple values in the mapping table and return multiple values.

Say I've declared variables for the source table (Table1) and mapping table (Table2), including their rows, columns and data, and selected an output range for Table3 as a single cell (outRng); this would roughly be where I'm at (the code below isn't complete for all of the headings, but I can figure that part out...this is just for the mapping lookup):

Code: 
For i = 1 to Table1.rows.count
    For j = 1 To Table1.columns.count
        If Not Table1.body(i, j) = "" Then
            Set foundRng = Table2Groups.Find(Table1Heading(,j), LookIn:=xlValues)
            outRng.Offset(k - 1).Resize(foundRng.Rows.Count).Value = foundRng.Offset(, 1).Value
            k = k + foundRng.Rows.Count
        Else
        End If
    Next j
Next i

This, obviously, only returns one value, as range.find only returns the range of the first found cell. This would be my resulting table:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="align: center"]Fruit Types[/TD]
[TD="align: center"]Fruit Variety[/TD]
[TD="align: center"]Location Group[/TD]
[TD="align: center"]Location[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Apple[/TD]
[TD]Gala[/TD]
[TD]Stores[/TD]
[TD]Store 100[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Apple[/TD]
[TD]Gala[/TD]
[TD]Ecommerce[/TD]
[TD]Canada Website[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Apple[/TD]
[TD]Fuji[/TD]
[TD]Corporate[/TD]
[TD]Head Office[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Orange[/TD]
[TD]Tangerine[/TD]
[TD]Stores[/TD]
[TD]Store 100[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Orange[/TD]
[TD]Mandarin[/TD]
[TD]Ecommerce[/TD]
[TD]Canada Website[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Orange[/TD]
[TD]Mandarin[/TD]
[TD]Corporate[/TD]
[TD]Head Office[/TD]
[/TR]
</tbody>[/TABLE]


What am I missing? It would be great if I could use a range.resize(foundrange.size) = foundrange.offset(1) kind of formula here, but maybe I can only do this with multiple loops?

Thanks in advance!
 
Last edited:

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Sort by date Sort by votes
The tables you created as a range table or only a range in different sheets.
Can it be with VBA or does it have to be with formulas?
 
Upvote 0
Thanks for the responses, but unfortunately I'm not really clear what either of you are asking for.

All of the tables exist in different sheets, and they are not real "tables" or named ranges; they are just data in those sheets. For this example, assume that I've declared range variables and named them in the vba as Table1 and Table2.
 
Upvote 0
So if you have 3 tables, try this macro

Change data in red by your information

Code: 
Sub Convert_Data_List()
    Dim lo1 As ListObject, lo2 As ListObject, lo3 As ListObject
    Dim elem As Range, b As Range, sh As Worksheet
    Dim j As Long, cell As String, n As Long
    
    Application.ScreenUpdating = False
    Set sh = Sheets("[COLOR=#ff0000]Data[/COLOR]")
    Set lo1 = sh.ListObjects("[COLOR=#ff0000]Source[/COLOR]")
    Set lo2 = sh.ListObjects("[COLOR=#ff0000]mapping[/COLOR]")
    Set lo3 = sh.ListObjects("[COLOR=#ff0000]output[/COLOR]")
    
    With lo3.DataBodyRange
        If .Rows.Count > 1 Then .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Rows.Delete
        .Rows(1).ClearContents
    End With
    
    For Each elem In lo1.ListColumns(1).DataBodyRange
        For j = 2 To lo1.ListColumns.Count
            Set b = lo2.Range.Find(lo1.HeaderRowRange(, j), LookAt:=xlWhole)
            If Not b Is Nothing Then
                cell = b.Address
                Do
                    n = lo3.DataBodyRange.Rows.Count
                    lo3.DataBodyRange(n, 1).Resize(1, 4).Value = Array(elem, elem.Offset(, 1), b, b.Offset(, 1))
                    lo3.ListRows.Add AlwaysInsert:=True
                    Set b = lo2.Range.FindNext(b)
                Loop While Not b Is Nothing And b.Address <> cell
            End If
        Next
    Next
End Sub

Result

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:115.96px;" /><col style="width:101.7px;" /><col style="width:89.35px;" /><col style="width:79.84px;" /><col style="width:27.56px;" /><col style="width:85.54px;" /><col style="width:94.1px;" /><col style="width:110.26px;" /><col style="width:101.7px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#4f81bd; color:#ffffff; font-weight:bold; ">Fruit Types</td><td style="background-color:#4f81bd; color:#ffffff; font-weight:bold; ">Stores</td><td style="background-color:#4f81bd; color:#ffffff; font-weight:bold; ">Ecommerce</td><td style="background-color:#4f81bd; color:#ffffff; font-weight:bold; ">Corporate</td><td > </td><td style="background-color:#4f81bd; color:#ffffff; font-weight:bold; ">Fruit Types</td><td style="background-color:#4f81bd; color:#ffffff; font-weight:bold; ">Fruit Variety</td><td style="background-color:#4f81bd; color:#ffffff; font-weight:bold; ">Location Group</td><td style="background-color:#4f81bd; color:#ffffff; font-weight:bold; ">Location</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="background-color:#b8cce4; ">Apple</td><td style="background-color:#b8cce4; ">Gala</td><td style="background-color:#b8cce4; ">Gala</td><td style="background-color:#b8cce4; ">Fuji</td><td > </td><td style="background-color:#b8cce4; ">Apple</td><td style="background-color:#b8cce4; ">Gala</td><td style="background-color:#b8cce4; ">Stores</td><td style="background-color:#b8cce4; ">Store 100</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="background-color:#dbe5f1; ">Orange</td><td style="background-color:#dbe5f1; ">Tangerine</td><td style="background-color:#dbe5f1; ">Mandarin</td><td style="background-color:#dbe5f1; ">Mandarin</td><td > </td><td style="background-color:#dbe5f1; ">Apple</td><td style="background-color:#dbe5f1; ">Gala</td><td style="background-color:#dbe5f1; ">Stores</td><td style="background-color:#dbe5f1; ">Store 200</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="background-color:#b8cce4; ">Apple</td><td style="background-color:#b8cce4; ">Gala</td><td style="background-color:#b8cce4; ">Stores</td><td style="background-color:#b8cce4; ">Store 300</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="background-color:#dbe5f1; ">Apple</td><td style="background-color:#dbe5f1; ">Gala</td><td style="background-color:#dbe5f1; ">Ecommerce</td><td style="background-color:#dbe5f1; ">Canada Website</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="background-color:#4f81bd; color:#ffffff; font-weight:bold; ">Location Groups</td><td style="background-color:#4f81bd; color:#ffffff; font-weight:bold; ">Locations</td><td > </td><td > </td><td > </td><td style="background-color:#b8cce4; ">Apple</td><td style="background-color:#b8cce4; ">Gala</td><td style="background-color:#b8cce4; ">Ecommerce</td><td style="background-color:#b8cce4; ">USA Website</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="background-color:#b8cce4; ">Stores</td><td style="background-color:#b8cce4; ">Store 100</td><td > </td><td > </td><td > </td><td style="background-color:#dbe5f1; ">Apple</td><td style="background-color:#dbe5f1; ">Gala</td><td style="background-color:#dbe5f1; ">Ecommerce</td><td style="background-color:#dbe5f1; ">Global Website</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="background-color:#dbe5f1; ">Stores</td><td style="background-color:#dbe5f1; ">Store 200</td><td > </td><td > </td><td > </td><td style="background-color:#b8cce4; ">Apple</td><td style="background-color:#b8cce4; ">Gala</td><td style="background-color:#b8cce4; ">Corporate</td><td style="background-color:#b8cce4; ">Head Office</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="background-color:#b8cce4; ">Stores</td><td style="background-color:#b8cce4; ">Store 300</td><td > </td><td > </td><td > </td><td style="background-color:#dbe5f1; ">Orange</td><td style="background-color:#dbe5f1; ">Tangerine</td><td style="background-color:#dbe5f1; ">Stores</td><td style="background-color:#dbe5f1; ">Store 100</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="background-color:#dbe5f1; ">Ecommerce</td><td style="background-color:#dbe5f1; ">Canada Website</td><td > </td><td > </td><td > </td><td style="background-color:#b8cce4; ">Orange</td><td style="background-color:#b8cce4; ">Tangerine</td><td style="background-color:#b8cce4; ">Stores</td><td style="background-color:#b8cce4; ">Store 200</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="background-color:#b8cce4; ">Ecommerce</td><td style="background-color:#b8cce4; ">USA Website</td><td > </td><td > </td><td > </td><td style="background-color:#dbe5f1; ">Orange</td><td style="background-color:#dbe5f1; ">Tangerine</td><td style="background-color:#dbe5f1; ">Stores</td><td style="background-color:#dbe5f1; ">Store 300</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="background-color:#dbe5f1; ">Ecommerce</td><td style="background-color:#dbe5f1; ">Global Website</td><td > </td><td > </td><td > </td><td style="background-color:#b8cce4; ">Orange</td><td style="background-color:#b8cce4; ">Tangerine</td><td style="background-color:#b8cce4; ">Ecommerce</td><td style="background-color:#b8cce4; ">Canada Website</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="background-color:#b8cce4; ">Corporate</td><td style="background-color:#b8cce4; ">Head Office</td><td > </td><td > </td><td > </td><td style="background-color:#dbe5f1; ">Orange</td><td style="background-color:#dbe5f1; ">Tangerine</td><td style="background-color:#dbe5f1; ">Ecommerce</td><td style="background-color:#dbe5f1; ">USA Website</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="background-color:#b8cce4; ">Orange</td><td style="background-color:#b8cce4; ">Tangerine</td><td style="background-color:#b8cce4; ">Ecommerce</td><td style="background-color:#b8cce4; ">Global Website</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="background-color:#dbe5f1; ">Orange</td><td style="background-color:#dbe5f1; ">Tangerine</td><td style="background-color:#dbe5f1; ">Corporate</td><td style="background-color:#dbe5f1; ">Head Office</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="background-color:#b8cce4; "> </td><td style="background-color:#b8cce4; "> </td><td style="background-color:#b8cce4; "> </td><td style="background-color:#b8cce4; "> </td></tr></table>
 
Upvote 0
Gimics said:
Thanks for the responses, but unfortunately I'm not really clear what either of you are asking for.

All of the tables exist in different sheets, and they are not real "tables" or named ranges; they are just data in those sheets. For this example, assume that I've declared range variables and named them in the vba as Table1 and Table2.
Click to expand...


Too late, I finished the macro as if you had the information in Tables.
You mentioned in your orignal requrimiento:
I have two tables
Click to expand...

Well, table and sheet are different and programming is also different.

Can you help by explaining exactly how you have the data in each sheet, that is, in which row and column each data is found?
 
Upvote 0
Another way.

Code: 
Sub Combine()
Dim Tbl1() As Variant: Tbl1 = Range("A1:D3").Value 'change range for where your table is
Dim Tbl2() As Variant: Tbl2 = Range("H2:I8").Value 'change range for where your table is
Dim AL As Object: Set AL = CreateObject("System.Collections.ArrayList")
Dim Fruit As String
Dim fType As String
Dim cHead As String


For i = 2 To UBound(Tbl1)
    Fruit = Tbl1(i, 1)
    For j = 2 To UBound(Tbl1, 2)
        fType = Tbl1(i, j)
        cHead = Tbl1(1, j)
        For k = LBound(Tbl2) To UBound(Tbl2)
            If Tbl2(k, 1) = cHead Then
                AL.Add xJoin(",", Fruit, fType, cHead, Tbl2(k, 2))
            End If
        Next k
    Next j
Next i


With Range("R1").Resize(AL.Count) 'Change this range for where you want your results to show up
    .Value = Application.Transpose(AL.toArray)
    .TextToColumns DataType:=xlDelimited, Comma:=True
End With


End Sub


Function xJoin(del As String, ParamArray arg() As Variant) As String
xJoin = Join(arg, del)
End Function
 
Last edited:
Upvote 0
Same thing but adjusted to use tables instead of loading ranges to arrays.

Code: 
Sub Combo2()
Dim T1 As ListObject: Set T1 = Sheets("Sheet1").ListObjects("Table1")
Dim T2 As ListObject: Set T2 = Sheets("Sheet1").ListObjects("Table2")
Dim AL As Object: Set AL = CreateObject("System.Collections.ArrayList")
Dim Fruit As String, fType As String, cHead As String


AL.Add xJoin(",", "Fruit Types", "Fruit Variety", "Location Group", "Location")


For i = 1 To T1.DataBodyRange.Rows.Count
    Fruit = T1.DataBodyRange.Cells(i, 1)
    For j = 2 To T1.DataBodyRange.Columns.Count
        cHead = T1.HeaderRowRange.Cells(1, j)
        fType = T1.DataBodyRange(i, j)
        For k = 1 To T2.DataBodyRange.Rows.Count
            If T2.DataBodyRange.Cells(k, 1) = cHead Then
                AL.Add xJoin(",", Fruit, fType, cHead, T2.DataBodyRange.Cells(k, 2))
            End If
        Next k
    Next j
Next i


With Range("R1").Resize(AL.Count) 'Change this range for where you want your results to show up
    .Value = Application.Transpose(AL.toArray)
    .TextToColumns DataType:=xlDelimited, Comma:=True
End With


End Sub


Function xJoin(del As String, ParamArray arg() As Variant) As String
xJoin = Join(arg, del)
End Function
 
Upvote 0
enough PowerQuery directly from Excel

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Fruit Types[/td][td=bgcolor:#5B9BD5]Stores[/td][td=bgcolor:#5B9BD5]Ecommerce[/td][td=bgcolor:#5B9BD5]Corporate[/td][td][/td][td=bgcolor:#5B9BD5]Location Groups[/td][td=bgcolor:#5B9BD5]Locations[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Apple[/td][td=bgcolor:#DDEBF7]Gala[/td][td=bgcolor:#DDEBF7]Gala[/td][td=bgcolor:#DDEBF7]Fuji[/td][td][/td][td=bgcolor:#DDEBF7]Stores[/td][td=bgcolor:#DDEBF7]Store 100[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Orange[/td][td]Tangerine[/td][td]Mandarin[/td][td]Mandarin[/td][td][/td][td]Stores[/td][td]Store 200[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#DDEBF7]Stores[/td][td=bgcolor:#DDEBF7]Store 300[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td]Ecommerce[/td][td]Canada Website[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#DDEBF7]Ecommerce[/td][td=bgcolor:#DDEBF7]USA Website[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td]Ecommerce[/td][td]Global Website[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#DDEBF7]Corporate[/td][td=bgcolor:#DDEBF7]Head Office[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Fruit Types[/td][td=bgcolor:#70AD47]Fruit variety[/td][td=bgcolor:#70AD47]Location Groups[/td][td=bgcolor:#70AD47]Locations[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Apple[/td][td=bgcolor:#E2EFDA]Fuji[/td][td=bgcolor:#E2EFDA]Corporate[/td][td=bgcolor:#E2EFDA]Head Office[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Apple[/td][td]Gala[/td][td]Ecommerce[/td][td]Canada Website[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Apple[/td][td=bgcolor:#E2EFDA]Gala[/td][td=bgcolor:#E2EFDA]Ecommerce[/td][td=bgcolor:#E2EFDA]Global Website[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Apple[/td][td]Gala[/td][td]Ecommerce[/td][td]USA Website[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Apple[/td][td=bgcolor:#E2EFDA]Gala[/td][td=bgcolor:#E2EFDA]Stores[/td][td=bgcolor:#E2EFDA]Store 100[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Apple[/td][td]Gala[/td][td]Stores[/td][td]Store 200[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Apple[/td][td=bgcolor:#E2EFDA]Gala[/td][td=bgcolor:#E2EFDA]Stores[/td][td=bgcolor:#E2EFDA]Store 300[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Orange[/td][td]Mandarin[/td][td]Corporate[/td][td]Head Office[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Orange[/td][td=bgcolor:#E2EFDA]Mandarin[/td][td=bgcolor:#E2EFDA]Ecommerce[/td][td=bgcolor:#E2EFDA]Canada Website[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Orange[/td][td]Mandarin[/td][td]Ecommerce[/td][td]Global Website[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Orange[/td][td=bgcolor:#E2EFDA]Mandarin[/td][td=bgcolor:#E2EFDA]Ecommerce[/td][td=bgcolor:#E2EFDA]USA Website[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Orange[/td][td]Tangerine[/td][td]Stores[/td][td]Store 100[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Orange[/td][td=bgcolor:#E2EFDA]Tangerine[/td][td=bgcolor:#E2EFDA]Stores[/td][td=bgcolor:#E2EFDA]Store 200[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Orange[/td][td]Tangerine[/td][td]Stores[/td][td]Store 300[/td][td][/td][td][/td][td][/td][/tr]
[/table]

unpivot table5 (first blue from the left)

Code: 
[SIZE=1]// Table5
let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    UnPivot = Table.UnpivotOtherColumns(Source, {"Fruit Types"}, "Attribute", "Value")
in
    UnPivot[/SIZE]

then merge unpivoted table5 and table 6 (first blue from the right)

Code: 
[SIZE=1]// Merge1
let
    Source = Table.NestedJoin(Table5,{"Attribute"},Table6,{"Location Groups"},"Table6",JoinKind.FullOuter),
    Expand = Table.ExpandTableColumn(Source, "Table6", {"Location Groups", "Locations"}, {"Location Groups", "Locations"}),
    RC = Table.RemoveColumns(Expand,{"Attribute"}),
    Rename = Table.RenameColumns(RC,{{"Value", "Fruit variety"}}),
    Sort = Table.Sort(Rename,{{"Fruit Types", Order.Ascending}, {"Fruit variety", Order.Ascending}, {"Location Groups", Order.Ascending}, {"Locations", Order.Ascending}})
in
    Sort[/SIZE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
Auto-Populate data in a table using a drop-down list selection from other sheet tables
Replies
0
Views
996
rdoulaghsingh
R
M
  • Question Question
Consolidating data with same headers from multiple worksheets into master worksheet
2
Replies
14
Views
1K
oldbrewer
O
S
  • Question Question
Trying to align/merge two data sets/tables
Replies
5
Views
659
mart37
M
G
  • Question Question
Lookup with Exclusion List formula
Replies
5
Views
754
Aladin Akyurek
Aladin Akyurek
D
  • Question Question
scraping website using vba excel please help
Replies
0
Views
871
dinesh1master
D

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top