Good Morning All,
I have a spreadsheet and each column has attributes (product id, description, price, etc) Column "A" has the product id's (IE: 0030-BG) I need to change the id to have the manufacturer acronym infront of the id ( IE: make 0030-BG to AW0030-BG) there should be a way to batch do this yet I cannot figure out how. I dont want to do this line by line for near 3,000 products.
where does the 'AW' come from?Originally Posted by cpeters
Is it fixed for this set of 3000 rows?
Is it variable and on each row? . . . (like column F)
Is it a portion of a cell on each row? . . . . (a part of column F)
to 'fix' column A, select a blank column (say column J) and put in J1
="AW"&A1
=F1&A1
=Left(F1,2)&A1
depending on where the 'AW' is
and formula copy that to the end of your data rows in A1
Copy this column, then select A1 and Paste Special = Values.
Delete the helper column.
hth
---
Si fractum non sit, noli id reficere.
Good afternoon Bryan,
The AW comes from the name of the manufacturer. Usually is 2 letters but sometimes can be 3. It only gets added to the product id column when i get the spreadsheet from manufacturer. It would be going down anywhere from 5 to (as in this case) 3,000 cells. I pretty much simply want to go down line by line adding the acronym one at a time using copy/paste if there is a way I can do it all at once. Hope I made myself clearer.
Hi,
Your answers to Bryan's questions are not really completed ...
Concatenation (adding two cells..) is possible with precise cell addresses ...
If cell A2 equals your Id number and cell B2 your manufacturer name,
then
in cell C2 you can type :
=A2&Left(B2,2)
HTH
Carim
Good afternoon Carim,
I apologize if I am not being clear. I don't use excel much at all so I truly dont understand it.
When I get my lists, they are in excel format. They are formatted to their values, column A is the products list number or id that they use for stock/ordering column B is the description and so forth through the sheet.
When they come in I create a identifier usually 2 to 3 letters to be put in front of the product ID so that I am able to know from which manufacturer/wholesaler it comes from. I keep these on a seperate sheet in notepad.
IE: American Burrito Company becomes ABC
Product 0003BG - 3 pack Burritos Grande
becomes
ABC0003BG
Normally I would simply go to each product ID in column A and simply paste the ABC before the product ID. I just received a sheet with over 3,000 products on it that I have to put the personal acronym I use to identify which manufacturer/wholesaler it is before the product ID. I really don't want to sit and slog through it line by line and was hoping there was an easier way to do it. I also have to creeate the price markup for them and that will be another question!
I hope this makes it clearer and apologize for not being so.
Hi,
Assuming first row is used for titles, in cell B2 use formula :
="ABC"&A2
and copy this formula down to row 3000
that's it ...
HTH
Carim
Good afternoon Carim!
That did the trick! Thank you! Is there aw ay to do something similar to prices? I have a column that is the base wholesale price of each item. I need to raise them by a percentage for my pricing? Is there a way I can do this en masse as well. You guys are great!
Hi,
Glad it fixed your problem ...
Assume your percentage increase is located in cell H1,
and the first wholesale price is located in cell D2
then your formula in cell H2
=D2*(1+$H$1)
to be copied all the way down ...
HTH
Carim
HI Carim!
Please forgive my ignorance but do I just type in 175% (or whatever percentage I am putting in) into the cell?
Once again thank you! It looks like i may need to take a crash course in excel as several other wholesalers have asked me to list their products and have large lists as well.
Well to input 10% in a cell, you type in 0.1 and then Format Cells Percentage.
When it comes to working out a price increase :
newprice = oldprice*(1+percentage increase)
Hope this is clear enough
Carim
Hi Carim,
LOL actually it isn't (for me) as I dont know excel that well. right now the prices go from I1 down. I dont really know formulas at all.
In cell H1 type your percentage
In cell J1 type =I1*(1+$H$1)
and then copy the formula down ...
Carim
Hi Carim, and thanks for your assistance here, my weekends are (as usual) busy with scoring at cricket, and am about to leave for another match.In cell H1 type your percentage
In cell J1 type =I1*(1+$H$1)
and then copy the formula down ...
Carim
With the adding 'ABC' to column A, if this is a regular thing it might be easier for cpeters to Insert a new column A, do Carim's ="ABC"&B1 down that column, Copy column A and Paste Special = Values back over itsself, then, if all appears well delete column B (the old column A). This leaves other details in the row untouched.
Another way to increase prices (a 5% increase is a new price of 1.05 etc) is to put 1.05 in a spare cell, copy that (single) cell, then select the prices that need to increase (I1 to I3000 ?) and Paste Special = Multiply. Then delete the 1.05 cell.
There is then no need to copy - paste values - and remove a helper coumn.
Hope this helps.
---
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks