Unique formula

M

Mr2017

Well-known Member
Joined
Nov 28, 2016
Messages
644
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi

I've been given a set of data where I need to get a list of unique users from data where the usernames are repeated multiple times.

A Pivot table will count the number of rows that each data set has, but not the unique number of users.

There are 4 criteria that need to be met for a user to be counted - City, Status (this must be 'Active'), Distributor and Platform.

In the sample list below, the combination 'Chicago, Active, British Broadcasting News, 3' would return just 2 unique users (John Regis and Jane Fonda).

Whilst 'New York, Active, British Broadcasting News, 3' would return just 1 user (Jack Grey).

Does anyone know the easiest way to do this? Is it possible with a Pivot Table?

TIA

CityStatusDistributorPlatformContact: Full Name
ChicagoActiveBritish Broadcasting News
3​
John Regis
ChicagoActiveBritish Broadcasting News
3​
John Regis
ChicagoActiveBritish Broadcasting News
3​
John Regis
ChicagoActiveBritish Broadcasting News
3​
John Regis
ChicagoActiveBritish Broadcasting News
3​
Jane Fonda
ChicagoActiveBritish Broadcasting News
3​
Jane Fonda
ChicagoActiveBritish Broadcasting News
3​
Jane Fonda
ChicagoActiveBritish Broadcasting News
3​
Jane Fonda
ChicagoActiveBritish Broadcasting News
3​
Jane Fonda
ChicagoActiveBritish Broadcasting News
3​
Jane Fonda
ChicagoActiveBritish Broadcasting News
3​
John Regis
ChicagoInactiveBritish Broadcasting News
3​
John Regis
ChicagoInactiveBritish Broadcasting News
3​
John Regis
ChicagoInactiveBritish Broadcasting News
3​
John Regis
ChicagoInactiveBritish Broadcasting News
3​
John Regis
ChicagoInactiveBritish Broadcasting News
3​
John Regis
ChicagoInactiveBritish Broadcasting News
3​
Jane Fonda
ChicagoInactiveBritish Broadcasting News
3​
Jane Fonda
ChicagoInactiveBritish Broadcasting News
3​
Jane Fonda
ChicagoInactiveBritish Broadcasting News
3​
Charles Windsor
ChicagoInactiveBritish Broadcasting News
3​
Charles Windsor
ChicagoInactiveBritish Broadcasting News
3​
Charles Windsor

​
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
If you have power pivot, it's simple as you can use a Distinct Count function.
 
Upvote 0
Thanks - I hadn't used it before, but I've downloaded the add-in and I'll watch a video to see how it can be done.

Thanks again.
 
Upvote 0
Your profile says you have 2016 and 365, both of which have Power Pivot automatically. You don't need to download it, and if you do, it will be the wrong version. ;)
 
Upvote 0
Yes, I’ve got both versions.

I bought 2016 a few months ago, but I got 365 recently.

The Power Pivot tab didn’t actually show up in the ribbon, hence the reason I had to add it.

But I appreciate the suggestion for using it - I‘ll definitely look into it!
 
Upvote 0
Mr2017 said:
I need to get a list of unique users from data where the usernames are repeated multiple times.

A Pivot table will count the number of rows that each data set has, but not the unique number of users.

There are 4 criteria that need to be met for a user to be counted
Click to expand...
I am unsure whether you are asking for a list or a count?

IF you have the new dynamic array formulas in your Excel 365 then you can get a list by formulas as follows.

I don't know how you want the results laid out but here is one way.
Formula is entered in G1 only. The other values will 'spill' automatically into the other required cells

20 09 02.xlsm
ABCDEFGHIJK
1CityStatusDistributorPlatformContact: Full NameCityStatusDistributorPlatformContact: Full Name
2ChicagoActiveBritish Broadcasting News3John RegisChicagoActiveBritish Broadcasting News3John Regis
3ChicagoActiveBritish Broadcasting News3John RegisChicagoActiveBritish Broadcasting News3Jane Fonda
4ChicagoActiveBritish Broadcasting News3John RegisChicagoInactiveBritish Broadcasting News3John Regis
5ChicagoActiveBritish Broadcasting News3John RegisChicagoInactiveBritish Broadcasting News3Jane Fonda
6ChicagoActiveBritish Broadcasting News3Jane FondaChicagoInactiveBritish Broadcasting News3Charles Windsor
7ChicagoActiveBritish Broadcasting News3Jane Fonda
8ChicagoActiveBritish Broadcasting News3Jane Fonda
9ChicagoActiveBritish Broadcasting News3Jane Fonda
10ChicagoActiveBritish Broadcasting News3Jane Fonda
11ChicagoActiveBritish Broadcasting News3Jane Fonda
12ChicagoActiveBritish Broadcasting News3John Regis
13ChicagoInactiveBritish Broadcasting News3John Regis
14ChicagoInactiveBritish Broadcasting News3John Regis
15ChicagoInactiveBritish Broadcasting News3John Regis
16ChicagoInactiveBritish Broadcasting News3John Regis
17ChicagoInactiveBritish Broadcasting News3John Regis
18ChicagoInactiveBritish Broadcasting News3Jane Fonda
19ChicagoInactiveBritish Broadcasting News3Jane Fonda
20ChicagoInactiveBritish Broadcasting News3Jane Fonda
21ChicagoInactiveBritish Broadcasting News3Charles Windsor
22ChicagoInactiveBritish Broadcasting News3Charles Windsor
23ChicagoInactiveBritish Broadcasting News3Charles Windsor
List 1
Cell Formulas
RangeFormula
G1:K6G1=UNIQUE(A1:E23,0)
Dynamic array formulas.



