Extract only the Titles that not empty

[Omer_K]

Hello friends,
In the example I have attached, I have a list of countries where some states have data in the same column and some do not.
In the table below I would like to see only a list of countries that have data.


Hello friends,
In the example I have attached, I have a list of countries where some states have data in the same column and some do not.
I would like to know how to create list of the countries that onlt have data have data.


I'll be glad to receive your help
Thank you

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][th]

K​

[/th][th]

L​

[/th][th]

M​

[/th][th]

N​

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

1​

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

Alabama​

[/td][td]

California​

[/td][td]

Florida​

[/td][td]

Hawaii​

[/td][td]

Illinois​

[/td][td]

Indiana​

[/td][td]

Nevada​

[/td][td]

New Jersey​

[/td][td]

New York​

[/td][td]

Oregon​

[/td][td]

Texas​

[/td][td]

Washington​

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


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

2​

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

3​

[/td][td]

1​

[/td][td]

2​

[/td][td]

​

[/td][td]

3​

[/td][td]

1​

[/td][td]

1​

[/td][td]

​

[/td][td]

5​

[/td][td]

​

[/td][td]

1​

[/td][td]

​

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


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

3​

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

1​

[/td][td]

1​

[/td][td]

2​

[/td][td]

​

[/td][td]

2​

[/td][td]

1​

[/td][td]

5​

[/td][td]

​

[/td][td]

3​

[/td][td]

​

[/td][td]

​

[/td][td]

​

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


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

4​

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

5​

[/td][td]

4​

[/td][td]

​

[/td][td]

​

[/td][td]

3​

[/td][td]

​

[/td][td]

3​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

4​

[/td][td]

​

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


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

5​

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

3​

[/td][td]

​

[/td][td]

5​

[/td][td]

​

[/td][td]

​

[/td][td]

5​

[/td][td]

5​

[/td][td]

​

[/td][td]

1​

[/td][td]

​

[/td][td]

5​

[/td][td]

​

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


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

6​

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

3​

[/td][td]

3​

[/td][td]

1​

[/td][td]

​

[/td][td]

5​

[/td][td]

1​

[/td][td]

2​

[/td][td]

​

[/td][td]

2​

[/td][td]

​

[/td][td]

​

[/td][td]

​

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


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

7​

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

5​

[/td][td]

3​

[/td][td]

4​

[/td][td]

​

[/td][td]

5​

[/td][td]

3​

[/td][td]

2​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

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


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

8​

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

3​

[/td][td]

2​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

2​

[/td][td]

​

[/td][td]

​

[/td][td]

1​

[/td][td]

​

[/td][td]

1​

[/td][td]

​

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


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

9​

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

4​

[/td][td]

​

[/td][td]

5​

[/td][td]

​

[/td][td]

5​

[/td][td]

3​

[/td][td]

2​

[/td][td]

​

[/td][td]

1​

[/td][td]

​

[/td][td]

​

[/td][td]

​

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


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

10​

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

3​

[/td][td]

3​

[/td][td]

2​

[/td][td]

​

[/td][td]

5​

[/td][td]

​

[/td][td]

4​

[/td][td]

​

[/td][td]

3​

[/td][td]

​

[/td][td]

1​

[/td][td]

​

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


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

11​

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

3​

[/td][td]

3​

[/td][td]

4​

[/td][td]

​

[/td][td]

4​

[/td][td]

2​

[/td][td]

4​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

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


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

12​

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

4​

[/td][td]

3​

[/td][td]

3​

[/td][td]

​

[/td][td]

2​

[/td][td]

4​

[/td][td]

5​

[/td][td]

​

[/td][td]

5​

[/td][td]

​

[/td][td]

1​

[/td][td]

​

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


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

13​

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

​

[/td][td]

​

[/td][td]

​

[/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]

14​

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

​

[/td][td]

​

[/td][td]

​

[/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]

Alabama​

[/td][td]

California​

[/td][td]

Florida​

[/td][td]

Illinois​

[/td][td]

Indiana​

[/td][td]

Nevada​

[/td][td]

New York​

[/td][td]

Texas​

[/td][td]

Washington​

[/td][td]

​

[/td][td]

​

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


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

16​

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

​

[/td][td]

​

[/td][td]

​

[/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]

17​

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

​

[/td][td]

​

[/td][td]

​

[/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]

​

[/td][td]

​

[/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]

19​

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

​

[/td][td]

​

[/td][td]

​

[/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]

20​

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

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/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]

 

[Eric W]

Put this in C15 and drag right:

=IFERROR(INDEX($1:$1,AGGREGATE(15,6,COLUMN($B$1:$M$1)/(MMULT(TRANSPOSE(ROW($B$2:$B$12)),--($B$2:$M$2<>""))>0),COLUMNS($C15:C15))),"")

And it appears that Washington should not be listed?

 

[Omer_K]

Eric you are a genius!!


You're definitely right - Washington shouldn't show up,


Thanks so much it works perfect

 

[Eric W]

Happy to help!