Cleaning data

[MadHatsJess]

Hi

I have a very large data set with customer nationalities. I need to clean the nationalities up as there are several variations.

For example France should be French, British National should be British.

I can easily do this however I will be refreshing the data daily and am looking for an automated way.

I have made a table listing "From and To".

Can anyone help?

I was thinking of recording a macro and using the find and replace function but I think it would take too long as the list is quite big. (I am not the best with Macros so couldn't write it)

Thanks

 

[Fluff]

How about

Sub ReplaceDetails()
   Dim ary As Variant
   Dim i As Long
   
   With Sheets("List")
      ary = .Range("A2", .Range("A" & Rows.Count).End(xlUp).Offset(, 1))
   End With
   
   With Sheets("Data")
      For i = 1 To UBound(ary)
         .Range("E:E").Replace ary(i, 1), ary(i, 2), xlWhole, , False, , False, False
      Next i
   End With
End Sub

As you haven't given much detail, you'll need to change sheet names & ranges to suit

 

[MadHatsJess]

Worked Perfectly - Thank you so much!

 

[Fluff]

Glad to help & thanks for the feedback