A formula that shows only the list with non-empty values

[Omer_K]

Dear all,
I have a table (I have added a table with pictorial values) with quite a bit of data, under there is another table divided by countries with INDEX and MATCH, as you can see not every country has values,
I would like to have only non-empty countries in the list. According to the example, the material Biomaterials is not found in USA and JAPAN and therefore the table below will show me only the countries Canada, Brazil and Mexico
I wish you will understand..

(I hope it can be solve by formula)

Excel 2016 (Windows) 32 bit

	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]E[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]F[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]G[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]H[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]I[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]J[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]K[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]L[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]M[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]N[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]O[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]P[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​		USA​	USA​	USA​	Canada​	Canada​	Canada​	Brazil​	Brazil​	Brazil​	Japan​	Japan​	Japan​	Mexico​	Mexico​	Mexico​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Metrial[/COLOR]	%​	Result​	No.​	%​	Result​	No.​	%​	Result​	No.​	%​	Result​	No.​	%​	Result​	No.​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​	Biomaterials​				31%​	strong​		8%​	easy​					34%​	short​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​	Ceramics​	32%​	short​	13​		easy​			long​		26%​	strong​	12​	79%​		14​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​	Composites​	59%​	long​	14​				97%​	med​		77%​	long​	21​		short​	4​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]6[/COLOR]​	Concrete​			1​		long​		80%​					5​	33%​	med​	5​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]7[/COLOR]​	Electronic / Optical​		med​	9​	18%​					22​	80%​	short​	18​		short​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]8[/COLOR]​	Glass​	43%​			75%​	med​		1%​	easy​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]9[/COLOR]​	Metals​	80%​	med​	9​	62%​		6​	59%​		19​	30%​	strong​	3​		short​	18​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]10[/COLOR]​	Metamaterials​		long​	12​	90%​	strong​	10​	8%​	short​	20​				83%​	med​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]11[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]12[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]13[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]14[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]15[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]16[/COLOR]​	<== Select Metrial From The List​			Biomaterials
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]17[/COLOR]​							%​	Result​	No.​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]18[/COLOR]​						USA​	0%​	0​	0​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]19[/COLOR]​						Canada​	31%​	strong​	0​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]20[/COLOR]​						Brazil​	8%​	easy​	0​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]21[/COLOR]​						Japan​	0%​	0​	0​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]22[/COLOR]​						Mexico​	34%​	short​	0​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]23[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]24[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]25[/COLOR]​				Cell G18: {=INDEX($B$3:$P$10,MATCH($D$16,$A$3:$A$10,0),MATCH($F18&G$17,$B$1:$P$1&$B$2:$P$2,0))}​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]26[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]27[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]28[/COLOR]​

<tbody>
</tbody>

Sheet: DataBase

<tbody>
</tbody>

Thank you so much...

Omer.

 

[Aladin Akyurek]

Do not try to post big chunks, just enough to show how your data looks like. Avoid also showing any formulas. And give the desired result(s) for the sample you posted.

 

[Omer_K]

Now it's OK?

Excel 2016 (Windows) 32 bit

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​
1​		USA​	USA​	Canada​	Canada​	Brazil​	Brazil​	Japan​	Japan​	Mexico​	Mexico​
2​	Metrial	%​	Result​	%​	Result​	%​	Result​	%​	Result​	%​	Result​
3​	Biomaterials​	​	​	31%​	strong​	8%​	easy​	​	​	34%​	short​
4​	Ceramics​	32%​	short​	​	easy​	​	long​	26%​	strong​	79%​	​
5​	Composites​	59%​	long​	​	​	97%​	med​	77%​	long​	​	short​
6​	Concrete​	​	​	​	long​	80%​	​	​	​	33%​	med​
7​	Electronic / Optical​	​	med​	18%​	​	​	​	80%​	short​	​	short​
8​	Glass​	43%​	​	75%​	med​	1%​	easy​	​	​	​	​
9​	Metals​	80%​	med​	62%​	​	59%​	​	30%​	strong​	​	short​
10​	Metamaterials​	​	long​	90%​	strong​	8%​	short​	​	​	83%​	med​
11​
12​
13​	<== Select Metrial From The List​	​	​	Biomaterials
14​							%​	Result​
15​						USA​	0%​	0​
16​						Canada​	31%​	strong​
17​						Brazil​	8%​	easy​
18​						Japan​	0%​	0​
19​						Mexico​	34%​	short​
20​
21​		Cell G15: {=INDEX($B$3:$P$10,MATCH($D$16,$A$3:$A$10,0),MATCH($F18&G$17,$B$1:$P$1&$B$2:$P$2,0))}​	​	​	​	​	​	​	​	​	​
22​		​	​	​	​	​	​	​	​	​	​
23​		​	​	​	​	​	​	​	​	​	​

