Display a list only with the values by country

Omer_K

Board Regular
Joined
Apr 9, 2017
Messages
124
Office Version
  1. 365
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
A
B
C
D
E
F
G
H
I
J
1
Country
AreaBuilding permitsCapitalCountry/regionAbbreviationNamePopulationPersons per householdTime zone(s)
2
Alabama
135,765​
MontgomeryUS-ALAlabama
4,874,747​
2.55​
Central Time Zone
3
California
102,350​
US-CACalifornia
39,536,653​
Pacific Time Zone
4
Florida
170,304​
TallahasseeUnited StatesFlorida
21,670,000​
2.63​
5
Hawaii
28,311​
3,369​
US-HI
1,427,538​
Hawaii-Aleutian Time Zone
6
Illinois
149,998​
Illinois
2.63​
Central Time Zone
7
Indiana
18,713​
IndianapolisUnited States
6,666,818​
8
Nevada
286,367​
17,952​
Carson CityUnited StatesUS-NVNevada
2.72​
Mountain Time Zone, Pacific Time Zone
9
New Jersey
22,608​
26,793​
United StatesUS-NJ
9,005,644​
2.73​
10
New YorkAlbanyUS-NYNew York
11
Oregon
255,026​
19,586​
United StatesUS-OROregon
4,142,776​
2.51​
Mountain Time Zone, Pacific Time Zone
12
TexasAustinUS-TX
28,304,596​
2.84​
13
Washington
184,827​
44,077​
OlympiaWashington
7,405,743​
2.56​
Pacific Time Zone
14
15
16
Florida​
<==Choose Country from the list
17
18
Area​
170,304​
19
Capital​
Tallahassee​
20
Country/region​
United States​
21
Name​
Florida​
22
Population​
21,670,000​
23
Persons per household​
2.63​
24
Time zone(s)​
Sheet: Data
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
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))
 
Upvote 0
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
 
Upvote 0
:confused: 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.
 
Upvote 0
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
'Building permits' and 'Abbreviation' are empty In Florida so the title not show for the list at the range C18:C26

<tbody>
</tbody>

<tbody>
</tbody>
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]16[/COLOR]​
Florida​
<==Choose Country from the list
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]17[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]18[/COLOR]​
Area​
170,304​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]19[/COLOR]​
Capital​
Tallahassee​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]20[/COLOR]​
Country/region​
United States​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]21[/COLOR]​
Name​
Florida​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]22[/COLOR]​
Population​
21,670,000​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]23[/COLOR]​
Persons per household​
2.63​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]24[/COLOR]​
Time zone(s)​

<tbody>
</tbody>


****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]16[/COLOR]​
Florida​
<==Choose Country from the list
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]17[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]18[/COLOR]​
Area​
170,304​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]19[/COLOR]​
Capital​
Tallahassee​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]20[/COLOR]​
Country/region​
United States​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]21[/COLOR]​
Name​
Florida​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]22[/COLOR]​
Population​
21,670,000​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]23[/COLOR]​
Persons per household​
2.63​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]24[/COLOR]​
Time zone(s)​

<tbody>
</tbody>
</body>
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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..
 
Upvote 0

Forum statistics

Threads
1,213,528
Messages
6,114,154
Members
448,553
Latest member
slaytonpa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top