NEED HELP WITH SEEMINGLY IMPOSSIBLE REQUEST

NICKATWORK

New Member
Joined
Nov 5, 2004
Messages
44
I have a spreadsheet with part numbers in column A and Description for the part number in Column B

The problem I'm faced with is each part number has a different code that needs to be attached to the end of it to show color, and the color that code represents added to the beginning of the description in column B...

-25 for Grey
-03 for Almond
-49 for Burgandy
-36 for Blue Frost
-53 for Pine Forest
-11 for Black

So, esentially abc123 would become abc123-25 and grey added to the front of the description then abc123-03and almond added to the descrition, abc123-49 ect. ect.... moving on to the next part number after all codes and colors have been added to that part number.

Now the only way I can fathom doing this is to use a counter that loops four times on a single part number in row A, concatenating part number with the dash and code, and adding the color to the beginning of the description in Column B and pasting the result into sheet 2, before moving to the next part number.

Is this even possible or have I lost my mind??
If it is possible can someone please help me accomplish this?
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
perhaps I'm not fully understanding you but it seems like a very simple concatenation.

If A1 contains abc and b1 contains -45
=CONCATENATE(A1,B1)
will give you back abc-45

even simpler is mere: =A1&B1
 
Upvote 0
Sounds as though you just want to concatenate data as nbrcrunch has shown you. If you have the cell coloured and you want some way to add the color index that Excel associates with colours then advise what you are trying to do.
 
Upvote 0
no no no......

In Column A would be ABC123 and Column B would have the description we'll say its an office chair....

what I need is to take that abc123 and add a dash and color code to the end of it, and at the same time add the color that code represents to the beginning of the description. so that ABC123 office chair would become ABC123-25 Grey, office chair and do the same thing with the same ABC123 number only a different code and color until all codes and colors are added, then move on the next part number....... and do all the codes over again with that part number

The Part Number and description appear only once in the spreadsheet but I have to use it 6 times does that make sense??

Also, These color codes do not appear in my speadsheet I just have them in a printed list.[/list]
 
Upvote 0
NICKATWORK said:
so that ABC123 office chair would become ABC123-25 Grey, office chair and do the same thing with the same ABC123 number only a different code and color until all codes and colors are added, then move on the next part number....... and do all the codes over again with that part number

The Part Number and description appear only once in the spreadsheet but I have to use it 6 times does that make sense??

Also, These color codes do not appear in my speadsheet I just have them in a printed list.

Firstly you are going to have to create a list then from your printed doc unless you can get an e-copy or scan it. I suggest having a list approach where one column is the numer you've assigned for the colour and the other column is the colour itself. You can then use a column by your data to add the number if not already there and use a vlookup to grab the colour desciption and use this in a concatenate formula to add to the existing data.

By creating a list of colour and number you can then re-use this in the future if required.
 
Upvote 0
ASI Spreadsheet.xls
ABCD
1Part NumberDescriptionList Price
2ASI 11121242LOCKER, STARTER;SINGLE TIER TRADITIONAL COLLECTION; 16 GAUGE DOORS, FRAMES AND BOTTOMS, W/RECESSED HANDLES, FULL LENGTH PIANO HINGES, LOUVERS AND LEGS 12X12X42, WT 28LBS112.00
3ASI 11121542LOCKER, STARTER;SINGLE TIER TRADITIONAL COLLECTION; 16 GAUGE DOORS, FRAMES AND BOTTOMS, W/RECESSED HANDLES, FULL LENGTH PIANO HINGES, LOUVERS AND LEGS 12X15X42, WT 30LBS118.00
4ASI 11121842LOCKER, STARTER;SINGLE TIER TRADITIONAL COLLECTION; 16 GAUGE DOORS, FRAMES AND BOTTOMS, W/RECESSED HANDLES, FULL LENGTH PIANO HINGES, LOUVERS AND LEGS 12X18X42,WT 32LBS124.00
5ASI 11121254LOCKER, STARTER;SINGLE TIER TRADITIONAL COLLECTION; 16 GAUGE DOORS, FRAMES AND BOTTOMS, W/RECESSED HANDLES, FULL LENGTH PIANO HINGES, LOUVERS AND LEGS 12X12X54,WT 39LBS137.00
6ASI 11121554LOCKER, STARTER;SINGLE TIER TRADITIONAL COLLECTION; 16 GAUGE DOORS, FRAMES AND BOTTOMS, W/RECESSED HANDLES, FULL LENGTH PIANO HINGES, LOUVERS AND LEGS 12X15X54,WT 42LBS142.00
7ASI 11121854LOCKER, STARTER;SINGLE TIER TRADITIONAL COLLECTION; 16 GAUGE DOORS, FRAMES AND BOTTOMS, W/RECESSED HANDLES, FULL LENGTH PIANO HINGES, LOUVERS AND LEGS 12X18X54, WT 45LBS151.00
8ASI 11121266LOCKER, STARTER;SINGLE TIER TRADITIONAL COLLECTION; 16 GAUGE DOORS, FRAMES AND BOTTOMS, W/RECESSED HANDLES, FULL LENGTH PIANO HINGES, LOUVERS AND LEGS 12X12X66, WT 42LBS151.00
9ASI 11121566LOCKER, STARTER;SINGLE TIER TRADITIONAL COLLECTION; 16 GAUGE DOORS, FRAMES AND BOTTOMS, W/RECESSED HANDLES, FULL LENGTH PIANO HINGES, LOUVERS AND LEGS 12X15X66, WT 45LBS158.00
10ASI 11121866LOCKER, STARTER;SINGLE TIER TRADITIONAL COLLECTION; 16 GAUGE DOORS, FRAMES AND BOTTOMS, W/RECESSED HANDLES, FULL LENGTH PIANO HINGES, LOUVERS AND LEGS 12X18X66, WT 48LBS167.00
Sheet1


