Display a list only with the values by country

[Omer_K]

Hello everyone,
I asked a question in the past, but there are some changes here:
The table above has data for each country - as you can see - there are a number of missing data.
Cell D16 has a Data Validation list of all countries
I would like to know that if I select a particular country from the list, I will only see the full data,
For example, if I choose the state of Florida, I will only see a list of complete data in the table below.
But anyway (even if it's empty) I want to see the three columns of:
Population, Persons per household, and Time zone (s)


Hope i was clear,


Thank you

Omer


Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][th]

H​

[/th][th]

I​

[/th][th]

J​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]

Country​

[/td][td]Area[/td][td]Building permits[/td][td]Capital[/td][td]Country/region[/td][td]Abbreviation[/td][td]Name[/td][td]Population[/td][td]Persons per household[/td][td]Time zone(s)[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]Alabama[/td][td]

135,765​

[/td][td][/td][td]Montgomery[/td][td][/td][td]US-AL[/td][td]Alabama[/td][td]

4,874,747​

[/td][td]

2.55​

[/td][td]Central Time Zone[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]California[/td][td][/td][td]

102,350​

[/td][td][/td][td][/td][td]US-CA[/td][td]California[/td][td]

39,536,653​

[/td][td][/td][td]Pacific Time Zone[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]Florida[/td][td]

170,304​

[/td][td][/td][td]Tallahassee[/td][td]United States[/td][td][/td][td]Florida[/td][td]

21,670,000​

[/td][td]

2.63​

[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]Hawaii[/td][td]

28,311​

[/td][td]

3,369​

[/td][td][/td][td][/td][td]US-HI[/td][td][/td][td]

1,427,538​

[/td][td][/td][td]Hawaii-Aleutian Time Zone[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]Illinois[/td][td]

149,998​

[/td][td][/td][td][/td][td][/td][td][/td][td]Illinois[/td][td][/td][td]

2.63​

[/td][td]Central Time Zone[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td]Indiana[/td][td][/td][td]

18,713​

[/td][td]Indianapolis[/td][td]United States[/td][td][/td][td][/td][td]

6,666,818​

[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td]Nevada[/td][td]

286,367​

[/td][td]

17,952​

[/td][td]Carson City[/td][td]United States[/td][td]US-NV[/td][td]Nevada[/td][td][/td][td]

2.72​

[/td][td]Mountain Time Zone, Pacific Time Zone[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td]New Jersey[/td][td]

22,608​

[/td][td]

26,793​

[/td][td][/td][td]United States[/td][td]US-NJ[/td][td][/td][td]

9,005,644​

[/td][td]

2.73​

[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td]New York[/td][td][/td][td][/td][td]Albany[/td][td][/td][td]US-NY[/td][td]New York[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11​

[/td][td]Oregon[/td][td]

255,026​

[/td][td]

19,586​

[/td][td][/td][td]United States[/td][td]US-OR[/td][td]Oregon[/td][td]

4,142,776​

[/td][td]

2.51​

[/td][td]Mountain Time Zone, Pacific Time Zone[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

12​

[/td][td]Texas[/td][td][/td][td][/td][td]Austin[/td][td][/td][td]US-TX[/td][td][/td][td]

28,304,596​

[/td][td]

2.84​

[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

13​

[/td][td]Washington[/td][td]

184,827​

[/td][td]

44,077​

[/td][td]Olympia[/td][td][/td][td][/td][td]Washington[/td][td]

7,405,743​

[/td][td]

2.56​

[/td][td]Pacific Time Zone[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

14​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

15​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

16​

[/td][td][/td][td][/td][td]

​

[/td][td]

Florida​

[/td][td]<==Choose Country from the list[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

17​

[/td][td][/td][td][/td][td]

​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

18​

[/td][td][/td][td][/td][td]

Area​

[/td][td]

170,304​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

19​

[/td][td][/td][td][/td][td]

Capital​

[/td][td]

Tallahassee​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

20​

[/td][td][/td][td][/td][td]

Country/region​

[/td][td]

United States​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

21​

[/td][td][/td][td][/td][td]

Name​

[/td][td]

Florida​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

22​

[/td][td][/td][td][/td][td]

Population​

[/td][td]

21,670,000​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

23​

[/td][td][/td][td][/td][td]

Persons per household​

[/td][td]

2.63​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

24​

[/td][td][/td][td][/td][td]

Time zone(s)​

[/td][td]

​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Data [/td][/tr][/table]

 

[Fluff]

How about in D18 copied down
=INDEX($B$2:$J$13,MATCH($D$16,$A$2:$A$13,0),MATCH($C18,$B$1:$J$1,0))

 

[Omer_K]

How about in D18 copied down
=INDEX($B$2:$J$13,MATCH($D$16,$A$2:$A$13,0),MATCH($C18,$B$1:$J$1,0))

Hey,
Thanks but that was not my intention ..
My goal is to create a list only of the non-empty values and display them from the range of cells C18: C26
Thus, only what the values are then filled to display

 

[Fluff]

Originally you said

But anyway (even if it's empty) I want to see the three columns of:
Population, Persons per household, and Time zone (s)

Now you're saying the opposite. That formula shows the values as per the data you posted, if that is not what you want, can you please post a sample of what you do want.

 

[Omer_K]

Hey,
You can see at the range C18:C26 the titles that are not empty according to the country,
For Example,
I choose Florida:
in the main table [TABLE="width: 436"]
<tbody>[TR]
[TD="width: 146, align: left"]'Building permits' and 'Abbreviation' are empty In Florida so the title not show for the list at the range C18:C26 [/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 436"]
<tbody>[TR]
[TD="width: 146, align: left"][/TD]
[TD="width: 146, align: left"][/TD]
[TD="width: 146, align: left"][/TD]
[TD="width: 146, align: left"][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: cms_table_head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #E0E0F0"]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]16[/COLOR]​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

Florida​

[/TD]
[TD]<==Choose Country from the list[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #E0E0F0"]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]17[/COLOR]​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #E0E0F0"]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]18[/COLOR]​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

Area​

[/TD]
[TD]

170,304​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #E0E0F0"]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]19[/COLOR]​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

Capital​

[/TD]
[TD]

Tallahassee​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #E0E0F0"]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]20[/COLOR]​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

Country/region​

[/TD]
[TD]

United States​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #E0E0F0"]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]21[/COLOR]​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

Name​

[/TD]
[TD]

Florida​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #E0E0F0"]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]22[/COLOR]​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

Population​

[/TD]
[TD]

21,670,000​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #E0E0F0"]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]23[/COLOR]​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

Persons per household​

[/TD]
[TD]

2.63​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #E0E0F0"]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]24[/COLOR]​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

Time zone(s)​

[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="class: cms_table_head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #E0E0F0"]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]16[/COLOR]​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

Florida​

[/TD]
[TD]<==Choose Country from the list[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #E0E0F0"]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]17[/COLOR]​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #E0E0F0"]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]18[/COLOR]​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

Area​

[/TD]
[TD]

170,304​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #E0E0F0"]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]19[/COLOR]​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

Capital​

[/TD]
[TD]

Tallahassee​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #E0E0F0"]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]20[/COLOR]​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

Country/region​

[/TD]
[TD]

United States​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #E0E0F0"]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]21[/COLOR]​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

Name​

[/TD]
[TD]

Florida​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #E0E0F0"]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]22[/COLOR]​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

Population​

[/TD]
[TD]

21,670,000​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #E0E0F0"]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]23[/COLOR]​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

Persons per household​

[/TD]
[TD]

2.63​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #E0E0F0"]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]24[/COLOR]​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

Time zone(s)​

[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
</body>

 

[Fluff]

Ok I think I understand, you only want to show the tiles from B:G if there is data & show the titles from H:J regardless.
If that's the case, then unfortunately I don't know how to do that. So hopefully somebody else will step in & help.

 

[sandy666]

are you able to use Power Query (Get&Transform) ?

 

[Omer_K]

Hey,
I would like in formula...

 

[Aladin Akyurek]

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

=IFERROR(INDEX($B$1:$J$1,SMALL(IF(INDEX($B$2:$J$13,MATCH($D$16,$A$2:$A$13,0),0)<>"",COLUMN($B$1:$J$1)-COLUMN($B$1)+1),ROWS($A$18:A18))),"")

In D18 just enter and copy down:

=IF($C18="","",VLOOKUP($D$16,$A$2:$J$14,MATCH(C18,$A$1:$J$1,0),0))

where D16 = Florida.

 

[Omer_K]

Dear Aladin - you are the best!!
if i wuold like No matter what, at the bottom of the list in range C18:.. always get the three last columns: Population,Persons per household,Time zone(s) Although one may be empty..