# show value on first number, zero for all additional duplicate numbers

#### richardsmith

##### New Member
hi,

this has been doing my head in for a couple of days now... hopefully a clever excel whizz will know the answer....

I have a column that a 'job number' is entered into, this is alphanumerical.

where the 'job number' is unique I want a value to be associated with it.

where the 'job number' is not unique, I need the first entry to have a value associated but not the additional ones.

I can only get it to show zero value if the 'job number' appears more than once, which I dont want.

ie:

job value

12345 512 &#60;-keep this value, even if the same'job number' is entered again

12345 0

12346 303

I have date field too, if this helps anyones formaula...

Richard

I am assuming your Job Number is in a named range called Job

and that

Job Number goes from B2..B100

and associated values are in D2:D100

Try this is C2 and copy down

=+IF(MATCH(B2,Job,-1)=ROW()-1,D2,0)

In here if the first occurence of the Job_No in the list Job equals the Row number -1 (allows for the titles), get a value from D2

You could change the D2 Reference to do a Lookup of what the value corresponding to B2

thanks, it works.

however I am running a macro to insert a new line with my formulas on it, when I insert a new line with this formula it goes to the 0 value before a second job number has been inserted....

my worksheet is a bit busy, would it be easier to email it so you can see what I mean?

ihuitson @ gmail dot com

thank you. incoming....

I have found out a few things, like I'm not the best excel user...