0

I would like a way to replace all different names for a country and replace them with one name to make future pivot tables more effective.

For example: If the country column has a bunch of different rows containing GBR, GB, UK, United Kingdom, etc.

I want to change them all to United Kingdom without having to manually filter them and change them. Is there a way to make a table and then let excel do the rest of the work?

jf12345
  • 25
  • 1
  • 6
  • Welcome to stack overflow. Please provide some sample data and the codes that you already tried so that we are better able to help you. Also what program do you use. If you use excel please refrain from using macros tag and use [excel-vba] instead. – Luuklag Aug 06 '15 at 06:28

1 Answers1

0

I solved it thanks. I made two lookup tables and used them while recording the macro. Maybe it can be a little cleaner, but I just recorded this:

Sub Country_Region_Names() ' ' Country_Region_Names Macro ' Range("Y1").Select ActiveCell.FormulaR1C1 = "-" Range("Y1").Select Selection.AutoFill Destination:=Range("Y1:BA1"), Type:=xlFillDefault Range("Y1:BA1").Select ActiveWindow.SmallScroll ToRight:=-2 Range("BB1").Select ActiveCell.FormulaR1C1 = "NewCountry" Range("BB2").Select ActiveCell.FormulaR1C1 = _ "=IFERROR(VLOOKUP(RC[-35],'Country Lookup Table'!R[2]C[-52]:R[228]C[-51],2,0),"""")" Range("BB2").Select Selection.AutoFill Destination:=Range("BB2:BB1045"), Type:=xlFillDefault Range("BB2:BB1045").Select ActiveWindow.SmallScroll Down:=-1172 ActiveWindow.SmallScroll ToRight:=-34 ActiveWindow.SmallScroll Down:=-1 ActiveWindow.SmallScroll ToRight:=4 ActiveWindow.SmallScroll Down:=-2 ActiveWindow.SmallScroll ToRight:=8 ActiveWindow.SmallScroll Down:=-1 ActiveWindow.SmallScroll ToRight:=3 ActiveWindow.SmallScroll Down:=-1 ActiveWindow.SmallScroll ToRight:=11 ActiveWindow.SmallScroll Down:=-1 ActiveWindow.SmallScroll ToRight:=2 ActiveWindow.SmallScroll Down:=-368 Range("BB2").Select ActiveCell.FormulaR1C1 = _ "=IFERROR(VLOOKUP(RC[-35],'Country Lookup Table'!R4C2:R230C3,2,0),"""")" Range("BB2").Select Selection.AutoFill Destination:=Range("BB2:BB1045") Range("BB2:BB1045").Select ActiveWindow.SmallScroll Down:=933 ActiveWindow.SmallScroll ToRight:=-1 ActiveWindow.SmallScroll Down:=-1069 Range("BC1").Select ActiveCell.FormulaR1C1 = "Region" Range("BC2").Select ActiveCell.FormulaR1C1 = "" Range("BB2").Select Selection.Copy Range("BC2").Select ActiveSheet.Paste Application.CutCopyMode = False ActiveCell.FormulaR1C1 = _ "=IFERROR(VLOOKUP(RC[-1],'Country Lookup Table'!R4C5:R230C6,2,0),"""")" Range("BC2").Select Selection.AutoFill Destination:=Range("BC2:BC256") Range("BC2:BC256").Select End Sub

jf12345
  • 25
  • 1
  • 6