Formula Error - Assign number to unique values.

  • Thread starter Thread starter J.W. Aldridge
  • Start date Start date
J

J.W. Aldridge

I have a list of codes in F$19:F$1500.
I was attempting to use the following code to assign a number to each
unique value in the list and repeat the previously assigned number if
it is duplicated.

This works for the first 15-20 numbers, but returns an error (#n/a)
afterwards as if the code stopped working.

I have tried EVERYTHING... Same result each time.

=IF(COUNTIF($F$18:F19,F19)>1,VLOOKUP(F19,F$18:G18,2,FALSE),MAX($G
$18:G18)+1)


Any clues?
 
My guess...

=countif() treats everything as text.

So the text string ="1" (or '1) will be counted as a match as well as the number
1.

But =vlookup() needs to have an exact match. The string ="1" and the number =1
are not equal in =vlookup()'s eyes.

I'd do my best to make sure that the values in column F are all real numbers or
all real text.

And it's not enough to just change the format of the cells from Text to General
(or from General to Text).

You actually have to change the values.

When I want to convert a text string that's masquerading as a number to a real
number, I'll use this:

Select an empty cell
edit|copy
Select the offending range
Edit|paste special|check add and values
 
J.W. Aldridge said:
I have a list of codes in F$19:F$1500.
I was attempting to use the following code to assign a number to each
unique value in the list and repeat the previously assigned number if
it is duplicated.

This works for the first 15-20 numbers, but returns an error (#n/a)
afterwards as if the code stopped working.

I have tried EVERYTHING... Same result each time.

=IF(COUNTIF($F$18:F19,F19)>1,VLOOKUP(F19,F$18:G18,2,FALSE),MAX($G
$18:G18)+1)


Any clues?
Click to expand...

Well, it works for me with very simple data, as follows
a b c d e f g g h i g h a b c d e f g g h i g h a b c d e f g g h i g
h j k l m n o p q r s t u v w x y z
1 2 3 4 5 6 7 7 8 9 7 8 1 2 3 4 5 6 7 7 8 9 7 8 1 2 3 4 5 6 7 7 8 9 7
8 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26


I've transposed the data so it doesn't go over too many lines, and hopefully
it will wrap gracefully. Do you want to give us a sample of the data you are
using?

By the way, I continued this down over another 450-500 lines, ending up
with:
aaaaaaaae 213
aaaaaaaaf 214
aaaaaaaag 215
aaaaaaaag 215


So, it seems to work for longer strings and larger than 15-20 numbers.

BTW what version of excel?
 
The string consist of codes (containing a various number of letters
and numbers):

AAAAAA12345AAAA
AAAA45644ASDA

I tried using the paste special method (recommended above) but
everything turned to errors.

I am using MS Excel 2000.
 
Back
Top