Microsoft Excel

[brewskieman21]

Hi I was wondering if anybody might know a formula or some way to make a problem I have easier for me. I'll try to explain it as best as I can. I have a list of products codes that I'm copying from another Excel file into a brand new Excel file and then importing it into a a shopping cart program. Well some of the product codes are the same and so when I import it it gives me an error so I've been numbering them like this for example: (Original Product Code) 83A-3322-A4K to (New Product Code) 83A-3322-A4K_1 and 83A-3322-A4K_2 and 83A-3322-A4K_3 and so on. Is there a formula or some other way to add just the _# at the end of the product codes? If anyone knows you would be such a life saver. Oh incase you're wondering I have no idea why some of the products codes are the same. If they were all different I wouldn't have to be typing it all in manually. Thanks for the help.

[James T]

Put them in a table.
Use data sort to get the field you are interested in in order.
Create a column with =exact(An,An-1), this tells you if two rows are the same.
Create a column with =if(1,An&"_#",An), this adds the _# based on duplication.
Copy the last column, paste special VALUES into the original column.
Delete the two formula columns.
Circuitous, but ... effective.

[brewskieman21]

Sorry I figured I wouldn't explain it very well. I want to add "_#" in order, regardless whether or not its the same product code. My fault I apologize for not clearifying what I meant. Example below:

A432-332AK-J_1
B432-332AK-J_2
C432-332AK-J_3
D432-332AK-J_4
A432-332AK-J_5
B432-332AK-J_6
D432-332AK-J_7


Thank you though for the fast response I really do appreciate it.

[James T]

Data in A:A, start at B1 with =A1&"_"&row() copy down.

[brewskieman21]

Ok, that didn't work. When I did that it added the product code from the previous cell and added the _# at the end. For instance it said PRODUCT CODE_#1. Let's see if I can add a sample of exactly what I have into here maybe it'll best explain what i need. Product Code starts on A1. Sorry I have been confusing to ya'll. Thanks for the help

PRODUCTCODE
06A-115-561-B_1
1J0-129-620_2
1H0-819-644-B_3
101-000-063-AA_4
8N0-615-301-A_5
8N0-698-151_6
8N0-698-151-A_7
1J0-615-601_8
1J0-698-451-F_9
06A-260-849-C_10
06A-903-315-E_11
06B-109-119-A_12
06B-109-243_13
06B-109-477_14
895-955-429-A_15
4B0-955-427_16
8D1-955-427_17
N-103-201-01_18
N-104-456-01_19
N-017-761-2_20
N-017-738-2_21
N-017-732-2_22
1J0-201-511-A_23
N-020-353-5_24
PKTBATW_25
PKATWTUNEUP_26

[James T]

Hmmm, what you are after is not clear, how about an example of the form

I have                                 I want
ASD                                     ASD_1
ASD                                     ASD_2
AAA                                     AAA_3

with enough examples to show all the cases you have.

[Jarhed7276]

This method should work. It will require you to create a 2nd column with just the numbering in it, and a 3rd column to concatenate the 2. Here's how you do it:

Put all your products in column 1
Put _1 in the first row of column 2
Drag the cell from row 1, column 2 (b2) down as many rows as you have product codes. When you do this, it should auto number all of the values in the 2nd column, incrementing them by 1. In other words, after you drag, the cells will now be _1, then _2, etc.
In the 3rd column, first row, key in this formula:
CONCATENATE(A1, B1)
Drag this down to the end of your product code rows.
Now this 3rd column should have the results you want.

Hope this helps!

[brewskieman21]

Already tried that. It doesn't work some reason. Thanks though I appreciate. I'm almost finished with what I was doing. Thanks everyone who helped me out.