Numbered List Of Unique Values

[xander1981]

Hello Everyone. I have been working a long tim on this and I actually think it may be one of those things that cannot be done by formula or Sub Routine. I hope I explain what I want to do properly ...

I need to itemise a list of values but I only want to go up a number when the cell value is unique when compared to the cells above ie a range. An exmple of what I need visually is..

Column1 | Column 2
1 | 3504
1 | 3504
2 | 6401
3 | 1205
1 | 3504
2 | 6401

So I need the formula to automate the numbers in Column 1 dependant on the value of Column 2.

Each number is given an item number starting from 1 of course. I guess it would be like a rowcount that ignores the duplicates.

I hope this makes sense any help is gratefully apprciated. Thanks

[Excel Fox]

Write 1 in A1, and in A2 use =IF(ISNUMBER(MATCH(B2,B$1:B1,0)),INDEX(A$1:A1,MATC H(B2,B$1:B1,0)),MAX(A$1:A1)+1) and drag down

[xander1981]

Thanks Admin. Think it needs a slight tweek as its not displaying the correct item number in colum A. I am attaching the source file. Can you take a look and advise. Many thanks Attachment 570

[xander1981]

Sorry I was being a fool. Forgot source file starts row 2 and I didnt adjust the forula. Now sorted, thanks very much for your help.

[xander1981]

Actually there is a problem. There is a circular ref

[Ingolf]

Actually there is a problem. There is a circular ref

Hi,

In Excel Fox formula is a typo but made from place where is formula, use this :

=IF(ISNUMBER(MATCH(B2,B$1:B1,0)),INDEX(A$1:A1,MATCH(B2,B$1:B1,0)),MAX(A$1:A1)+1)

Your date must start in B2 and in A2 use Excel Fox formula , for me works .

[xander1981]

Perfect! thanks guys. I get a little rubbush using multi functions within a formula.