Using IF and COUNTIF to add new names to a list.

cybercedd · Jun 13, 2011

Hello, everyone!

I'm new here, so please bear with me if I don't use the correct format for this question (or if my post title isn't as clear as it could be, haha). I've been looking around the forums and all over to try and figure this out, but haven't had any luck, so here I am.

The Situation: I have two columns, each with a list of countries. In Column A, countries are listed randomly as I encounter people from those places. Therefore I may have multiple occurrences of the same country. In Column B, I only want each country to appear once (for the purposes of using a COUNTIF in Column C and tallying the occurrence of each country in A).

The Problem: I can't seem to get the new cell (at the bottom of the list in Column B) to consistently check and add only countries that do not already appear in Column B. I have used IFs and COUNTIFs to check back, but can't seem to create the loop I need. The example below might help.

Example:

 <table class="MsoTableGrid" style="width: 243.9pt; border-collapse: collapse; border: medium none;" width="325" border="1" cellpadding="0" cellspacing="0"> <tbody><tr style=""> <td style="width: 27.9pt; border: 1pt solid windowtext; padding: 0in 5.4pt;" valign="top" width="37">
</td> <td style="width: 1.5in; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt;" valign="bottom" width="144"> A
</td> <td style="width: 1.5in; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt;" valign="bottom" width="144"> B
</td> </tr> <tr style=""> <td style="width: 27.9pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt;" valign="top" width="37"> 1
</td> <td style="width: 1.5in; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt;" valign="bottom" width="144"> China
</td> <td style="width: 1.5in; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt;" valign="bottom" width="144"> China
</td> </tr> <tr style=""> <td style="width: 27.9pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt;" valign="top" width="37"> 2
</td> <td style="width: 1.5in; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt;" valign="bottom" width="144"> Malaysia
</td> <td style="width: 1.5in; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt;" valign="bottom" width="144"> Malaysia
</td> </tr> <tr style=""> <td style="width: 27.9pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt;" valign="top" width="37"> 3
</td> <td style="width: 1.5in; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt;" valign="bottom" width="144"> Russia
</td> <td style="width: 1.5in; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt;" valign="bottom" width="144"> India
</td> </tr> <tr style=""> <td style="width: 27.9pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt;" valign="top" width="37"> 4
</td> <td style="width: 1.5in; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt;" valign="bottom" width="144"> Djibouti
</td> <td style="width: 1.5in; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt;" valign="bottom" width="144"> Canada
</td> </tr> <tr style=""> <td style="width: 27.9pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt;" valign="top" width="37"> 5
</td> <td style="width: 1.5in; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt;" valign="bottom" width="144"> Albania
</td> <td style="width: 1.5in; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt;" valign="bottom" width="144"> USA
</td> </tr> <tr style=""> <td style="width: 27.9pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt;" valign="top" width="37"> 6
</td> <td style="width: 1.5in; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt;" valign="bottom" width="144"> Russia
</td> <td style="width: 1.5in; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt;" valign="bottom" width="144"> Libya
</td> </tr> <tr style=""> <td style="width: 27.9pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt;" valign="top" width="37"> 7
</td> <td style="width: 1.5in; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt;" valign="bottom" width="144"> Canada
</td> <td style="width: 1.5in; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt;" valign="bottom" width="144"> Sweden
</td> </tr> <tr style=""> <td style="width: 27.9pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt;" valign="top" width="37"> 8
</td> <td style="width: 1.5in; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt;" valign="bottom" width="144"> Thailand
</td> <td style="width: 1.5in; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt;" valign="bottom" width="144"> Russia
</td> </tr> <tr style=""> <td style="width: 27.9pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt;" valign="top" width="37"> 9
</td> <td style="width: 1.5in; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt;" valign="bottom" width="144"> Canada
</td> <td style="width: 1.5in; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt;" valign="bottom" width="144"> Japan
</td> </tr> <tr style=""> <td style="width: 27.9pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt;" valign="top" width="37"> 10
</td> <td style="width: 1.5in; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt;" valign="bottom" width="144"> Russia
</td> <td style="width: 1.5in; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt;" valign="bottom" width="144"> Zimbabwe
</td> </tr> <tr style=""> <td style="width: 27.9pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt;" valign="top" width="37"> 11
</td> <td style="width: 1.5in; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt;" valign="bottom" width="144"> china
</td> <td style="width: 1.5in; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt;" valign="bottom" width="144"> Sri Lanka
</td> </tr> <tr style=""> <td style="width: 27.9pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt;" valign="top" width="37"> 12
</td> <td style="width: 1.5in; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt;" valign="bottom" width="144"> Bangladesh
</td> <td style="width: 1.5in; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt;" valign="bottom" width="144"> Bangladesh
</td> </tr> <tr style=""> <td style="width: 27.9pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt;" valign="top" width="37"> 13
</td> <td style="width: 1.5in; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt;" valign="bottom" width="144"> Jordan
</td> <td style="width: 1.5in; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt;" valign="bottom" width="144"> Iran
</td> </tr> <tr style=""> <td style="width: 27.9pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt;" valign="top" width="37"> 14
</td> <td style="width: 1.5in; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt;" valign="bottom" width="144"> Thailand
</td> <td style="width: 1.5in; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt;" valign="bottom" width="144"> Ukraine
</td> </tr> <tr style=""> <td style="width: 27.9pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt;" valign="top" width="37"> 15
</td> <td style="width: 1.5in; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt;" valign="bottom" width="144"> Romania
</td> <td style="width: 1.5in; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt;" valign="bottom" width="144"> Morocco
</td> </tr> <tr style=""> <td style="width: 27.9pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt;" valign="top" width="37"> 16
</td> <td style="width: 1.5in; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt;" valign="bottom" width="144"> China
</td> <td style="width: 1.5in; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt;" valign="bottom" width="144"> Saudi Arabia
</td> </tr> <tr style=""> <td style="width: 27.9pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt;" valign="top" width="37"> 17
</td> <td style="width: 1.5in; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt;" valign="bottom" width="144"> China
</td> <td style="width: 1.5in; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt;" valign="bottom" width="144"> France
</td> </tr> <tr style=""> <td style="width: 27.9pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt;" valign="top" width="37"> 18
</td> <td style="width: 1.5in; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt;" valign="bottom" width="144"> China
</td> <td style="width: 1.5in; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt;" valign="bottom" width="144"> Mexico
</td> </tr> <tr style=""> <td style="width: 27.9pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt;" valign="top" width="37"> 19
</td> <td style="width: 1.5in; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt;" valign="bottom" width="144"> China
</td> <td style="width: 1.5in; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt;" valign="bottom" width="144"> Nigeria
</td> </tr> <tr style=""> <td style="width: 27.9pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt;" valign="top" width="37"> 20
</td> <td style="width: 1.5in; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt;" valign="bottom" width="144"> Canada
</td> <td style="width: 1.5in; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt;" valign="top" width="144">
</td> </tr> <tr style=""> <td style="width: 27.9pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt;" valign="top" width="37"> 21
</td> <td style="width: 1.5in; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt;" valign="bottom" width="144"> New Zealand
</td> <td style="width: 1.5in; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt;" valign="top" width="144">
</td> </tr> </tbody></table>
The Formula:
In Cell B20:
=IF(COUNTIF(B$1:B19,A1)=0,A1,IF(COUNTIF(B$1:B19,A2)=0,A2,IF(COUNTIF(B$1:B19,A3)=0,A3,IF(COUNTIF(B$1:B19,A4)=0,A4,"")))) etc.

What I'm trying to say is "If the country in A1 isn't in B, add it. If it is there, check A2 and do the same." I know I could just type the formula out so that it will check all the way up to A21, or A100, but I feel like the should be some way of having excel loop back and continue checking.

There's probably something simple that I'm missing here, but I can't find it. Any help is greatly appreciated.

Thank-you!

jim may · Jun 13, 2011

Not sure this is what you want, but...

Excel Workbook

A

B

1

China

2

Malaysia

3

Russia

4

Djibouti

5

Albania

6

Russia

7

Canada

8

Thailand

9

Canada

10

Russia

11

china

12

Bangladesh

13

Jordan

Sheet1

Excel 2007

Cell Formulas
Range	Formula
B1	=IF(COUNTIF(A$1:A1,A1)>1,"",A1)

cybercedd · Jun 13, 2011

Thanks, jim may, but it isn't quite what I'm looking for. I'm hoping to end up with a list in B without any blank cells. That's the piece that's giving me trouble.

I realize that I also forgot to mention that I'm using Excel 2007 and Windows 7 (if that makes any difference).

Arcticwarrio · Jun 13, 2011

just sort the column afterwards

cybercedd · Jun 13, 2011

Thank-you, Arcticwarrio, for your suggestion. Sorting the column afterwards would eliminate the blank cells, but doesn't accomplish the same goal. I would like all of this to happen automatically, which is accomplished by my IF/COUNTIF combination, but not quite to my liking.

T. Valko · Jun 13, 2011

cybercedd said:
Hello, everyone!

I'm new here, so please bear with me if I don't use the correct format for this question (or if my post title isn't as clear as it could be, haha). I've been looking around the forums and all over to try and figure this out, but haven't had any luck, so here I am.

The Situation: I have two columns, each with a list of countries. In Column A, countries are listed randomly as I encounter people from those places. Therefore I may have multiple occurrences of the same country. In Column B, I only want each country to appear once (for the purposes of using a COUNTIF in Column C and tallying the occurrence of each country in A).

The Problem: I can't seem to get the new cell (at the bottom of the list in Column B) to consistently check and add only countries that do not already appear in Column B. I have used IFs and COUNTIFs to check back, but can't seem to create the loop I need. The example below might help.

Example:

<TABLE class=MsoTableGrid style="BORDER-RIGHT: medium none; BORDER-TOP: medium none; BORDER-LEFT: medium none; WIDTH: 243.9pt; BORDER-BOTTOM: medium none; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=325 border=1><TBODY><TR><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; WIDTH: 27.9pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid" vAlign=top width=37>

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 1.5in; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid" vAlign=bottom width=144>A

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 1.5in; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid" vAlign=bottom width=144>B

</TD></TR><TR><TD style="BORDER-RIGHT: 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: 1pt solid; WIDTH: 27.9pt; PADDING-TOP: 0in; BORDER-BOTTOM: 1pt solid" vAlign=top width=37>1

</TD><TD style="BORDER-RIGHT: 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 1.5in; PADDING-TOP: 0in; BORDER-BOTTOM: 1pt solid" vAlign=bottom width=144>China

</TD><TD style="BORDER-RIGHT: 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 1.5in; PADDING-TOP: 0in; BORDER-BOTTOM: 1pt solid" vAlign=bottom width=144>China

</TD></TR><TR><TD style="BORDER-RIGHT: 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: 1pt solid; WIDTH: 27.9pt; PADDING-TOP: 0in; BORDER-BOTTOM: 1pt solid" vAlign=top width=37>2

</TD><TD style="BORDER-RIGHT: 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 1.5in; PADDING-TOP: 0in; BORDER-BOTTOM: 1pt solid" vAlign=bottom width=144>Malaysia

</TD><TD style="BORDER-RIGHT: 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 1.5in; PADDING-TOP: 0in; BORDER-BOTTOM: 1pt solid" vAlign=bottom width=144>Malaysia

</TD></TR><TR><TD style="BORDER-RIGHT: 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: 1pt solid; WIDTH: 27.9pt; PADDING-TOP: 0in; BORDER-BOTTOM: 1pt solid" vAlign=top width=37>3

</TD><TD style="BORDER-RIGHT: 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 1.5in; PADDING-TOP: 0in; BORDER-BOTTOM: 1pt solid" vAlign=bottom width=144>Russia

</TD><TD style="BORDER-RIGHT: 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 1.5in; PADDING-TOP: 0in; BORDER-BOTTOM: 1pt solid" vAlign=bottom width=144>India

</TD></TR><TR><TD style="BORDER-RIGHT: 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: 1pt solid; WIDTH: 27.9pt; PADDING-TOP: 0in; BORDER-BOTTOM: 1pt solid" vAlign=top width=37>4

</TD><TD style="BORDER-RIGHT: 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 1.5in; PADDING-TOP: 0in; BORDER-BOTTOM: 1pt solid" vAlign=bottom width=144>Djibouti

</TD><TD style="BORDER-RIGHT: 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 1.5in; PADDING-TOP: 0in; BORDER-BOTTOM: 1pt solid" vAlign=bottom width=144>Canada

</TD></TR><TR><TD style="BORDER-RIGHT: 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: 1pt solid; WIDTH: 27.9pt; PADDING-TOP: 0in; BORDER-BOTTOM: 1pt solid" vAlign=top width=37>5

</TD><TD style="BORDER-RIGHT: 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 1.5in; PADDING-TOP: 0in; BORDER-BOTTOM: 1pt solid" vAlign=bottom width=144>Albania

</TD><TD style="BORDER-RIGHT: 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 1.5in; PADDING-TOP: 0in; BORDER-BOTTOM: 1pt solid" vAlign=bottom width=144>USA

</TD></TR><TR><TD style="BORDER-RIGHT: 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: 1pt solid; WIDTH: 27.9pt; PADDING-TOP: 0in; BORDER-BOTTOM: 1pt solid" vAlign=top width=37>6

</TD><TD style="BORDER-RIGHT: 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 1.5in; PADDING-TOP: 0in; BORDER-BOTTOM: 1pt solid" vAlign=bottom width=144>Russia

</TD><TD style="BORDER-RIGHT: 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 1.5in; PADDING-TOP: 0in; BORDER-BOTTOM: 1pt solid" vAlign=bottom width=144>Libya

</TD></TR><TR><TD style="BORDER-RIGHT: 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: 1pt solid; WIDTH: 27.9pt; PADDING-TOP: 0in; BORDER-BOTTOM: 1pt solid" vAlign=top width=37>7

</TD><TD style="BORDER-RIGHT: 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 1.5in; PADDING-TOP: 0in; BORDER-BOTTOM: 1pt solid" vAlign=bottom width=144>Canada

</TD><TD style="BORDER-RIGHT: 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 1.5in; PADDING-TOP: 0in; BORDER-BOTTOM: 1pt solid" vAlign=bottom width=144>Sweden

</TD></TR><TR><TD style="BORDER-RIGHT: 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: 1pt solid; WIDTH: 27.9pt; PADDING-TOP: 0in; BORDER-BOTTOM: 1pt solid" vAlign=top width=37>8

</TD><TD style="BORDER-RIGHT: 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 1.5in; PADDING-TOP: 0in; BORDER-BOTTOM: 1pt solid" vAlign=bottom width=144>Thailand

</TD><TD style="BORDER-RIGHT: 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 1.5in; PADDING-TOP: 0in; BORDER-BOTTOM: 1pt solid" vAlign=bottom width=144>Russia

</TD></TR><TR><TD style="BORDER-RIGHT: 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: 1pt solid; WIDTH: 27.9pt; PADDING-TOP: 0in; BORDER-BOTTOM: 1pt solid" vAlign=top width=37>9

</TD><TD style="BORDER-RIGHT: 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 1.5in; PADDING-TOP: 0in; BORDER-BOTTOM: 1pt solid" vAlign=bottom width=144>Canada

</TD><TD style="BORDER-RIGHT: 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 1.5in; PADDING-TOP: 0in; BORDER-BOTTOM: 1pt solid" vAlign=bottom width=144>Japan

</TD></TR><TR><TD style="BORDER-RIGHT: 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: 1pt solid; WIDTH: 27.9pt; PADDING-TOP: 0in; BORDER-BOTTOM: 1pt solid" vAlign=top width=37>10

</TD><TD style="BORDER-RIGHT: 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 1.5in; PADDING-TOP: 0in; BORDER-BOTTOM: 1pt solid" vAlign=bottom width=144>Russia

</TD><TD style="BORDER-RIGHT: 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 1.5in; PADDING-TOP: 0in; BORDER-BOTTOM: 1pt solid" vAlign=bottom width=144>Zimbabwe

</TD></TR><TR><TD style="BORDER-RIGHT: 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: 1pt solid; WIDTH: 27.9pt; PADDING-TOP: 0in; BORDER-BOTTOM: 1pt solid" vAlign=top width=37>11

</TD><TD style="BORDER-RIGHT: 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 1.5in; PADDING-TOP: 0in; BORDER-BOTTOM: 1pt solid" vAlign=bottom width=144>china

</TD><TD style="BORDER-RIGHT: 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 1.5in; PADDING-TOP: 0in; BORDER-BOTTOM: 1pt solid" vAlign=bottom width=144>Sri Lanka

</TD></TR><TR><TD style="BORDER-RIGHT: 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: 1pt solid; WIDTH: 27.9pt; PADDING-TOP: 0in; BORDER-BOTTOM: 1pt solid" vAlign=top width=37>12

</TD><TD style="BORDER-RIGHT: 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 1.5in; PADDING-TOP: 0in; BORDER-BOTTOM: 1pt solid" vAlign=bottom width=144>Bangladesh

</TD><TD style="BORDER-RIGHT: 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 1.5in; PADDING-TOP: 0in; BORDER-BOTTOM: 1pt solid" vAlign=bottom width=144>Bangladesh

</TD></TR><TR><TD style="BORDER-RIGHT: 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: 1pt solid; WIDTH: 27.9pt; PADDING-TOP: 0in; BORDER-BOTTOM: 1pt solid" vAlign=top width=37>13

</TD><TD style="BORDER-RIGHT: 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 1.5in; PADDING-TOP: 0in; BORDER-BOTTOM: 1pt solid" vAlign=bottom width=144>Jordan

</TD><TD style="BORDER-RIGHT: 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 1.5in; PADDING-TOP: 0in; BORDER-BOTTOM: 1pt solid" vAlign=bottom width=144>Iran

</TD></TR><TR><TD style="BORDER-RIGHT: 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: 1pt solid; WIDTH: 27.9pt; PADDING-TOP: 0in; BORDER-BOTTOM: 1pt solid" vAlign=top width=37>14

</TD><TD style="BORDER-RIGHT: 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 1.5in; PADDING-TOP: 0in; BORDER-BOTTOM: 1pt solid" vAlign=bottom width=144>Thailand

</TD><TD style="BORDER-RIGHT: 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 1.5in; PADDING-TOP: 0in; BORDER-BOTTOM: 1pt solid" vAlign=bottom width=144>Ukraine

</TD></TR><TR><TD style="BORDER-RIGHT: 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: 1pt solid; WIDTH: 27.9pt; PADDING-TOP: 0in; BORDER-BOTTOM: 1pt solid" vAlign=top width=37>15

</TD><TD style="BORDER-RIGHT: 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 1.5in; PADDING-TOP: 0in; BORDER-BOTTOM: 1pt solid" vAlign=bottom width=144>Romania

</TD><TD style="BORDER-RIGHT: 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 1.5in; PADDING-TOP: 0in; BORDER-BOTTOM: 1pt solid" vAlign=bottom width=144>Morocco

</TD></TR><TR><TD style="BORDER-RIGHT: 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: 1pt solid; WIDTH: 27.9pt; PADDING-TOP: 0in; BORDER-BOTTOM: 1pt solid" vAlign=top width=37>16

</TD><TD style="BORDER-RIGHT: 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 1.5in; PADDING-TOP: 0in; BORDER-BOTTOM: 1pt solid" vAlign=bottom width=144>China

</TD><TD style="BORDER-RIGHT: 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 1.5in; PADDING-TOP: 0in; BORDER-BOTTOM: 1pt solid" vAlign=bottom width=144>Saudi Arabia

</TD></TR><TR><TD style="BORDER-RIGHT: 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: 1pt solid; WIDTH: 27.9pt; PADDING-TOP: 0in; BORDER-BOTTOM: 1pt solid" vAlign=top width=37>17

</TD><TD style="BORDER-RIGHT: 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 1.5in; PADDING-TOP: 0in; BORDER-BOTTOM: 1pt solid" vAlign=bottom width=144>China

</TD><TD style="BORDER-RIGHT: 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 1.5in; PADDING-TOP: 0in; BORDER-BOTTOM: 1pt solid" vAlign=bottom width=144>France

</TD></TR><TR><TD style="BORDER-RIGHT: 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: 1pt solid; WIDTH: 27.9pt; PADDING-TOP: 0in; BORDER-BOTTOM: 1pt solid" vAlign=top width=37>18

</TD><TD style="BORDER-RIGHT: 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 1.5in; PADDING-TOP: 0in; BORDER-BOTTOM: 1pt solid" vAlign=bottom width=144>China

</TD><TD style="BORDER-RIGHT: 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 1.5in; PADDING-TOP: 0in; BORDER-BOTTOM: 1pt solid" vAlign=bottom width=144>Mexico

</TD></TR><TR><TD style="BORDER-RIGHT: 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: 1pt solid; WIDTH: 27.9pt; PADDING-TOP: 0in; BORDER-BOTTOM: 1pt solid" vAlign=top width=37>19

</TD><TD style="BORDER-RIGHT: 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 1.5in; PADDING-TOP: 0in; BORDER-BOTTOM: 1pt solid" vAlign=bottom width=144>China

</TD><TD style="BORDER-RIGHT: 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 1.5in; PADDING-TOP: 0in; BORDER-BOTTOM: 1pt solid" vAlign=bottom width=144>Nigeria

</TD></TR><TR><TD style="BORDER-RIGHT: 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: 1pt solid; WIDTH: 27.9pt; PADDING-TOP: 0in; BORDER-BOTTOM: 1pt solid" vAlign=top width=37>20

</TD><TD style="BORDER-RIGHT: 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 1.5in; PADDING-TOP: 0in; BORDER-BOTTOM: 1pt solid" vAlign=bottom width=144>Canada

</TD><TD style="BORDER-RIGHT: 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 1.5in; PADDING-TOP: 0in; BORDER-BOTTOM: 1pt solid" vAlign=top width=144>

</TD></TR><TR><TD style="BORDER-RIGHT: 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: 1pt solid; WIDTH: 27.9pt; PADDING-TOP: 0in; BORDER-BOTTOM: 1pt solid" vAlign=top width=37>21

</TD><TD style="BORDER-RIGHT: 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 1.5in; PADDING-TOP: 0in; BORDER-BOTTOM: 1pt solid" vAlign=bottom width=144>New Zealand

</TD><TD style="BORDER-RIGHT: 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 1.5in; PADDING-TOP: 0in; BORDER-BOTTOM: 1pt solid" vAlign=top width=144>

</TD></TR></TBODY></TABLE>
The Formula:
In Cell B20:
=IF(COUNTIF(B$1:B19,A1)=0,A1,IF(COUNTIF(B$1:B19,A2)=0,A2,IF(COUNTIF(B$1:B19,A3)=0,A3,IF(COUNTIF(B$1:B19,A4)=0,A4,"")))) etc.

What I'm trying to say is "If the country in A1 isn't in B, add it. If it is there, check A2 and do the same." I know I could just type the formula out so that it will check all the way up to A21, or A100, but I feel like the should be some way of having excel loop back and continue checking.

There's probably something simple that I'm missing here, but I can't find it. Any help is greatly appreciated.

Thank-you!

What version of Excel are you using?

cybercedd · Jun 13, 2011

Excel 2007.
Windows 7.

Aladin Akyurek · Jun 13, 2011

cybercedd said:
Thanks, jim may, but it isn't quite what I'm looking for. I'm hoping to end up with a list in B without any blank cells. That's the piece that's giving me trouble.

I realize that I also forgot to mention that I'm using Excel 2007 and Windows 7 (if that makes any difference).

Consider A1:A22, including the header...

<TABLE style="WIDTH: 185pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=246><COLGROUP><COL style="WIDTH: 93pt; mso-width-source: userset; mso-width-alt: 4408" width=124><COL style="WIDTH: 92pt; mso-width-source: userset; mso-width-alt: 4352" width=122><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 93pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=124>Field-1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 92pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=122>Field-2</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 93pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19 width=124>China</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>China</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 93pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19 width=124>Malaysia</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Russia</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 93pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19 width=124>Russia</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Canada</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 93pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19 width=124>Djibouti</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Thailand</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 93pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19 width=124>Albania</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Malaysia</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 93pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19 width=124>Russia</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Djibouti</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 93pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19 width=124>Canada</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Albania</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 93pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19 width=124>Thailand</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Bangladesh</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 93pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19 width=124>Canada</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Jordan</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 93pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19 width=124>Russia</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Romania</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 93pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19 width=124>china</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>New Zealand</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 93pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19 width=124>Bangladesh</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 93pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19 width=124>Jordan</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 93pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19 width=124>Thailand</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 93pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19 width=124>Romania</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 93pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19 width=124>China</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 93pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19 width=124>China</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 93pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19 width=124>China</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 93pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19 width=124>China</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 93pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19 width=124>Canada</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD></TR>

<TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 93pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2749874 class=xl64 height=19 width=124>New Zealand</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD></TR></TBODY></TABLE>

B2, control+shift+enter, not just enter, copy down:

Code:

=IFERROR(INDEX($A$2:$A$22,MODE(IF($A$2:$A$22<>"",
    IF(ISNA(MATCH($A$2:$A$22,$B$1:B1,0)),
     MATCH($A$2:$A$22,$A$2:$A$22,0)*{1,1})))),"")

The following set up should be faster and fully automatic...

<TABLE style="WIDTH: 192pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=256><COLGROUP><COL style="WIDTH: 73pt; mso-width-source: userset; mso-width-alt: 3470" width=98><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3356" width=94><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 73pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=98> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 71pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=94 align=right>11</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-STYLE: italic; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; HEIGHT: 14.4pt; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #4F81BD none; text-underline-style: none; text-line-through: none" class=xl67 height=19>Field-1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-STYLE: italic; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #4F81BD none; text-underline-style: none; text-line-through: none" class=xl67>Field-2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-STYLE: italic; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #4F81BD none; text-underline-style: none; text-line-through: none" class=xl67>Field-3</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 73pt; FONT-FAMILY: Verdana; BACKGROUND: #efefef; HEIGHT: 14.4pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl66 height=19 width=98>China</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #DCE6F1 none; text-underline-style: none; text-line-through: none" class=xl65 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #DCE6F1 none; text-underline-style: none; text-line-through: none" class=xl65>China</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 73pt; FONT-FAMILY: Verdana; BACKGROUND: #efefef; HEIGHT: 14.4pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl66 height=19 width=98>Malaysia</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65 align=right>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65>Malaysia</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 73pt; FONT-FAMILY: Verdana; BACKGROUND: #efefef; HEIGHT: 14.4pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl66 height=19 width=98>Russia</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #DCE6F1 none; text-underline-style: none; text-line-through: none" class=xl65 align=right>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #DCE6F1 none; text-underline-style: none; text-line-through: none" class=xl65>Russia</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 73pt; FONT-FAMILY: Verdana; BACKGROUND: #efefef; HEIGHT: 14.4pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl66 height=19 width=98>Djibouti</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65 align=right>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65>Djibouti</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 73pt; FONT-FAMILY: Verdana; BACKGROUND: #efefef; HEIGHT: 14.4pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl66 height=19 width=98>Albania</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #DCE6F1 none; text-underline-style: none; text-line-through: none" class=xl65 align=right>5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #DCE6F1 none; text-underline-style: none; text-line-through: none" class=xl65>Albania</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 73pt; FONT-FAMILY: Verdana; BACKGROUND: #efefef; HEIGHT: 14.4pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl66 height=19 width=98>Russia</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65>Canada</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 73pt; FONT-FAMILY: Verdana; BACKGROUND: #efefef; HEIGHT: 14.4pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl66 height=19 width=98>Canada</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #DCE6F1 none; text-underline-style: none; text-line-through: none" class=xl65 align=right>6</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #DCE6F1 none; text-underline-style: none; text-line-through: none" class=xl65>Thailand</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 73pt; FONT-FAMILY: Verdana; BACKGROUND: #efefef; HEIGHT: 14.4pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl66 height=19 width=98>Thailand</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65 align=right>7</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65>Bangladesh</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 73pt; FONT-FAMILY: Verdana; BACKGROUND: #efefef; HEIGHT: 14.4pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl66 height=19 width=98>Canada</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #DCE6F1 none; text-underline-style: none; text-line-through: none" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #DCE6F1 none; text-underline-style: none; text-line-through: none" class=xl65>Jordan</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 73pt; FONT-FAMILY: Verdana; BACKGROUND: #efefef; HEIGHT: 14.4pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl66 height=19 width=98>Russia</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65>Romania</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 73pt; FONT-FAMILY: Verdana; BACKGROUND: #efefef; HEIGHT: 14.4pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl66 height=19 width=98>china</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #DCE6F1 none; text-underline-style: none; text-line-through: none" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #DCE6F1 none; text-underline-style: none; text-line-through: none" class=xl65>New Zealand</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 73pt; FONT-FAMILY: Verdana; BACKGROUND: #efefef; HEIGHT: 14.4pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl66 height=19 width=98>Bangladesh</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65 align=right>8</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 73pt; FONT-FAMILY: Verdana; BACKGROUND: #efefef; HEIGHT: 14.4pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl66 height=19 width=98>Jordan</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #DCE6F1 none; text-underline-style: none; text-line-through: none" class=xl65 align=right>9</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #DCE6F1 none; text-underline-style: none; text-line-through: none" class=xl65> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 73pt; FONT-FAMILY: Verdana; BACKGROUND: #efefef; HEIGHT: 14.4pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl66 height=19 width=98>Thailand</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 73pt; FONT-FAMILY: Verdana; BACKGROUND: #efefef; HEIGHT: 14.4pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl66 height=19 width=98>Romania</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #DCE6F1 none; text-underline-style: none; text-line-through: none" class=xl65 align=right>10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #DCE6F1 none; text-underline-style: none; text-line-through: none" class=xl65> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 73pt; FONT-FAMILY: Verdana; BACKGROUND: #efefef; HEIGHT: 14.4pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl66 height=19 width=98>China</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 73pt; FONT-FAMILY: Verdana; BACKGROUND: #efefef; HEIGHT: 14.4pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl66 height=19 width=98>China</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #DCE6F1 none; text-underline-style: none; text-line-through: none" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #DCE6F1 none; text-underline-style: none; text-line-through: none" class=xl65> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 73pt; FONT-FAMILY: Verdana; BACKGROUND: #efefef; HEIGHT: 14.4pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl66 height=19 width=98>China</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 73pt; FONT-FAMILY: Verdana; BACKGROUND: #efefef; HEIGHT: 14.4pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl66 height=19 width=98>China</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #DCE6F1 none; text-underline-style: none; text-line-through: none" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #DCE6F1 none; text-underline-style: none; text-line-through: none" class=xl65> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 73pt; FONT-FAMILY: Verdana; BACKGROUND: #efefef; HEIGHT: 14.4pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl66 height=19 width=98>Canada</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 73pt; FONT-FAMILY: Verdana; BACKGROUND: #efefef; HEIGHT: 14.4pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl66 height=19 width=98>New Zealand</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #DCE6F1 none; text-underline-style: none; text-line-through: none" class=xl65 align=right>11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #DCE6F1 none; text-underline-style: none; text-line-through: none" class=xl65> </TD></TR></TBODY></TABLE>

Select A2:C23.
Run Insert | Table.

Insert 0 in B1.

In B2 enter and copy down:

=IF(A3<>"",IF(ISNA(MATCH(A3,$A$1:A2,0)),LOOKUP(9.99999999999999E+307,$B$1:B2)+1,""),"")

In C1 enter:

=LOOKUP(9.99999999999999E+307,Table2[Field-2])

Select the appropriate range while typing the formula. A structured reference
is automatically producded by Excel itself.

In C3 enter and copy down:

=IF(ROWS($C$3:C3)<=$C$1,LOOKUP(ROWS($C$3:C3),[Field-2],[Field-1]),"")

The formula will immediately respond on any new addition to or deletion from the range in column A.

T. Valko · Jun 13, 2011

cybercedd said:
Excel 2007.
Windows 7.

Create a dynamic range that refers to the data in column A.

Name: Countries
Refers to: =$A$2:INDEX($A$2:$A$100,COUNTA($A$2:$A$100))

Adjust for a reasonable end of range A100 to allow for future data additions.

Enter this formula in C2:

=A2

Enter this array formula** in C3:

=IFERROR(INDEX(Countries,MATCH(0,COUNTIF(C$2:C2,Countries),0)),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.

cybercedd · Jun 13, 2011

Thanks so much! This seems to work perfectly!

Thanks to everyone who helped, here. I really appreciate it.

T. Valko said:
Create a dynamic range that refers to the data in column A.

Name: Countries
Refers to: =$A$2:INDEX($A$2:$A$100,COUNTA($A$2:$A$100))

Adjust for a reasonable end of range A100 to allow for future data additions.

Enter this formula in C2:

=A2

Enter this array formula** in C3:

=IFERROR(INDEX(Countries,MATCH(0,COUNTIF(C$2:C2,Countries),0)),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.

Using IF and COUNTIF to add new names to a list.

cybercedd

New Member

Excel Facts

jim may

Well-known Member

cybercedd

New Member

Arcticwarrio

Active Member

cybercedd

New Member

T. Valko

Well-known Member

cybercedd

New Member

Aladin Akyurek

MrExcel MVP

T. Valko

Well-known Member

cybercedd

New Member

Similar threads

Forum statistics

Share this page

Using IF and COUNTIF to add new names to a list.

New Member

Excel Facts

Well-known Member

New Member

Active Member

New Member

Well-known Member

New Member

MrExcel MVP

Well-known Member

New Member

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