Formula to identify first value with 1, others with 0

[braddewhat]

I need to add a column to count the number of orders. Report is sorted by order number.

For the first instance of the order, I need a value of 1. For lines with the same order number, I need a 0. Having the 1 or 0 will allow me to count the unique orders and display on a pivot table.

 

[BarryL]

say data is in B2 down.

in A2 put

=IF(COUNTIF($B$2:B2,B2)=1,1,0)

 

[FormR]

Report is sorted by order number.

Hi, if that's the case this might be sufficient and efficient.

Excel 2013
	A	B
1	Order #	Formula
2	1	1
3	1	0
4	1	0
5	2	1
6	2	0
7	3	1
8	3	0
9	3	0
10	4	1
11	4	0
12	4	0
13	4	0
14	5	1
15	5	0
16	6	1
17	6	0
18	6	0
Sheet1

Cell Formulas
Range	Formula
B2	=IF(A2=A1,0,1)

 

[dssJones]

Happy holidays:

I assumed your tracking column is A.
The first order will always be the first unique so A1 = 1 shouldn't cause any problems
All subsequent rows (I assumed 1000 rows) should examine column B on same row and compare it to B on previous row.

A1 =1
A2:A1000 = if(b2=b1,0,1)