Add data with blank cells to new table + Data validation without blanks

[Johnhtvu]

Hi guys!

I ran into a problem which I can't seem to solve.

I am on Excel for Mac 2019

Problem:

1. Table 1 is new data. This data contains blank cells in between. I want it to add to table 2, without the blanks. Not directly under it but under table 2 [cell E26]
2. The new table still have blank data in between. this is how I need it. However, I want to create a data validation without blanks from this table

What formulas can help solve this?

Help is much appreciated!!

Add to list & DV problem.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2		table 1				table 2				table 3
3		New data				Existing List				Desired Results			Data Validation
4
5	Name	Position	Country		Name	Position	Country		Name	Position	Country		Name	Position	Country
6	John	CEO	Vietnam		Peter	Supervisor	Vietnam		Peter	Supervisor	Vietnam		Willem	Supervisor	Thailand
7	Emely	COO	Hong Kong		Piet	Supervisor	Hong Kong		Piet	Supervisor	Hong Kong
8					Pan	Manager	Australia		Pan	Manager	Australia
9	Peter	CFO	China		Poca	Director	Hong kong		Poca	Director	Hong kong
10					John	Assistant	Netherlands		John	Assistant	Netherlands
11					Donald	Housekeeping	Germany		Donald	Housekeeping	Germany
12	Cindy	CTO	Netherlands		Willem	Supervisor	Thailand		Willem	Supervisor	Thailand
13	Jessica	Director	Germany		Jessica	Supervisor	Australia		Jessica	Supervisor	Australia
14	William	Manager	Germany		Thijs	Manager	Mongolia		Thijs	Manager	Mongolia
15					TK	Director	Hong kong		TK	Director	Hong kong
16					Nina	Assistant	Netherlands		Nina	Assistant	Netherlands
17					Lisa	Housekeeping	Germany		Lisa	Housekeeping	Germany
18	Rosa	Supervisor	England		Martino	Supervisor	Thailand		Martino	Supervisor	Thailand
19					Klaas	Supervisor	Australia		Klaas	Supervisor	Australia
20	Kai	Administration	Australia		Ben	Manager	Mongolia		Ben	Manager	Mongolia
21
22
23
24
25
26									John	CEO	Vietnam
27									Emely	COO	Hong Kong
28									Peter	CFO	China
29									Cindy	CTO	Netherlands
30									Jessica	Director	Germany
31									William	Manager	Germany
32									Rosa	Supervisor	England
33									Kai	Administration	Australia
34
35
Sheet1

Cell Formulas
Range	Formula
N6	N6	=INDEX(desiredresults[Position],MATCH(M6,desiredresults[Name],0))
O6	O6	=INDEX(desiredresults[Country],MATCH(M6,desiredresults[Name],0))

Cells with Data Validation
Cell	Allow	Criteria
M6	List	=$I$6:$I$33