This is what my sheet looks like is there not some VBA Way I can loop throgh 6 times concatenate what's neded and paste it to a new sheet going row by row??
 
Upvote 0
Put the color codes across the top, where they will appear as column headings. We'll assume they are in row one, columns "C" to "E". Example codes will be "10" for Red, "20" for White, and "30" for "Blue".

Column "A" has your item current item codes (starting in cell "A2"), and "B" the descriptions. We'll ignore the descriptions for now. Cells A1 & B1 have the titles "Code" and "Description", respectively. We'll assume the first code, in cell A2, is ABC123. Also, we'll assume there are ten items.

In cell C2, put what inside these brackets [=$A1&"-"&C$2]

This combines the column heading with the item code. You should see "ABC123-10" as the result. Because the formula is anchored (the two "$"), you can copy it to all rows & columns (ie. from C2 to E11). When done, you will have a new set of item-codes, one for each item/color combination.

Copy/Paste special (Values) will lock-in the new codes.

There are several ways to lock in the descriptions. You could repear the above process, using the current descriptions and the color names (reverse the formula to put the color in front of the description: [=C$2&" - "&$A1]) You'll need to be cautious when you put the new codes & descriptions together in this case.

Or you could use VLOOKUP formulas to find the original descriptions and match them to the colors. In this case, the formulas will need to lookup using part of the cells contents. For example "VLOOKUP(LEFT(C3, LEN(c3)-3),DATARANGE,2) If you have named the table with the original Codes & Descriptions "DataRange" (in example, DataRange is A2:B11).

This is more reliable, but more advanced. If you want, go ahead and try it. If you have a problem, this board is a great place for more answers.

Good luck.
 
Upvote 0
Does this get you close to what you need?
Code:
Sub AddColours()
'Written by Barrie Davidson
Dim DataRange As Range
Dim NewPartNo As Range
Dim NewDesc As Range
Dim PriceRange As Range
Dim i As Long

Set DataRange = Range("A2:C" & Range("A1").End(xlDown).Row)
Sheets.Add after:=ActiveSheet
Set NewPartNo = Range("A2")
Set NewDesc = Range("B2")
Set PriceRange = Range("C2")

'Grey
For i = 1 To DataRange.Rows.Count
    NewPartNo = DataRange.Cells(i, 1).Value & "-25"
    Set NewPartNo = NewPartNo.Offset(1)
    NewDesc = "Grey " & DataRange.Cells(i, 2).Value
    Set NewDesc = NewDesc.Offset(1)
    Set PriceRange = DataRange.Cells(i, 3).Value
    Set PriceRange = PriceRange.Offset(1)
Next i

'Almond
For i = 1 To DataRange.Rows.Count
    NewPartNo = DataRange.Cells(i, 1).Value & "-03"
    Set NewPartNo = NewPartNo.Offset(1)
    NewDesc = "Almond " & DataRange.Cells(i, 2).Value
    Set NewDesc = NewDesc.Offset(1)
    Set PriceRange = DataRange.Cells(i, 3).Value
    Set PriceRange = PriceRange.Offset(1)
Next i

'Burgundy
For i = 1 To DataRange.Rows.Count
    NewPartNo = DataRange.Cells(i, 1).Value & "-49"
    Set NewPartNo = NewPartNo.Offset(1)
    NewDesc = "Burgundy " & DataRange.Cells(i, 2).Value
    Set NewDesc = NewDesc.Offset(1)
    Set PriceRange = DataRange.Cells(i, 3).Value
    Set PriceRange = PriceRange.Offset(1)
Next i

'etc.

End Sub
 
Upvote 0
This is exactly what I need only it stops on an error after the first color is completed, and says object required. I'm not sure what causes it I've stepped through it a couple of times but can't figure it out.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,238
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top