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

	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Company[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]User ID[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Unique ID[/COLOR]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​	Microsoft	10​	1​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​	Microsoft	10​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​	Apple	1​	1​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​	Apple	3​	1​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]6[/COLOR]​	Test1	2​	1​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]7[/COLOR]​	Test1	2​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]8[/COLOR]​	Test1	4​	1​

<tbody>
</tbody>

Sheet: Sheet1

<tbody>
</tbody>

 

[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,"")