Which way to skin this cat?

**inspireme** · 10-19-2005, 04:40 PM

Originally Posted by Domenic

Assuming that Columns A, B, and C contain your data, and the first row
contains your headers/labels, try...

D2, copied down:

=B2&IF(COUNTIF($B$2:B9,B2)>1,COUNTIF($B$2:B2,B2),"")

or

=B2&ROWS($D$2:D2)

Then, if you want to convert these to values...

1) Select your new column, Column D

2) Edit > Copy > Edit > Paste Special > Values > Ok

Looking at the first formula:

1. What value is "B9" and ","") supposed to be and explain why?

Thank again!

Best,
-JP

Domenic · 10-19-2005, 06:05 PM

In article <inspireme.1x63md_1129755915.1624@excelforum-nospam.com>,
inspireme <inspireme.1x63md_1129755915.1624@excelforum-nospam.com>
wrote:

> O.K. I a newbie at this...Let's start with the 2nd formula first...If
> I'm getting this right:
>
> 1. I should select a new column D and fill down that column with the
> actual formula.

Yes, enter the formula in D2 and copy down...

=B2&ROWS($D$2:D2)

If you enter the formula in another location, let's say G2, change
ROWS($D$2:D2) to ROWS($G$2:G2).

> 2. Go to Edit menu and copy the entire column D then paste special with
> values selected? I tried this, but I must be missing something here BIG
> TIME!

Yes, that's it. Select your data in Column D and then go through the
steps. If you're still having problems, post back.

> Now, looking at the first formula:
>
> 1. What value is "B9" and ","") supposed to be and explain why?

I was using the range in your example. I assumed A1:C9 contained your
data, and that the first row contains your headers/labels. Actually, B9
should be $B$9. Therefore the formula should be...

=B2&IF(COUNTIF($B$2:$B$9,B2)>1,COUNTIF($B$2:B2,B2),"")

Change the range $B$2:$B$9 according to the data/range contained in your
spreadsheet.

> I'm assuming when you said "D2, copied down:" that meant to copy one
> of the above formulas to D2 and "fill down" so I'd see the formula
> repeated down column D...right?

Right.

>Then I copy, paste special and it
> appears nothing happens?? Where are the new values supposed to appear?
> Is there any operator that should be selected in the "Paste Special"
> Menu?

Once you've entered the formula in Column D, here's what you do...

1) Select the data in your new column, Column D

2) With the data highlighted, select 'Edit' from the 'Menu' and then
'Copy'

3) Then select 'Edit' again, Paste Special, Values, and click Ok

Now the same column, Column D, will contain the actual values. It will
no longer contain the formulas. Post back if you need further help.