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

This code (placed in your sheet module) will RETAIN your Existing yet unused Column B countries. Just another way to acheive your goal...
Jim

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Alr As Long, Blr As Long
Dim i As Integer, j As Integer
Dim Mysource As Range, x As Range
If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
Application.EnableEvents = False
Alr = Range("A" & Rows.Count).End(xlUp).Row
Blr = Range("B" & Rows.Count).End(xlUp).Row
Set Mysource = Range("B1:B" & Blr)
i = 1
j = 1
With Mysource
    Set x = .Find(what:=Cells(i, 1), LookIn:=xlValues)
    Do
    If x Is Nothing Then
    Cells(Blr + 1, 2).Value = Cells(i, 1).Value
    Blr = Blr + 1
    i = i + 1
    j = j + 1
    Set x = .Find(Cells(i, 1))
    Else
    i = i + 1
    j = j + 1
    Set x = .Find(Cells(i, 1))
    End If
    Loop While Cells(Alr, 1).Row > j - 1
End With
Application.EnableEvents = True
End Sub

T. Valko · Jun 13, 2011

cybercedd said:
Thanks so much! This seems to work perfectly!

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

Originally Posted by T. Valko
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.

Click to expand...

You're welcome. Thanks for the feedback!

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

cybercedd

New Member

jim may

Well-known Member

Excel Facts

T. Valko

Well-known Member

Similar threads

Forum statistics

Share this page

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

cybercedd

New Member

jim may

Well-known Member

Excel Facts

T. Valko

Well-known 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