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

Omer_K · Dec 4, 2018

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]

Sheet: DataBase

Thank you so much...

Omer.

Omer_K · Jan 15, 2019

Aladin Akyurek said:
Thanks for the appropriate Excel readable visual, desired results, and the clarification.

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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
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)))

Hey,
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.

Aladin Akyurek · Jan 15, 2019

It's a better idea is to post a sample along with the desired results for that sample.

Omer_K · Jan 17, 2019

Hey,

Excel 2016 (Windows) 32 bit

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
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					0.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		80%
8	Electronic / Optical		med	2.8	18%						80%	short
9	Glass	43%			75%	med		1%	easy
10	Metals	80%	med		62%			59%			30%	strong
11	Metamaterials		long		90%	strong		8%	short
12
13
14						Biomaterials	%	Result	density
15						Canada	31%	strong	5.6
16						Brazil		easy
17						Mexico	0.31	strong	2.7

Sheet: âéìéåï1

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

The table 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),

I hope I was clear,

Thank you

Omer_K · Jan 25, 2019

Aladin Akyurek said:
It's a better idea is to post a sample along with the desired results for that sample.

Dear Aladin,
I hope now it's more clearly:

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 Validation List ==>		Biomaterials	%	Result	density
15						Canada	31%	strong	5.6
16						Brazil		easy
17						Mexico	0.31	strong	2.7

Sheet: Data Base

I would like to show 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 so much,

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

Omer_K

Board Regular

Omer_K

Board Regular

Excel Facts

Aladin Akyurek

MrExcel MVP

Omer_K

Board Regular

Omer_K

Board Regular

Similar threads

Forum statistics

Share this page

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

Omer_K

Board Regular

Omer_K

Board Regular

Excel Facts

Aladin Akyurek

MrExcel MVP

Omer_K

Board Regular

Omer_K

Board Regular

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