Replace En Masse Values of Cells With New Names

[surkdidat]

In Sheet "YES_DATA" I have a table C2 to AD227 (some cells are blank) (Product ID in Column A)

Column A	Column B	Column C	Column D	Column E	Column F
PRODUCT ID	(COUNT of values In Row)	Detail_1	Detail_2	Detail_3	Detail_4
001	4	Olive	0.5kg	Square	Cheese
002	2	Gray	10cm
003	1	Golden
004	3	Golden	0.5kg	Square
005	1	Round

The ones that contain values have the old names associated with products

In Sheet New_Names I have a list of ID's in Column J, and the new name in Column K.

Column J	Column K
(OLD VALUE - TO BE REPLACED)	(NEW VALUE)
Olive	Green
Golden	Gold
Round	Cicrular
0.5kg	500g
Square	Cube
Cheese	Cheesy
Gray	Grey
10cm	0.1m

So, in a new sheet I end up with

Column A	Column B	Column C	Column D	Column E	Column F
PRODUCT ID	COUNT	Detail_1	Detail_2	Detail_3	Detail_4
001	4	Green	500g	Cube	Cheesy
002	2	Grey	0.1m
003	1	Gold
004	3	Gold	500g	Cube
005	1	Circular

Hope that makes sense!

 

[Toadstool]

Hi Surkdidat,

I believe this is what you want.

Surkdidat2.xlsx
	A	B	C	D	E	F
1	PRODUCT ID	(COUNT of values In Row)	Detail_1	Detail_2	Detail_3	Detail_4
2	1	4	Olive	0.5kg	Square	Cheese
3	2	2	Gray	10cm
4	3	1	Golden
5	4	3	Golden	0.5kg	Square
6	5	1	Round
7
YES_DATA

Cell Formulas
Range	Formula
B2:B6	B2	=COUNTA(C2:F2)

Surkdidat2.xlsx
	J	K
1	(OLD VALUE - TO BE REPLACED)	(NEW VALUE)
2	Olive	Green
3	Golden	Gold
4	Round	Cicrular
5	0.5kg	500g
6	Square	Cube
7	Cheese	Cheesy
8	Gray	Grey
9	10cm	0.1m
10
New_Names

Surkdidat2.xlsx
	A	B	C	D	E	F
1	PRODUCT ID	(COUNT of values In Row)	Detail_1	Detail_2	Detail_3	Detail_4
2	1	4	Green	500g	Cube	Cheesy
3	2	2	Grey	0.1m
4	3	1	Gold
5	4	3	Gold	500g	Cube
6	5	1	Cicrular
7
8
NewSheet

Cell Formulas
Range	Formula
A2:B8	A2	=IF(YES_DATA!A2="","",YES_DATA!A2)
C2:F8	C2	=IF(YES_DATA!C2="","",IF(ISNA(MATCH(YES_DATA!C2,New_Names!$J$2:$J$9999,0)),YES_DATA!C2,INDEX(New_Names!$K$2:$K$9999,MATCH(YES_DATA!C2,New_Names!$J$2:$J$9999,0))))

 

[Peter_SSs]

For a macro approach, try this.

Sub Update_Values()
  Dim a As Variant
  Dim i As Long
  
  With Sheets("New_Names")
    a = Range("J2", Range("K" & Rows.Count).End(xlUp)).Value
  End With
  Application.ScreenUpdating = False
  Sheets("YES_DATA").Copy After:=Sheets("YES_DATA")
  With Sheets(Sheets("YES_DATA").Index + 1).Columns("C:AD")
    For i = 1 To UBound(a)
      .Replace What:=a(i, 1), Replacement:=a(i, 2), LookAt:=xlWhole, MatchCase:=False
    Next i
    .Parent.Name = "YES_DATA_NEW"
  End With
  Application.ScreenUpdating = True
End Sub

 

[surkdidat]

Hi

I have tried this, however, i seems to have only made an exact copy of the original data (ie not updated with te new values)

PRODUCT ID	(COUNT of values In Row)	Detail_1	Detail_2	Detail_3	Detail_4
2	1	4	Olive	0.5kg	Square	Cheese
3	2	2	Gray	10cm
4	3	1	Golden
5	4	3	Golden	0.5kg	Square
6	5	1	Round

For a macro approach, try this.

Sub Update_Values()
  Dim a As Variant
  Dim i As Long
 
  With Sheets("New_Names")
    a = Range("J2", Range("K" & Rows.Count).End(xlUp)).Value
  End With
  Application.ScreenUpdating = False
  Sheets("YES_DATA").Copy After:=Sheets("YES_DATA")
  With Sheets(Sheets("YES_DATA").Index + 1).Columns("C:AD")
    For i = 1 To UBound(a)
      .Replace What:=a(i, 1), Replacement:=a(i, 2), LookAt:=xlWhole, MatchCase:=False
    Next i
    .Parent.Name = "YES_DATA_NEW"
  End With
  Application.ScreenUpdating = True
End Sub

 

[surkdidat]

Hi

I have just tried this again on a new spreadsheet and it has worked, so not sure what I did first time for it NOT to work. Thank you. I will now upscale this to my larger data, and hopefuly this will all work!

Hi

I have tried this, however, i seems to have only made an exact copy of the original data (ie not updated with te new values)

PRODUCT ID	(COUNT of values In Row)	Detail_1	Detail_2	Detail_3	Detail_4
2	1	4	Olive	0.5kg	Square	Cheese
3	2	2	Gray	10cm
4	3	1	Golden
5	4	3	Golden	0.5kg	Square
6	5	1	Round

 

[Peter_SSs]

(ie not updated with te new values)

has worked, so not sure what I did first time for it NOT to work

Sorry, it was my mistake. I had omitted a couple of "." from the code. That meant it would work or not work depending on what the active sheet was when the code ran. Try this instead.

Sub Update_Values()
  Dim a As Variant
  Dim i As Long
  
  With Sheets("New_Names")
    a = .Range("J2", .Range("K" & Rows.Count).End(xlUp)).Value
  End With
  Application.ScreenUpdating = False
  Sheets("YES_DATA").Copy After:=Sheets("YES_DATA")
  With Sheets(Sheets("YES_DATA").Index + 1).Columns("C:AD")
    For i = 1 To UBound(a)
      .Replace What:=a(i, 1), Replacement:=a(i, 2), LookAt:=xlWhole, MatchCase:=False
    Next i
    .Parent.Name = "YES_DATA_NEW"
  End With
  Application.ScreenUpdating = True
End Sub