How to Create a Unique Number

[kimberly090]

Hi,

I wish to create condition formatting for my customer:

This will be my data:

I wish to create a condition formatting for the specific customer called Jack.

For example,

1. If Ticket number for Jack will be 123, than the ticket number cannot be repeated on others customer. If others customer are using the same ticket number, than the customer named will be highlighted.

2. For others customer, Kurt and Kimberly or others new entry customer, they ticket number can be use repeatedly.

3. The ticket number shall not repeat again once the specific customer Jack have appear on the top. For example in B10, the the ticket number should be key in with other number that is not same as the top Jack's ticket number and others customer ticket number. It should be a unique number like 789. If it repeat, the cell will be highlighted.

Thank you.

 

[kvsrinivasamurthy]

Use this for validation of cell B2 , then copy validation to other cells.

Validation--> Custom

=IF(COUNTIF($A$2:$A2,$A2)=1,COUNTIF($B$2:$B2,$B2)=1,COUNTIF($A$2:$A2,$A2)=COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2))

 

[Peter_SSs]

For example,

1. If Ticket number for Jack will be 123, than the ticket number cannot be repeated on others customer. If others customer are using the same ticket number, than the customer named will be highlighted.

2. For others customer, Kurt and Kimberly or others new entry customer, they ticket number can be use repeatedly.

3. The ticket number shall not repeat again once the specific customer Jack have appear on the top. For example in B10, the the ticket number should be key in with other number that is not same as the top Jack's ticket number and others customer ticket number. It should be a unique number like 789. If it repeat, the cell will be highlighted.

My understanding is that if B10 in your screen shot was 789 then row 10 would not be highlighted but row 6 would be because Kimberly has used Jack's numnber (123).

If B10 was 789 and B6 was also 789 would row 6 be highlighted because Kimberly has used a number of Jack's or would a Kimberly|789 row only be highlighted if it is after a Jack|789 row?

 

[kvsrinivasamurthy]

This also will do,

=COUNTIF($A$2:$A2,$A2)=COUNTIF($B$2:$B2,$B2)

 

[kimberly090]

My understanding is that if B10 in your screen shot was 789 then row 10 would not be highlighted but row 6 would be because Kimberly has used Jack's numnber (123).

If B10 was 789 and B6 was also 789 would row 6 be highlighted because Kimberly has used a number of Jack's or would a Kimberly|789 row only be highlighted if it is after a Jack|789 row?

Hi, Kimberly will be only highlighted after a Jack|789 row.
Jack will be highlighted when on the top of the list contain of Jack|789.

 

[kimberly090]

Use this for validation of cell B2 , then copy validation to other cells.

Validation--> Custom

=IF(COUNTIF($A$2:$A2,$A2)=1,COUNTIF($B$2:$B2,$B2)=1,COUNTIF($A$2:$A2,$A2)=COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2))

Hi, I have try the rule, but for the customer besides than Jack, they can share the same ticket number.
Than for Jack at below will be highlighted if it share the same ticket number with Jack that appear on the top.
For example on Jack (B2) have ticket number on 123, than on Jack,B10 ( which Jack appear after different customer) it cannot share the same ticket number with Jack(B2) which is 123, it must use a unique ticket number like 987.

 

[kimberly090]

This also will do,

=COUNTIF($A$2:$A2,$A2)=COUNTIF($B$2:$B2,$B2)

Hi, I have tried this also, Jack should not share the same ticket number with others customer. If they contain same customer number, it will be highlighted or prompt message.
Then, Jack ticket number should not be the same with the number with Jack on B2:B4 after few customer.

 

[kimberly090]

I wish my result will look something like this:

 

[Peter_SSs]

Try selecting from A2:B??(end of data) and apply the Conditional Formatting rules shown.

Excel Workbook
	A	B
1	Name	Ticket
2	Jack	123
3	Jack	123
4	Jack	123
5	Kimberly	456
6	Kimberly	123
7	Kimberly	456
8	Kurt	456
9	Kurt	587
10	Jack	123
11	Jack	789
CF Jack

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2	1. / Formula is =AND($A2<>"Jack",COUNTIFS($A$1:$A1,"Jack",$B$1:$B1,$B2))	Abc
A2	2. / Formula is =AND($A2="Jack",$A1&$B1<>$A2&$B2,COUNTIFS($A$1:$A1,"Jack",$B$1:$B1,$B2))	Abc

 

[kimberly090]

Try selecting from A2:B??(end of data) and apply the Conditional Formatting rules shown.

CF Jack

	A	B
1	Name	Ticket
2	Jack	123
3	Jack	123
4	Jack	123
5	Kimberly	456
6	Kimberly	123
7	Kimberly	456
8	Kurt	456
9	Kurt	587
10	Jack	123
11	Jack	789

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:70px;"><col style="width:56px;"></colgroup><tbody>
</tbody>

Conditional formatting

Cell Nr.: / Condition Format A2 1. / Formula is =AND($A2<>"Jack",COUNTIFS($A$1:$A1,"Jack",$B$1:$B1,$B2)) Abc A2 2. / Formula is =AND($A2="Jack",$A1&$B1<>$A2&$B2,COUNTIFS($A$1:$A1,"Jack",$B$1:$B1,$B2)) Abc <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Hi, I have try~ yes, it is almost match what I want.
But after I try the result, I found out that if on Kimberly(B5) is 456, than on Jack(B10) if I type in 456, it will not be highlighted.
The result should be Jack cannot contain any ticket number which is same like others customer. So which meant, on Jack(B10) should be highlighted due to it contain same ticket number with Kimberly*B5)