Create a new table without empty data rows

[Omer_K]

Hey all,
I asked a few weeks ago something like that,
but now I would like to upgraed my question.

I have a big data base (In range A2:P12),

I would like to create the table in range F14:I17,
The Cell F14 is a data validation list with all metrials.

The table in range F14:I17 show only the countriues with their data that don't empty (if one of the options: %, result or density not empty- the country will apper in the small table with the data),

Thank you,

Excel 2016 (Windows) 32 bit

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​	L​	M​	N​	O​	P​
1​
2​		USA​	USA​	USA​	Canada​	Canada​	Canada​	Brazil​	Brazil​	Brazil​	Japan​	Japan​	Japan​	Mexico​	Mexico​	Mexico​
3​	Metrial	%​	Result​	density​	%​	Result​	density​	%​	Result​	density​	%​	Result​	density​	%​	Result​	density​
4​	Biomaterials​				31%​	strong​	5.6​		easy​	​			​	31%​	strong​	2.7​
5​	Ceramics​	32%​	short​	1.5​		easy​			long​	​	26%​	strong​	​
6​	Composites​	59%​	long​	​			​	97%​	med​	​	77%​	long​		​
7​	Concrete​					long​	2.8​	80%​					0.058​	​
8​	Electronic / Optical​		med​	2.8​	18%​					2.15​	80%​	short​		​
9​	Glass​	43%​			75%​	med​	​	1%​	easy​	​
10​	Metals​	80%​	med​	​	62%​		​	59%​		4.78​	30%​	strong​		​
11​	Metamaterials​		long​	​	90%​	strong​	​	8%​	short​	​			​	​
12​
13​
14​				Cell F14 is a Data Vakudation List ==>​		Biomaterials​	%​	Result​	density​
15​						Canada​	31%​	strong​	5.6​
16​						Brazil​	​	easy​	​
17​						Mexico​	0.31​	strong​	2.7​

Sheet: Data Base

 

[Omer_K]

Any ideas?

 

[sandy666]

are you able to use PowerQuery with/without PivotTable after modification a bit your original table?

 

[Omer_K]

I would like to do it only with formulas and without Pivot table or power query...

 

[sandy666]

ok, no problem

Have a nice day