Sheet: DataBase

 

[Omer_K]

The small table is what i would like...

Excel 2016 (Windows) 32 bit

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​
1​		USA​	USA​	Canada​	Canada​	Brazil​	Brazil​	Japan​	Japan​	Mexico​	Mexico​
2​	Metrial	%​	Result​	%​	Result​	%​	Result​	%​	Result​	%​	Result​
3​	Biomaterials​	​	​	31%​	strong​	​	easy​	​	​	34%​	short​
4​	Ceramics​	32%​	short​	​	easy​	​	long​	26%​	strong​	79%​	​
5​	Composites​	59%​	long​	​	​	97%​	med​	77%​	long​	​	short​
6​	Concrete​	​	​	​	long​	80%​	​	​	​	33%​	med​
7​	Electronic / Optical​	​	med​	18%​	​	​	​	80%​	short​	​	short​
8​	Glass​	43%​	​	75%​	med​	1%​	easy​	​	​	​	​
9​	Metals​	80%​	med​	62%​	​	59%​	​	30%​	strong​	​	short​
10​	Metamaterials​	​	long​	90%​	strong​	8%​	short​	​	​	83%​	med​
11​
12​
13​						Biomaterials	%​	Result​
14​						Canada​	31%​	strong​
15​						Brazil​	0%​	easy​
16​						Mexico​	34%​	short​

Sheet: DataBase

Thank you,

 

[Aladin Akyurek]

Regarding post #4 , we have an empty cell for Brazil in the Biomaterials row, but a Result value of easy. Is the latter the reason we have Brazil in our output list?

 

[Omer_K]

Hey,
That's right,
only the countries that % and result are empty (both of them) will not appear on the small table,
otherwise, such as Brazil that one of them not empty should appear on the smaill table.

 

[Aladin Akyurek]

Hey,
That's right,
only the countries that % and result are empty (both of them) will not appear on the small table,
otherwise, such as Brazil that one of them not empty should appear on the smaill table.

Thanks for the appropriate Excel readable visual, desired results, and the clarification.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1		USA	USA	Canada	Canada	Brazil	Brazil	Japan	Japan	Mexico	Mexico
2	Metrial	%	Result	%	Result	%	Result	%	Result	%	Result
3	Biomaterials			31%	strong		easy			34%	short
4	Ceramics	32%	short		easy		long	26%	strong	79%
5	Composites	59%	long			97%	med	77%	long		short
6	Concrete				long	80%				33%	med
7	Electronic / Optical		med	18%				80%	short		short
8	Glass	43%		75%	med	1%	easy
9	Metals	80%	med	62%		59%		30%	strong		short
10	Metamaterials		long	90%	strong	8%	short			83%	med
11
12
13						Biomaterials	%	Result
14					3	Canada	0.31	strong
15					5	Brazil	0	easy
16					9	Mexico	0.34	short
17
Sheet1

In E14 control+shift+enter, not just enter, and copy down:

=IFERROR(SMALL(IF(ISNUMBER(INDEX($B$3:$J$10,MATCH($F$13,$A$3:$A$10,0),0))+ISTEXT(INDEX($C$3:$K$10,MATCH($F$13,$A$3:$A$10,0),0)),COLUMN($B$1:$J$1)-COLUMN($B$1)+1),ROWS($1:1)),"")

In F14 just enter and copy down:

=IF($E14="","",INDEX($B$1:$K$1,$E14))

In G14 control+shift+enter, not just enter, copy across to H14, and down:

=IF($E14="","",INDEX($B$3:$K$10,MATCH($F$13,$A$3:$A$10,0),MATCH(1,($B$1:$K$1=$F14)*($B$2:$K$2=G$13),0)))

 

[Omer_K]

Dear Aladin,
you are the best!!

It works perfect
I really appreciate it
Thank you!

 

[Aladin Akyurek]

Dear Aladin,
you are the best!!

It works perfect
I really appreciate it
Thank you!

You are welcome. Glad to be of help.

 

[Omer_K]

Hey All,
If I would like to add for each country 1 more column,
such as:
For USA 3 column - 1 for %, one for result and one for density,
the same for Canada (3 columns) and all the countris,

what I should change in the formula?

=IFERROR(SMALL(IF(ISNUMBER(INDEX($B$3:$J$10,MATCH($F$13,$A$3:$A$10,0),0))+ISTEXT(INDEX($C$3:$K$10,MATCH($F$13,$A$3:$A$10 ,0),0)),COLUMN($B$1:$J$1)-COLUMN($B$1)+1),ROWS($1:1)),"")

thank you so much.