Identify every first unique ID with a 1

[Speeddragon]

I use a report that's delivered to me every week to analyse some data.
The I copy and paste the report in a tab I called 'source data' in my excel file every week, and in an other tab I make some calculations and prepare some data for a pivot table.
This way I just can copy paste the updated data, refresh the file and I have a fresh up to date pivot table with the data I would like to know

But there is one thing I just can't get done, I think the picture says all I need.

I need column C to put a 1 at every first unique user ID for a certain company (dummy data).
That way I will be able to let my pivot table sum al the 1's for every company, and that way I will know how many unique User ID's every company had.

For example, the pivot table will count the 1's from Test1 and give me 2.

The thing is that I can't seem to find a way to have only the first time a UserId appears on a row for a certain company to give me a 1.


It's important to have it this way so I can paste the new data every week, and have all my data refreshed in a minute. Doing it manually with a dupplicate finder isn't really an option.
A few steps in between with a few extra columns won't be a problem though.

 

[Speeddragon]

https://ibb.co/mAQgCe

Somehow I can't get this picture uploaded.

 

[Speeddragon]

Unknown 64 bit


[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[TH][/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​

[/TH]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​

[/TD]
[TD="bgcolor: #4472C4"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Company[/COLOR][/TD]
[TD="bgcolor: #4472C4"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]User ID[/COLOR][/TD]
[TD="bgcolor: #4472C4"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Unique ID[/COLOR][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​

[/TD]
[TD="bgcolor: #D9E1F2"]Microsoft[/TD]
[TD="bgcolor: #D9E1F2"]

10​

[/TD]
[TD="bgcolor: #D9E1F2"]

1​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​

[/TD]
[TD]Microsoft[/TD]
[TD]

10​

[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​

[/TD]
[TD="bgcolor: #D9E1F2"]Apple[/TD]
[TD="bgcolor: #D9E1F2"]

1​

[/TD]
[TD="bgcolor: #D9E1F2"]

1​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​

[/TD]
[TD]Apple[/TD]
[TD]

3​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]6[/COLOR]​

[/TD]
[TD="bgcolor: #D9E1F2"]Test1[/TD]
[TD="bgcolor: #D9E1F2"]

2​

[/TD]
[TD="bgcolor: #D9E1F2"]

1​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]7[/COLOR]​

[/TD]
[TD]Test1[/TD]
[TD]

2​

[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]8[/COLOR]​

[/TD]
[TD="bgcolor: #D9E1F2"]Test1[/TD]
[TD="bgcolor: #D9E1F2"]

4​

[/TD]
[TD="bgcolor: #D9E1F2"]

1​

[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Sheet1[/TD]
[/TR]
</tbody>[/TABLE]

 

[mrshl9898]

In C2 =IF(COUNTIFS(A$2:A2,A2,B$2:B2,B2)=1,1,"")

 

[Speeddragon]

In C2 =IF(COUNTIFS(A$2:A2,A2,B$2:B2,B2)=1,1,"")

Well it works, I tried to read it and understand how it works but I don't understand why it's working (something i would like to know).

I can't get a prober understanding of what the $ does here.

 

[Speeddragon]

I can't find a edit button for my last post, so sorry for making a new post again.

It even works when row A isn't alphabatecically ordered so that's super nice.
But I keep failing to understand what the formula does exactly..


First time i get a adjacent cell error, but it's working fine.

 

[mrshl9898]

To try and explain, the countifs is counting the row you are on and above rows for the combination of identical values in Columns A and B, the first row this = 1, second = 2 etc. IF that = 1 then it is the first occurrence so it places a 1.

The $ locks in a value $A1 locks in column A, A$1 locks in the first row, $A$1 locks in cell A1. It's used here so it looks at A2:A(whatever row you are on)

 

[Rick Rothstein]

Assuming your data is grouped by company name and then user ID as shown, then I think this formula placed in cell C2 and copied down should also work...

=IF(A2&CHAR(1)&B2<>A1&CHAR(1)&B1,1,"")