# Add a Value for first value in Column

I need a macro that would assign a 1 to the first value and a 0 to each additional value that is the same. IE

Apple  1
Apple  0
Apple  0
Orange 1
Banana 1
kiwi 1
Banana 0

Searching Column E and Put the value in Column N start at row 2.

Thanks,
Montrof
LVL 1
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Finance AnalystCommented:
No need for VBA

=IF(COUNTIF(\$E\$2:\$E2,\$E2)=0,1,0)

Copied down as far as required.

Thanks
Rob H
Excel & VBA ExpertCommented:
@Rob

If I am not wrong, tt should be like this.....

``````=IF(COUNTIF(\$E\$2:\$E2,\$E2)=1,1,0)
``````
Excel & VBA ExpertCommented:
If you really need a VBA solution, you may try this.....
``````Sub Put1ForUniqueItems()
Dim lr As Long
Dim Rng As Range, Cell As Range, chkRng As Range

lr = Cells(Rows.Count, "E").End(xlUp).Row
Set Rng = Range("E2:E" & lr)

For Each Cell In Rng
Set chkRng = Range(Cells(2, "E"), Cells(Cell.Row, "E"))
If WorksheetFunction.CountIf(chkRng, Cell) = 1 Then
Cells(Cell.Row, "N") = 1
Else
Cells(Cell.Row, "N") = 0
End If
Next Cell
End Sub
``````

Experts Exchange Solution brought to you by