countif....only once

[bluepenink]

Hello

My range of data is E12:E75 in worksheet "Data" contains city names (they repeat a few times)

In my "Calculation" worksheet, I would like to show the names only appear once from the list in E12:E75.

Is there any formulas? can someone pls advise.

thx you!

 

[Sahak]

Hi JW01,

Let assume:
1. data is in range E12:E75 in worksheet "Data" & E12 value is “Cities”
2. range A1 value is “Cities” in worksheet "Calculation" (same as in E12 in worksheet "Data")

This code will give you what you want

Sub Macro1()
    Range("Data!E12:E75").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("Calculation!A1"), Unique:=True
End Sub

 

[Aladin Akyurek]

Hello

My range of data is E12:E75 in worksheet "Data" contains city names (they repeat a few times)

In my "Calculation" worksheet, I would like to show the names only appear once from the list in E12:E75.

Is there any formulas? can someone pls advise.

thx you!

Calculation

A1: #List#

A2, control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX(Data!$E$2:$E$75,MODE(IF(Data!$E$2:$E$75<>"",
   IF(ISNA(MATCH(Data!$E$2:$E$75,$A$1:A1,0)),
    MATCH(Data!$E$2:$E$75,Data!$E$2:$E$75,0)*{1,1})))),"")

 

[bluepenink]

Hello Aladin,

I pasted the formula you provided in U52, and changed the $A$1:A1 to $U$52:U5 and dragged it down, but i get blanks?...any suggestions? thx u for your help.

array:

=IFERROR(INDEX(Data!$E$12:$E$75,MODE(IF(Data!$E$12:$E$75<>"",IF(ISNA(MATCH(Data!$E$12:$E$75,$U$52:U52,0)),MATCH(Data!$E$12:$E$75,Data!$E$12:$E$75,0)*{1,1})))),"")

also, i appreciate the macro, but i need a formula, thxs tho!

 

[Aladin Akyurek]

Hello Aladin,

I pasted the formula you provided in U52, and changed the $A$1:A1 to $U$52:U5 and dragged it down, but i get blanks?...any suggestions? thx u for your help.

array:

=IFERROR(INDEX(Data!$E$12:$E$75,MODE(IF(Data!$E$12:$E$75<>"",IF(ISNA(MATCH(Data!$E$12:$E$75,$U$52:U52,0)),MATCH(Data!$E$12:$E$75,Data!$E$12:$E$75,0)*{1,1})))),"")

also, i appreciate the macro, but i need a formula, thxs tho!

The formula must go in U53 and it must be confirmed with control+shift+enter, not just enter.