Joining two tables

Guinaba · Oct 9, 2020

Hi experts,

Need some help, I have two tables: List1 and List2:

List1

Brand	Item ID	Item Desc	Item Size

Star	33333	blablabla	small
Planet	44557	blablabla	medium
Moon	78786	blablabla	large

List2

Metrics
AA
AB

I have to loop through both tables adding the Metrics col from (List2) into each row of List1 creating a third table (List3)

List3

Brand	Item ID	Item Desc	Item Size	Metrics
Start	33333	blablabla	small	AA
Start	33333	blablabla	small	AB
Planet	44557	blablabla	medium	AA
Planet	44557	blablabla	medium	AB
Moon	78786	blablabla	large	AA
Moon	78786	blablabla	large	AB

I've started the following VBA code but couldn't finished off.

VBA Code:

Sub PopulateMetrics()

   Dim List1 As ListObject
   Dim List2 As ListObject
   Dim oRow As ListRow
   Dim ws1 As Worksheet
   Set ws1 = ThisWorkbook.Worksheets("WW_Portfolio")
   Dim ws2 As Worksheet
   Set ws2 = ThisWorkbook.Worksheets("WOW Metrics")
   Dim ws3 As Worksheet
   Set ws3 = ThisWorkbook.Worksheets("Template")
   Dim i As Long
   

   
   Set List1 = ws1.ListObjects("tPortfolio")
   Set List2 = ws2.ListObjects("tMetricsALL")

   For Each oRow In List1.ListRows
      ws3.Range("A" & oRow) = oRow.Range(1)
      
   Next oRow

End Sub

sandy666 · Oct 9, 2020

use

Power Query:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Metrics = Table.AddColumn(Source, "Metrics", each {"AA","AB"}),
    Expand = Table.ExpandListColumn(Metrics, "Metrics")
in
    Expand



Brand	Item ID	Item Desc	Item Size	Brand	Item ID	Item Desc	Item Size	Metrics
Star	33333	blablabla	small	Star	33333	blablabla	small	AA
Planet	44557	blablabla	medium	Star	33333	blablabla	small	AB
Moon	78786	blablabla	large	Planet	44557	blablabla	medium	AA
				Planet	44557	blablabla	medium	AB
				Moon	78786	blablabla	large	AA
				Moon	78786	blablabla	large	AB

sandy666 · Oct 9, 2020

or with single query and two tables

Power Query:

let
    Tbl2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Metrics = Table.AddColumn(Source, "Metrics", each Tbl2),
    Expand = Table.ExpandTableColumn(Metrics, "Metrics", {"Metrics"}, {"Metrics"})
in
    Expand



Brand	Item ID	Item Desc	Item Size	Brand	Item ID	Item Desc	Item Size	Metrics
Star	33333	blablabla	small	Star	33333	blablabla	small	AA
Planet	44557	blablabla	medium	Star	33333	blablabla	small	AB
Moon	78786	blablabla	large	Planet	44557	blablabla	medium	AA
				Planet	44557	blablabla	medium	AB
Metrics				Moon	78786	blablabla	large	AA
AA				Moon	78786	blablabla	large	AB
AB

Guinaba · Oct 9, 2020

Thanks @sandy666 !!! Much appreciate it!

sandy666 · Oct 9, 2020

You are welcome and thanks for the feedback

Dossfm0q · Oct 9, 2020

Greetings
An Option

Unique.xlsm

A

B

C

D

E

F

G

H

I

J

1

Brand

Item ID

Item Desc

Item Size

Brand

Item ID

Item Desc

Item Size

Metrics

2

Star

33333

blablabla

small

3

Planet

44557

blablabla

medium

4

Moon

78786

blablabla

large

5

6

Metrics

7

AA

8

AB

Sheet3

Cell Formulas
Range	Formula
F2:I7	F2	=INDEX(A$2:A$4,AGGREGATE(15,6,(ROW(A$2:A$4)-ROW(A$2)+1),CEILING(ROWS(F$2:F2)/2,1)))
J2:J7	J2	=INDEX(A$7:A$8,AGGREGATE(15,6,(ROW(A$7:A$8)-ROW(A$7)+1),MOD(ROWS(J$2:J2)+1,2)+1))

Dossfm0q · Oct 9, 2020

Hi can you complete the Highlighted with Red Color

trans.xlsx

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

1

A

B

2

Date

Name

Value

3

09-15-20

