Convert to common names based on conversion list

[Sontauzo]

Hello All,

I am trying to convert information from two different systems to the same naming system so that i can compare and analyze the data. Each system has a product number and name for each item. My trouble is that the numbers and names are different between the systems. I am wondering if there is a way for Excel to look at a static list of part numbers and convert the names of the products to the same nomenclature.

Right now the data is all housed in the same Workbook, but on different sheets (Storage and Usage). I would like to set up a Sub that would look at each sheet, compare it to the conversion list (on a separate worksheet named "Conversion"), and replace the names on the Storage/Usage sheets to the indicated value on the Conversion sheet.

This is how the "Conversion" sheet is set up. The actual list is much longer, but the information is in columns A, B, and C. On the Storage sheet the part number is in column "R", and it is in column "D" on the Usage sheet.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Storage Part #[/TD]
[TD]Common Name[/TD]
[TD]Usage Part #[/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD]Hammer[/TD]
[TD]001[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Shovel[/TD]
[TD]060[/TD]
[/TR]
[TR]
[TD]864[/TD]
[TD]Screwdriver[/TD]
[TD]217[/TD]
[/TR]
[TR]
[TD]38[/TD]
[TD]Nails[/TD]
[TD]113[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]Screws[/TD]
[TD]681[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Tarp[/TD]
[TD]941[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]Saw[/TD]
[TD]374[/TD]
[/TR]
[TR]
[TD]95[/TD]
[TD]Paint[/TD]
[TD]662[/TD]
[/TR]
[TR]
[TD]66[/TD]
[TD]Ladder[/TD]
[TD]062[/TD]
[/TR]
</tbody>[/TABLE]

I am not even sure if what i am trying to do is possible, but thank you in advance for your time and help.

Thanks,

BWL

 

[Scott T]

You can use formulas to do this

Excel 2010
	R	S
1	part#	name
2	30	Hammer
3	12	Shovel
4	864	Screwdriver
5	38	Nails
6	21	Screws
7	10	Tarp
8	17	Saw
9	95	Paint
10	66	Ladder
Storage

Cell Formulas
Range	Formula
S2	=VLOOKUP(R2,Conversion!$A$2:$B$10,2,0)
S3	=VLOOKUP(R3,Conversion!$A$2:$B$10,2,0)
S4	=VLOOKUP(R4,Conversion!$A$2:$B$10,2,0)
S5	=VLOOKUP(R5,Conversion!$A$2:$B$10,2,0)
S6	=VLOOKUP(R6,Conversion!$A$2:$B$10,2,0)
S7	=VLOOKUP(R7,Conversion!$A$2:$B$10,2,0)
S8	=VLOOKUP(R8,Conversion!$A$2:$B$10,2,0)
S9	=VLOOKUP(R9,Conversion!$A$2:$B$10,2,0)
S10	=VLOOKUP(R10,Conversion!$A$2:$B$10,2,0)

Excel 2010
	D	E
1	Part#
2	1	Hammer
3	60	Shovel
4	217	Screwdriver
5	113	Nails
6	681	Screws
7	941	Tarp
8	374	Saw
9	662	Paint
10	62	Ladder
usage

Cell Formulas
Range	Formula
E2	=INDEX(Conversion!$B$2:$B$10,MATCH(usage!D2,Conversion!$C$2:$C$10,0))
E3	=INDEX(Conversion!$B$2:$B$10,MATCH(usage!D3,Conversion!$C$2:$C$10,0))
E4	=INDEX(Conversion!$B$2:$B$10,MATCH(usage!D4,Conversion!$C$2:$C$10,0))
E5	=INDEX(Conversion!$B$2:$B$10,MATCH(usage!D5,Conversion!$C$2:$C$10,0))
E6	=INDEX(Conversion!$B$2:$B$10,MATCH(usage!D6,Conversion!$C$2:$C$10,0))
E7	=INDEX(Conversion!$B$2:$B$10,MATCH(usage!D7,Conversion!$C$2:$C$10,0))
E8	=INDEX(Conversion!$B$2:$B$10,MATCH(usage!D8,Conversion!$C$2:$C$10,0))
E9	=INDEX(Conversion!$B$2:$B$10,MATCH(usage!D9,Conversion!$C$2:$C$10,0))
E10	=INDEX(Conversion!$B$2:$B$10,MATCH(usage!D10,Conversion!$C$2:$C$10,0))

Excel 2010
	A	B	C
1	Storage Part #	Common Name	Usage Part #
2	30	Hammer	1
3	12	Shovel	60
4	864	Screwdriver	217
5	38	Nails	113
6	21	Screws	681
7	10	Tarp	941
8	17	Saw	374
9	95	Paint	662
10	66	Ladder	62
Conversion

 

[Sontauzo]

Scott,

Thank you for your help. I have a couple of other applications that the formulas will work for. However, for the main workbook that I am dealing with, I am thinking that a Macro will work better. If i use the formulas on that sheet, i would have to go into each sheet and enter the formulas every time a report is generated. I am hoping to be able to run a Macro and convert all of the names on both sheets with one button click instead of entering the formulas each time.

BWL

 

[Scott T]

What are exact sheet names and what columns are used for what data in each? If you could post a sample of each sheet that would help.

 

[Scott T]

You will need to change the cell references and sheet names to match your data

Sub partnames()
Dim con As Worksheet
Dim stor As Worksheet
Dim useage As Worksheet
Dim lrc As Long
Dim lrs As Long
Dim lru As Long

Set con = Sheets("Conversion")
Set stor = Sheets("Storage")
Set useage = Sheets("Usage")

lrc = con.Cells(Rows.Count, "B").End(xlUp).Row [COLOR=#008000]'find last row in conversion sheet[/COLOR]
lrs = stor.Cells(Rows.Count, "R").End(xlUp).Row [COLOR=#008000]'find last row in storage sheet[/COLOR]
lru = useage.Cells(Rows.Count, "D").End(xlUp).Row[COLOR=#008000] ' find last row in useage sheet[/COLOR]

For s = 2 To lrs '[COLOR=#008000]loop though storeage sheet looking up part # in column S from row 2 to end of data[/COLOR]
    stor.Cells(s, "S") = Application.VLookup(stor.Cells(s, "R"), con.Range("A2:B" & lrc), 2, 0)
Next s
For u = 2 To lrs [COLOR=#008000]'loop though usage sheet looking up part # in column D from row 2 to end of data[/COLOR]
    useage.Cells(u, "E") = Application.Index(con.Range("B2:B" & lrc), Application.Match(useage.Cells(u, "D"), con.Range("C2:C" & lrc), 0))
Next u

End Sub

Test this on a copy of your data. You can then assign this to a button. You will need to save the file as a macro enabled file type like xlsm

 

[Sontauzo]

I will try to lay out all of the details. I apologize that my other posts weren't clear.

On the "Storage" worksheet the raw data looks like this...
[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD]Storage Part # (Column "R")[/TD]
[TD]Name (Column "S")[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD]Hammer (type 2)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Shovel-blue[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]864[/TD]
[TD]Screwdriver large[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]38[/TD]
[TD]Nails #16[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]Screws #7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Tarp - black[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]Saw-hand[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]95[/TD]
[TD]Paint-red[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]66[/TD]
[TD]Ladder-small[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The "Usage" worksheet looks like this...
[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD]Usage Part # (Column "G")[/TD]
[TD]Name (Column "H")[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]001[/TD]
[TD]Large Hammer[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]060[/TD]
[TD]Blue Shovel[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]217[/TD]
[TD]#2 Screwdriver[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]113[/TD]
[TD]Nails-16[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]681[/TD]
[TD]Screws-7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]941[/TD]
[TD]Black Tarp[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]374[/TD]
[TD]Handsaw[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]662[/TD]
[TD]Red Paint[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]062[/TD]
[TD]Small Ladder[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The "Conversion" Worksheet looks like this... (columns A, B, and C)
[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD]Storage Part #[/TD]
[TD]Common Name[/TD]
[TD]Usage Part #[/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD]Hammer[/TD]
[TD]001[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Shovel[/TD]
[TD]060[/TD]
[/TR]
[TR]
[TD]864[/TD]
[TD]Screwdriver[/TD]
[TD]217[/TD]
[/TR]
[TR]
[TD]38[/TD]
[TD]Nails[/TD]
[TD]113[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]Screws[/TD]
[TD]681[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Tarp[/TD]
[TD]941[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]Saw[/TD]
[TD]374[/TD]
[/TR]
[TR]
[TD]95[/TD]
[TD]Paint[/TD]
[TD]662[/TD]
[/TR]
[TR]
[TD]66[/TD]
[TD]Ladder[/TD]
[TD]062[/TD]
[/TR]
</tbody>[/TABLE]

The macro needs to check the Storage Part number in column "R" against Column "A" of the Conversion sheet. When it finds a match, it should take the corresponding Conversion column B value and overwrite the Storage Column S value. See below. The same type of check needs to be done on the Usage sheet as well. Check the Usage part number against Column "C" of the Conversion sheet. When it finds a match, take the corresponding Conversion Column "B value and overwrite the Usage Column "H" value.

These would be the results in the "Storage" sheet after the macro goes through and changes Column "S".
[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD]Storage Part # (Column "R")[/TD]
[TD]Name (Column "S")[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD]Hammer[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Shovel[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]864[/TD]
[TD]Screwdriver[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]38[/TD]
[TD]Nails[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]Screws[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Tarp[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]Saw[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]95[/TD]
[TD]Paint[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]66[/TD]
[TD]Ladder[/TD]
[/TR]
</tbody>[/TABLE]


These would be the results in the "Usage" sheet after the macro goes through and changes Column "H".
[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD]Usage Part # (Column "G")[/TD]
[TD]Name (Column "H")[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]001[/TD]
[TD]Hammer[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]060[/TD]
[TD]Shovel[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]217[/TD]
[TD]Screwdriver[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]113[/TD]
[TD]Nails[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]681[/TD]
[TD]Screws[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]941[/TD]
[TD]Tarp[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]374[/TD]
[TD]Saw[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]662[/TD]
[TD]Paint[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]062[/TD]
[TD]Ladder[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I hope this helps. I know that this seems very convoluted, but the bottom line is that the two systems that we use have different names for the exact same product. I need a macro to go through each sheet and change the names to a Common Name (as designated in the Conversion sheet). Once the names are the same, I can run analytics on the data, but right now there is no common ground between the two systems so I can't combine anything.

Thanks in advance for your time.

BWL