Is this possible to do?

  • Thread starter Thread starter sassykeon
  • Start date Start date
S

sassykeon

Hi,

I would greatly appreciate it if you can help me with the following:

I have a list of about 10,000 names in an Excel spreadsheet.
All the names are not unique, and thus some names are repeated
up to 5 times.

Is there a way to use a formula to transfer those 10k names
from Column A in an excel spreadsheet to column B, while
editing the "duplicate" names so that all names are unique?

For example, my list has about 4 people with names of "Kevin
Johnson". Is there a way so that when all the names are transferred to
column B, the 4 "Kevin Johnson" gets changed?
The change would be something like this:
The first instance of "Kevin Johnson" would remain the same, but
the remaining three would be changed by adding the the number
"1" to the end of the 2nd "Kevin Johnson" and then the number "2" to
the third "Kevin Johnson" and then the number "3"
to the fourth and final "Kevin Johnson".

Thus the list in column B would still have a list of 10k names
in the same order as column A,
but the duplicates of Kevin Johnson would know be unique becuase they'd
be:

Kevin Johnson
Kevin Johnson1
Kevin Johnson2
Kevin Johnson3

Is it possible to do this with a formula?
 
Sayyskoen,

Assuming your lsit starts in row 1 , column A, place the followin
formula in in B1.

=if(COUNTIF($b$1:$b$50000,b1)>1, COUNTIF($b$1:b1,b1),"")

and then copy the formula down for the length of the names list.

Then in cell c1 insert the formula

=a1&b1
and then copy the formula down for the length of the names list.

Then select tall the values in column C and copy them and paste specia
- values to your new worksheet.

Basically it counts the number of duplicate occurences starting fro
the beginning of the list and appends the count if > 1
 
Kieran said:
*Sayyskoen,

Assuming your lsit starts in row 1 , column A, place the followin
formula in in B1.

=if(COUNTIF($b$1:$b$50000,b1)>1, COUNTIF($b$1:b1,b1),"")

and then copy the formula down for the length of the names list.

Then in cell c1 insert the formula

=a1&b1
and then copy the formula down for the length of the names list.

Then select tall the values in column C and copy them and past
special - values to your new worksheet.

Basically it counts the number of duplicate occurences starting fro
the beginning of the list and appends the count if > 1. *
Click to expand...

Hello Kieran,

I did exactly as you said, but the formulas I inputted in B1 and C1 an
down those columns don't seem to do anything. They just
show "0's" down the columns B and C. Is it suppose to do that?

(P.S. Column A consists of the data (the names) starting in A1.
 
Sorry,

my mistake,
try =IF(COUNTIF($A$1:$A$50000,A1)>1, COUNTIF($A$1:A1,A1),"") in cell B1
and copy down.
 
Kieran, thank you very much! It now works. :)

Jumbo, thanks for the link, I'll be sure to check out the articles on
that site.
 
sassy

Kieran had a typo in his formula.

=if(COUNTIF($b$1:$b$50000,b1)>1, COUNTIF($b$1:b1,b1),"")

Change all the b's to a's and enter the formula in B1 then drag down Column B.

The formula for Column C is OK.

Gord Dibben Excel MVP
 
Kieran said:
Sayyskoen,

Assuming your lsit starts in row 1 , column A, place the following
formula in in B1.

=if(COUNTIF($b$1:$b$50000,b1)>1, COUNTIF($b$1:b1,b1),"")

and then copy the formula down for the length of the names list.

Then in cell c1 insert the formula

=a1&b1
and then copy the formula down for the length of the names list.

Then select tall the values in column C and copy them and paste
special - values to your new worksheet.

*Basically it counts the number of duplicate occurences starting from
the beginning of the list and appends the count if > 1. *
Click to expand...


Hi,
what if I'd like it to append the count if >1, but also having a space
before the number? Like instead of appending just the number "1", how
about " 1"? (Notice the blank space)
 
Hmm, I still can't figure out how to output the "blank space" after th
number. Anyone got any ideas
 
Hi Gord,

You're right,

=A1&" "&B1

will give a space, but it'll give a space to all names even if
there if the name is not repeated. I'm only looking to have
a space between the name and the number, something like
"Kevin Johnson 1". But if the name is not repeated, I do not
want the name to have a space after like "Kevin James
 
I don't know functions that work with text very well but if perhaps yo
could find something suitable to isolate a number connected to a name
then perhaps use IF and ISNUMBER to remove the bit you don't want.

Try downloading this file to get the Excel Function Dictionary. It ha
some very good examples of working with text functions, perhaps FIN
and RIGHT might be come in handy.

http://homepage.ntlworld.com/noneley
 
Hi sassykeon!

Is this what you want?

=IF(B2="",A2,A2&" "&B2)

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Norman said:
*Hi sassykeon!

Is this what you want?

=IF(B2="",A2,A2&" "&B2)

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and
Arguments)
available free to good homes. *
Click to expand...

Shame on me, I should have read the entire thread. :(

Mr. Harker's solution is perfect.

Just a question, why doesn't anyone use ISBLANK? (Even though "" will
get the same result)
 
Hi Andrew!

=ISBLANK(B2) will not return TRUE if the cell contains a formula that
returns "".

But =B2="" will return TRUE if the cell is blank or contains a formula
that returns "".

In some instances we might be after a *truly* blank cell.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Hi sassykeon!

Pleased to hear that we got there in the end.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Hi Norman,
*
=ISBLANK(B2) will not return TRUE if the cell contains a formul
that
returns "".*
Click to expand...
Now I know why I was having problems with ISBLANK sometimes.

Thanks for enlightening me. ;)

Andre
 
Back
Top