Combine 2 tables into one table

[Chewyhairball]

Hi Guys

I am wondering what the best way to combine 2 table of data into one table not using a pivot table or VBA

Here is my example below.

thanks

Rory

 

[lrobbo314]

How about using Power Query.

xhtml.html.xlsm
	C	D	E	F	G	H	I	J	K	L	M	N	O
3	Item	Use by Date	Stock			Item	Use by Date	Stock			Item	Use by Date	Stock
4	Banana	3/2/2021	22			Orange	2/4/2021	12			Banana	3/2/2021 0:00	22
5	Apple	3/4/2021	3			Apple	4/3/2021	6			Apple	3/4/2021 0:00	3
6	Pear	5/2/2021	12			Pear	6/5/2021	8			Pear	5/2/2021 0:00	12
7	Apple	3/5/2021	3			Strawberry	3/1/2021	32			Apple	3/5/2021 0:00	3
8	Mango	5/4/2021	41			Lemon	2/2/2021	21			Mango	5/4/2021 0:00	41
9											Orange	2/4/2021 0:00	12
10											Apple	4/3/2021 0:00	6
11											Pear	6/5/2021 0:00	8
12											Strawberry	3/1/2021 0:00	32
13											Lemon	2/2/2021 0:00	21
Sheet6

let
    Source = Table.Combine({Table4, Table6})
in
    Source

 

[Chewyhairball]

How about using Power Query.

xhtml.html.xlsm
	C	D	E	F	G	H	I	J	K	L	M	N	O
3	Item	Use by Date	Stock			Item	Use by Date	Stock			Item	Use by Date	Stock
4	Banana	3/2/2021	22			Orange	2/4/2021	12			Banana	3/2/2021 0:00	22
5	Apple	3/4/2021	3			Apple	4/3/2021	6			Apple	3/4/2021 0:00	3
6	Pear	5/2/2021	12			Pear	6/5/2021	8			Pear	5/2/2021 0:00	12
7	Apple	3/5/2021	3			Strawberry	3/1/2021	32			Apple	3/5/2021 0:00	3
8	Mango	5/4/2021	41			Lemon	2/2/2021	21			Mango	5/4/2021 0:00	41
9											Orange	2/4/2021 0:00	12
10											Apple	4/3/2021 0:00	6
11											Pear	6/5/2021 0:00	8
12											Strawberry	3/1/2021 0:00	32
13											Lemon	2/2/2021 0:00	21
Sheet6

let
    Source = Table.Combine({Table4, Table6})
in
    Source

Hi. I have never used power query and dont think it is even on here. It is a works PC so we cant load anything new on.

 

[lrobbo314]

K, how about this?

xhtml.html.xlsm
	C	D	E	F	G	H	I	J	K	L	M	N
3	Item	Use by Date	Stock			Item	Use by Date	Stock		Item	Use by Date	Stock
4	Banana	3/2/2021	22			Orange	2/4/2021	12		Banana	3/2/2021	22
5	Apple	3/4/2021	3			Apple	4/3/2021	6		Apple	3/4/2021	3
6	Pear	5/2/2021	12			Pear	6/5/2021	8		Pear	5/2/2021	12
7	Apple	3/5/2021	3			Strawberry	3/1/2021	32		Apple	3/5/2021	3
8	Mango	5/4/2021	41			Lemon	2/2/2021	21		Mango	5/4/2021	41
9										Orange	2/4/2021	12
10										Apple	4/3/2021	6
11										Pear	6/5/2021	8
12										Strawberry	3/1/2021	32
13										Lemon	2/2/2021	21
Sheet6

Cell Formulas
Range	Formula
L4:N13	L4	=LET(tbl,FILTERXML("<a><b>" & SUBSTITUTE(TEXTJOIN(",",1,TEXTJOIN(",",1,C4:E8),TEXTJOIN(",",1,H4:J8)),",","</b><b>") & "</b></a>","//b"),INDEX(tbl,SEQUENCE(ROWS(tbl)/3,3)))
Dynamic array formulas.

 

[Chewyhairball]

K, how about this?

xhtml.html.xlsm
	C	D	E	F	G	H	I	J	K	L	M	N
3	Item	Use by Date	Stock			Item	Use by Date	Stock		Item	Use by Date	Stock
4	Banana	3/2/2021	22			Orange	2/4/2021	12		Banana	3/2/2021	22
5	Apple	3/4/2021	3			Apple	4/3/2021	6		Apple	3/4/2021	3
6	Pear	5/2/2021	12			Pear	6/5/2021	8		Pear	5/2/2021	12
7	Apple	3/5/2021	3			Strawberry	3/1/2021	32		Apple	3/5/2021	3
8	Mango	5/4/2021	41			Lemon	2/2/2021	21		Mango	5/4/2021	41
9										Orange	2/4/2021	12
10										Apple	4/3/2021	6
11										Pear	6/5/2021	8
12										Strawberry	3/1/2021	32
13										Lemon	2/2/2021	21
Sheet6

Cell Formulas
Range	Formula
L4:N13	L4	=LET(tbl,FILTERXML("<a><b>" & SUBSTITUTE(TEXTJOIN(",",1,TEXTJOIN(",",1,C4:E8),TEXTJOIN(",",1,H4:J8)),",","</b><b>") & "</b></a>","//b"),INDEX(tbl,SEQUENCE(ROWS(tbl)/3,3)))
Dynamic array formulas.

That’s pretty cool. Does the trick!
Thank you very much.