Generate a random number

[mm82]

Hi guys,

If column A has the same value as the previous line and the sum of column B is less than 3 I want that group to have unique number in column C for that group.

Please see attached file.. hard to explain this

Br,
Mikael

Col A Col B Col C
A 1 ABC123
A 2 ABC123
A 3 ABC123

A 1 SSD111

B 1 ABC456
B 2 ABC456

C 1 DDD
D 1 DDD11
D 2 DDD11
D 3 DDD11
help.xlsxhelp.xlsx
D 1 TEST1
D 2 TEST1
D 3 TEST1

D 1 TEST2
D 2 TEST2
D 3 TEST2

D 1 TEST33
D 2 TEST33

[AliGW]

What happens if there are four or more?

In B3 copied down:

=COUNTIFS(A$3:A3,A3,C$3:C3,C3)

[mm82]

There is a file attached already.

help.xlsx

[AliGW]

Yes, I found it - wasn't very obvious!

See post #2.

[mm82]

Hi,

If there is more than three, then a new random number should be generated for the new dataset. That is what I tried to illustrate.. but hard to explain. Sorry.

If is the reference in column C that I want to create.

So if Column A is the same value like it for line 3-5 and the count is less than 4 I want a unique number generated for those lines. In the example that is ABC123.

Customer Counter Reference
A 1 ABC123
A 2 ABC123
A 3 ABC123
A 1 SSD111
B 1 ABC456
B 2 ABC456
C 1 DDD
D 1 DDD11
D 2 DDD11
D 3 DDD11
D 1 TEST1
D 2 TEST1
D 3 TEST1
D 1 TEST2
D 2 TEST2
D 3 TEST2
D 1 TEST33
D 2 TEST33

[AliGW]

I don’t follow - sorry.

[mm82]

Ok. See the new file that I attached.

help2.xlsx

[Phuocam]

Try this in B12:

=IFERROR(VLOOKUP(A12,$A$11:B11,2,0),MAX($B$11:B11)+1)

[Gregb11]

Try this in cell C3 and copy down:
=IF($B3>$B2,$C2,RAND())
You don't mention how to generate the random number so I just put this in. It can be tailored to how you need it
EDIT: I said C3 above because I didn't see your second file.

[windknife]

Try this in C12.

Please Login or Register  to view this content.

copied down.

[mm82]

Thank you guys! This is just great!

[AliGW]

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

[windknife]

You are welcome.