Perhaps you want it like this?
Formula in G1 will 'spill' the other values to column J and down as many rows as required.
Formula in K2 needs to b copied down as far as you might ever need.

20 09 02.xlsm
ABCDEFGHIJKLM
1CityStatusDistributorPlatformContact: Full NameCityStatusDistributorPlatformNames
2ChicagoActiveBritish Broadcasting News3John RegisChicagoActiveBritish Broadcasting News3John RegisJane Fonda
3ChicagoActiveBritish Broadcasting News3John RegisChicagoInactiveBritish Broadcasting News3John RegisJane FondaCharles Windsor
4ChicagoActiveBritish Broadcasting News3John Regis 
5ChicagoActiveBritish Broadcasting News3John Regis 
6ChicagoActiveBritish Broadcasting News3Jane Fonda 
7ChicagoActiveBritish Broadcasting News3Jane Fonda 
8ChicagoActiveBritish Broadcasting News3Jane Fonda 
9ChicagoActiveBritish Broadcasting News3Jane Fonda
10ChicagoActiveBritish Broadcasting News3Jane Fonda
11ChicagoActiveBritish Broadcasting News3Jane Fonda
12ChicagoActiveBritish Broadcasting News3John Regis
13ChicagoInactiveBritish Broadcasting News3John Regis
14ChicagoInactiveBritish Broadcasting News3John Regis
15ChicagoInactiveBritish Broadcasting News3John Regis
16ChicagoInactiveBritish Broadcasting News3John Regis
17ChicagoInactiveBritish Broadcasting News3John Regis
18ChicagoInactiveBritish Broadcasting News3Jane Fonda
19ChicagoInactiveBritish Broadcasting News3Jane Fonda
20ChicagoInactiveBritish Broadcasting News3Jane Fonda
21ChicagoInactiveBritish Broadcasting News3Charles Windsor
22ChicagoInactiveBritish Broadcasting News3Charles Windsor
23ChicagoInactiveBritish Broadcasting News3Charles Windsor
24
List 2
Cell Formulas
RangeFormula
G1:J3G1=UNIQUE(A1:D23,0)
K2:L2,K4:K8,K3:M3K2=TRANSPOSE(UNIQUE(FILTER(E$1:E$23,(A$1:A$23=G2)*(B$1:B$23=H2)*(C$1:C$23=I2)*(D$1:D$23=J2),"")))
Dynamic array formulas.



If it is just a count that you want, the K2 formula could be this

20 09 02.xlsm
ABCDEFGHIJK
1CityStatusDistributorPlatformContact: Full NameCityStatusDistributorPlatformCount
2ChicagoActiveBritish Broadcasting News3John RegisChicagoActiveBritish Broadcasting News32
3ChicagoActiveBritish Broadcasting News3John RegisChicagoInactiveBritish Broadcasting News33
4ChicagoActiveBritish Broadcasting News3John Regis 
5ChicagoActiveBritish Broadcasting News3John Regis 
6ChicagoActiveBritish Broadcasting News3Jane Fonda 
7ChicagoActiveBritish Broadcasting News3Jane Fonda 
8ChicagoActiveBritish Broadcasting News3Jane Fonda 
9ChicagoActiveBritish Broadcasting News3Jane Fonda
10ChicagoActiveBritish Broadcasting News3Jane Fonda
11ChicagoActiveBritish Broadcasting News3Jane Fonda
12ChicagoActiveBritish Broadcasting News3John Regis
13ChicagoInactiveBritish Broadcasting News3John Regis
14ChicagoInactiveBritish Broadcasting News3John Regis
15ChicagoInactiveBritish Broadcasting News3John Regis
16ChicagoInactiveBritish Broadcasting News3John Regis
17ChicagoInactiveBritish Broadcasting News3John Regis
18ChicagoInactiveBritish Broadcasting News3Jane Fonda
19ChicagoInactiveBritish Broadcasting News3Jane Fonda
20ChicagoInactiveBritish Broadcasting News3Jane Fonda
21ChicagoInactiveBritish Broadcasting News3Charles Windsor
22ChicagoInactiveBritish Broadcasting News3Charles Windsor
23ChicagoInactiveBritish Broadcasting News3Charles Windsor
24
Count 1
Cell Formulas
RangeFormula
G1:J3G1=UNIQUE(A1:D23,0)
K2:K8K2=IF(G2="","",ROWS(UNIQUE(FILTER(E$1:E$23,(A$1:A$23=G2)*(B$1:B$23=H2)*(C$1:C$23=I2)*(D$1:D$23=J2)))))
Dynamic array formulas.
 
Upvote 0
Hi Peter

Thanks for the detailed response.

I'll take a look at the formulae then get back to you.

Just need to finish something urgent!
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question
Excel Table Representation Query: 2 columns in the data set with dates which needs to be aligned to get right result.
Replies
1
Views
411
GraH
GraH
U
  • Question
Adding formula to Pivot Table when calculated field not available
Replies
6
Views
3K
Ucubia
U
T
  • Question
Count Unique Values
Replies
1
Views
431
awoohaw
awoohaw
R
  • Solved
Advanced Filter only works if the worksheet is active
Replies
2
Views
531
Rfriend
R
ajjava
  • Question
Procedure to find MAX VALUE in a SPECIFIC pivot table value field, in the case of MULTIPLE pivot table value fields
Replies
0
Views
826
ajjava
ajjava

Forum statistics

Threads
1,214,834
Messages
6,121,873
Members
449,056
Latest member
ruhulaminappu

Share this page

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