Distinct Values in a Column

AnetShe

New Member
Joined
Apr 27, 2017
Messages
20
I can't seem to figure this out or find an already posted solution - any help would be amazing!
My dilemma - if the names below were in a column (approx 1,500 rows) I need a column beside them showing a 1 or a 0 ONLY once to note if the value is unique.
Is there a formula I can use for this?
The farthest I have gotten is using this formula but it only checks the rows below the lookup value and doesn't check the rows above it.
=IFERROR(IF(K3=VLOOKUP(K3,K4:K$2264,1,FALSE),0),1)

Example columns with last value showing the 1 and 0s that I need:

Region - Partner Name - Sales - Unique Identifier
Canada - James - $10 - 1
Canada - Rob - $5 - 1
Canada - James - $5 - 0
Canada - James - $25- 0
Canada - Stephanie - $10 - 1
Canada - Rob - $10 - 0
Canada - Rob - $5 - 0
Canada - Stephanie - $10 - 0

The reason this is needed is that I need to create a pivot table showing a count of number of unique names without actually showing the names in the pivot table.

Example of the pivot values that are needed from the example values above:

Region - # of Unique Partners - Sales
Canada - 3 - $80

Let me know if this makes sense and I am open to any other suggestions to achieve what is needed.
THANK YOU!
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Re: Help! Distinct Values in a Column

Insert PivotTable with Add to DataModel then select Distinct Count for Partner Name

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td]Region [/td][td] Partner Name [/td][td] Sales [/td][td] Unique Identifier[/td][td][/td][td]PivotTable[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Canada [/td][td] James [/td][td]
10​
[/td][td]
1​
[/td][td][/td][td=bgcolor:#DDEBF7]Region[/td][td=bgcolor:#DDEBF7]Distinct Count of Partner Name[/td][td=bgcolor:#DDEBF7]Sum of Sales[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Canada [/td][td] Rob [/td][td]
5​
[/td][td]
1​
[/td][td][/td][td]Canada[/td][td]
3​
[/td][td]
80​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Canada [/td][td] James [/td][td]
5​
[/td][td]
0​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Canada [/td][td] James [/td][td]
25​
[/td][td]
0​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Canada [/td][td] Stephanie [/td][td]
10​
[/td][td]
1​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Canada [/td][td] Rob [/td][td]
10​
[/td][td]
0​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Canada [/td][td] Rob [/td][td]
5​
[/td][td]
0​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Canada [/td][td] Stephanie [/td][td]
10​
[/td][td]
0​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]
 
Last edited:
Upvote 0
Re: Help! Distinct Values in a Column

With a formula


Excel 2013/2016
JKLM
2RegionPartner NameSalesUnique Identifier
3CanadaJames$101
4CanadaRob$51
5CanadaJames$50
6CanadaJames$250
7CanadaStephanie$101
8CanadaRob$100
9CanadaRob$50
10CanadaStephanie$100
Archive
Cell Formulas
RangeFormula
M3=IF(COUNTIF(K$3:K3,K3)=1,1,0)
 
Upvote 0
Re: Help! Distinct Values in a Column

You're welcome
 
Upvote 0
Re: Help! Distinct Values in a Column

Thank you also for this - the data model & distinct count was a fantastic new learning!
 
Upvote 0
Re: Help! Distinct Values in a Column

Another formula that might come in handy if you were dealing with big data sets:

=IF(MATCH(K3,$K$3:$K$1500,0)=ROW()-1,1,0)
 
Upvote 0

Forum statistics

Threads
1,223,908
Messages
6,175,306
Members
452,633
Latest member
DougMo

We've detected that you are using an adblocker.

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

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

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

Disable uBlock

Follow these easy steps to disable uBlock

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