• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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 <-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...
 

Hui

Excel Ninja
Staff member
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
 

richardsmith

New Member
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?
 
Top