How to Autonumber Rows but Retain Same Number for Duplicate Entrie

[Caledoniain]

I have a very large Excel 2007 database (over 13,000 entries) and I need to
transfer the data into SPSS. In order to do so, I need to convert one column
of text-labelled data (people's names) into numerical identifiers. However,
some people are deliberately included on more than one row. What I want to do
is find a way of allocating a unique numerical identifier to each unique
entry, NOT to each row of data. In other words, I want each person to have
their own ID which will be the same regardless of whether they appear in row
1, row 5000 or row 12,000 of my dataset.

Can somebody please explain how I go about doing this, please? Many thanks!

 

[Mike H]

Hi,

Lets assume these names are in A1 down, the first number will be 1 (or
whatever start number you choose) so put this in B1. Then put this formula in
B2. Double click the fill handle and it will fill down and number your names

=IF(COUNTIF($A$1:A2,A2)=1,MAX($B$1:B1)+1,LOOKUP(2,1/($A$1:A1=A2),$B$1:B1))

Mike

 

[Caledoniain]

Hi Mike,

First of all, many thanks for the very quick reply - it's much appreciated!

Unfortunately, this hasn't worked: I've done exactly as you recommended
(using A1 as the first name, using B1 as the first manually input ID number
and B2 as the first formula-generated ID number), but all I get is a column
full of the formula and not a column full of ID numbers.

I'm really not hugely experienced at dealing with Excel's formula functions,
though, so if there's anything blindingly obvious that you assumed I would
already have done, it's probably a good idea to mention it!

Thanks again - look forward to hearing back.

 

[Mike H]

Hi,

Start again. Delete all entries in column B and re-format the entire column
as General. Put a 1 in b1 and the formula in b2 and try again. The previous
cells were probably formatted as text.

Mike

 

[Caledoniain]

Hello again Mike,

That seems to be working, but even though I have a fast processor and 4GB
RAM, it's taking a while to populate all 13,000 rows. I'll let it do its
stuff and will then check the consistency of its work before reporting back
again.

In the meantime, can you answer me this: once I've run the formula and I am
happy with the results, will adding rows or columns corrupt the formula, or
will it autoupdate? For example, if I insert a new row 1 in order to provide
column headings, will this screw up the formula in column B, or will Excel be
savvy enough to understand what's going on, and alter its range automatically
(from starting in row 2 to starting in row 3)?

Thanks very much once again for your exceedingly prompt help!

 

[Mike H]

Hi,

I'm surprised that it's taking a long time to populate 13k rows, I just
tested it on my machine which is lower spec than your and it too 5 seconds
for 13k rows so I guess there's other recalculation going on as well,

Inserting a header row is fine.

Mike