A/B

100

4

09-15-20

A

30

5

09-16-20

B / C

200

6

09-17-20

D

30

7

09-18-20

G

500

8

9/30/2020

F

7

9

10

11

12

13

14

15

Sheet1

Cell Formulas
Range	Formula
F1:J1	F1	=IFERROR(MID($B3:$B7,FIND("\|",SUBSTITUTE("/"&$B3:$B7&"/","/","\|",CEILING((COLUMN()-COLUMN($F1)+1)/2,1))),FIND("/",$B3:$B7&"/",FIND("\|",SUBSTITUTE("/"&$B3:$B7&"/","/","\|",CEILING((COLUMN()-COLUMN($F1)+1)/2,1))))-FIND("\|",SUBSTITUTE("/"&$B3:$B7&"/","/","\|",CEILING((COLUMN()-COLUMN($F1)+1)/2,1)))),"")
E2:E15	E2	=IFERROR(INDEX(Sheet1!$A$2:$A$34,AGGREGATE(15,6,MATCH(Sheet1!$A$2:$A$34,Sheet1!$A$2:$A$34,0)/(MATCH(Sheet1!$A$2:$A$34,Sheet1!$A$2:$A$34,0)=(ROW(Sheet1!$A$2:$A$34)-ROW(Sheet1!$A$2)+1)),ROWS($A$2:A2))),"")
F2:S2	F2	=IF(COUNTIF(B3:B15,"<>?*"),IF(MOD(COLUMN(F$2)-COLUMN($F$2)+1,2)=0," # ","")&CHAR(CEILING((COLUMN(F$2)-COLUMN($F$2)+1)/2,1)+64),"")
F3:F15,R3:R15,P3:P15,N3:N15,L3:L15,J3:J15,H3:H15	F3	=IFERROR(SUMPRODUCT(--($A$3:$A$34=$E3),--ISNUMBER(FIND(F$2,$B$3:$B$34)),$C$3:$C$34/(LEN($B$3:$B$34)-LEN(SUBSTITUTE($B$3:$B$34,"/",""))+1)),"")
G3:G15,S3:S15,Q3:Q15,O3:O15,M3:M15,K3:K15,I3:I15	G3	=IFERROR(COUNTIFS($A$3:$A$34,$E3,$B$3:$B$34,""&F$2&""),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

Dossfm0q · Oct 11, 2020

Hi
what About Below Your Feedback is highly appreciated

?
Note: Please for the Second Table header Press CTRL+SHIFT+ENTER to enter array formulas. First Line

VBA Code:

=TRIM(IF(AND(E2<>"",MOD(COLUMN(F$2)-COLUMN($F$2)+1,2)=0)," # ","")&IFERROR(MID(TEXTJOIN("/",,FILTERXML("<t><s>"&SUBSTITUTE(TEXTJOIN("/",TRUE,SUBSTITUTE($B$3:$B$34,"/","/")),"/","</s><s>")&"</s></t>","//s[not(preceding::*=.)]")),FIND("|",SUBSTITUTE( "/"&TEXTJOIN("/",,FILTERXML("<t><s>"&SUBSTITUTE(TEXTJOIN("/",TRUE,SUBSTITUTE($B$3:$B$34,"/","/")),"/","</s><s>")&"</s></t>","//s[not(preceding::*=.)]"))& "/", "/","|",CEILING((COLUMN()-COLUMN($F2)+1)/2,1))),FIND( "/",TEXTJOIN("/",,FILTERXML("<t><s>"&SUBSTITUTE(TEXTJOIN("/",TRUE,SUBSTITUTE($B$3:$B$34,"/","/")),"/","</s><s>")&"</s></t>","//s[not(preceding::*=.)]"))& "/",FIND("|",SUBSTITUTE( "/"&TEXTJOIN("/",,FILTERXML("<t><s>"&SUBSTITUTE(TEXTJOIN("/",TRUE,SUBSTITUTE($B$3:$B$34,"/","/")),"/","</s><s>")&"</s></t>","//s[not(preceding::*=.)]"))& "/", "/","|",CEILING((COLUMN()-COLUMN($F2)+1)/2,1))))-FIND("|",SUBSTITUTE( "/"&TEXTJOIN("/",,FILTERXML("<t><s>"&SUBSTITUTE(TEXTJOIN("/",TRUE,SUBSTITUTE($B$3:$B$34,"/","/")),"/","</s><s>")&"</s></t>","//s[not(preceding::*=.)]"))& "/", "/","|",CEILING((COLUMN()-COLUMN($F2)+1)/2,1)))),""))

join.xlsx

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

1

2

Date

Name

Value

Date

3

09-15-2020

A/B

100

4

09-15-2020

A

3

5

09-16-2020

B/C

200

6

09-17-2020

D

30

7

09-18-2020

G

500

8

09-30-2020

F

7

9

10

11

12

13

14

15

16

17

Sheet1

Cell Formulas
Range	Formula
F2:W2	F2	=TRIM(IF(AND(E2<>"",MOD(COLUMN(F$2)-COLUMN($F$2)+1,2)=0)," # ","")&IFERROR(MID(TEXTJOIN("/",,FILTERXML("<t><s>"&SUBSTITUTE(TEXTJOIN("/",TRUE,SUBSTITUTE($B$3:$B$34,"/","/")),"/","</s><s>")&"</s></t>","//s[not(preceding::=.)]")),FIND("\|",SUBSTITUTE( "/"&TEXTJOIN("/",,FILTERXML("<t><s>"&SUBSTITUTE(TEXTJOIN("/",TRUE,SUBSTITUTE($B$3:$B$34,"/","/")),"/","</s><s>")&"</s></t>","//s[not(preceding::=.)]"))& "/", "/","\|",CEILING((COLUMN()-COLUMN($F2)+1)/2,1))),FIND( "/",TEXTJOIN("/",,FILTERXML("<t><s>"&SUBSTITUTE(TEXTJOIN("/",TRUE,SUBSTITUTE($B$3:$B$34,"/","/")),"/","</s><s>")&"</s></t>","//s[not(preceding::=.)]"))& "/",FIND("\|",SUBSTITUTE( "/"&TEXTJOIN("/",,FILTERXML("<t><s>"&SUBSTITUTE(TEXTJOIN("/",TRUE,SUBSTITUTE($B$3:$B$34,"/","/")),"/","</s><s>")&"</s></t>","//s[not(preceding::=.)]"))& "/", "/","\|",CEILING((COLUMN()-COLUMN($F2)+1)/2,1))))-FIND("\|",SUBSTITUTE( "/"&TEXTJOIN("/",,FILTERXML("<t><s>"&SUBSTITUTE(TEXTJOIN("/",TRUE,SUBSTITUTE($B$3:$B$34,"/","/")),"/","</s><s>")&"</s></t>","//s[not(preceding::*=.)]"))& "/", "/","\|",CEILING((COLUMN()-COLUMN($F2)+1)/2,1)))),""))
E3:E17	E3	=IFERROR(TEXT(INDEX($A$3:$A$34,AGGREGATE(15,6,MATCH($A$3:$A$34,$A$3:$A$34,0)/(MATCH($A$3:$A$34,$A$3:$A$34,0)=(ROW($A$3:$A$34)-ROW($A$3)+1)),ROWS($A$3:A3))),"mm-dd-yyyy"),"")
F3:F17,X3:X16,V3:V17,T3:T17,R3:R17,P3:P17,N3:N17,L3:L17,J3:J17,H3:H17	F3	=IFERROR(IF(F$2<>"",SUMPRODUCT(--($A$3:$A$34=VALUE($E3)),--ISNUMBER(FIND(F$2,$B$3:$B$34)),$C$3:$C$34/(LEN($B$3:$B$34)-LEN(SUBSTITUTE($B$3:$B$34,"/",""))+1)),""),"")
G3:G17,W3:W17,U3:U17,S3:S17,Q3:Q17,O3:O17,M3:M17,K3:K17,I3:I17	G3	=IFERROR(IF(AND(G$2<>"",$E3<>""),COUNTIFS($A$3:$A$34,VALUE($E3),$B$3:$B$34,""&F$2&""),""),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

Joining two tables

Guinaba

Board Regular

Excel Facts

sandy666

Banned - Rules violations

sandy666

Banned - Rules violations

Guinaba

Board Regular

sandy666

Banned - Rules violations

Dossfm0q

Banned User

Dossfm0q

Banned User

Dossfm0q

Banned User

Similar threads

Forum statistics

Share this page

Joining two tables

Board Regular

Excel Facts

Banned - Rules violations

Banned - Rules violations

Board Regular

Banned - Rules violations

Banned User

Banned User

Banned User

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock