Match and replace with index

[prayami]

Hi,

We got three sheets in a excel file.
Sheet1: Got three columns. Street, City Name and State Name.
Sheet2: Got one column. City
Sheet3: Got one column. State

Now in Sheet1, I need to replace the City Name with the index number of that City in Sheet2. And the same way, in Sheet1, I need to replace the State Name with the index number of that State in Sheet3.

Please let me know, how can I do this in VBA?
Thank you very much in advance.

[Leith Ross]

Hello prayami,

It would help to see some examples of what you have and what you want to do. I can envision several scenarios but would rather provide you with the one that you need.

Are you using this index number in a formula on Sheet1?

Is the index number to be the actual row number of the match or its position within the array?

[prayami]

Hi Leith,

Thank you very much for reply.
I have attached two files:
Original file : Suburb_City_State_Example_Final.xlsx
Expected File: Suburb_City_State_Example_Final_Result.xlsx

No...I am not using this index number as a formula in the sheet.

In the attached fie, there are three sheets:
Suburb_City_State, City and State.

This is just the sample file. We got 200000 rows. We will prepare the CSV file which we can upload in the MySQL database.
In the sheet1 i.e. "Suburb_City_State", we got the list of Suburb with corresponding City and State in front of that Suburb.

Now first we want to replace City in sheet1 with the index number of that City in Sheet2 i.e. 'City'.
Then second we want to replace State in sheet1 with the index number of that State in Sheet3 i.e. 'State'.

Finally I will make one CSV file. Which will have only Sheet1 with Suburb Name with corresponding index number of city and state. This is to upload in MySQL database.

To answer your question regarding the index. In the sheet2 i.e. 'City', we have already placed the City in the sequence so that we can take its index(or number of row) to replace in sheet1.

Please let me know if you need more information. Thanks again.

[prayami]

Please...I think this is obvious. Somebody will have the ready made code. Thanks.

[Mordred]

If this was obvious, you wouldn't be here . So, you receive the data without an index from MySQL and then you want to create an index in Excel so that you can re-upload into MySQL?

[Leith Ross]

Hello prayami,

Here is the macro to replace the city and state with the index numbers. A button has been added to the worksheet to run the macro.

' Thread:  http://www.excelforum.com/excel-programming/789158-match-and-replace-with-index.html
' Poster:  prayami
' Written: August 24, 2011
' Author:  Leith Ross

Sub ConvertToIndexNumbers()

  Dim Cell As Range
  Dim CityRng As Range
  Dim CityWks As Worksheet
  Dim FindWhat As Variant
  Dim Rng As Range
  Dim StateRng As Range
  Dim StateWks As Worksheet
  Dim Wks As Worksheet
  
  
    Set CityWks = Worksheets("City")
    Set StateWks = Worksheets("State")
    
    Set CityRng = CityWks.Range("A1").CurrentRegion
    Set StateRng = StateWks.Range("A1").CurrentRegion
    
    Set Wks = Worksheets("Suburb_City_State")
    Set Rng = Wks.Range("A1").CurrentRegion
    
      Set Rng = Intersect(Rng, Rng.Offset(1, 0)).Resize(RowSize:=Rng.Rows.Count - 1)
    
      For Each Cell In Rng.Columns(2).Cells
        FindWhat = Cell.Item(1, 1).Value
        Cell.Item(1, 1).Value = CityRng.Find(FindWhat, , xlValues, xlWhole, xlByRows, xlNext, False).Row
          
        FindWhat = Cell.Item(1, 2).Value
        Cell.Item(1, 2).Value = StateRng.Find(FindWhat, , xlValues, xlWhole, xlByRows, xlNext, False).Row
      Next Cell
      
End Sub

[prayami]

Thank you very much Leith. It works like a charm.

Mordred:
Sorry, I should have write "obvious for you guys".

Thanks.

[prayami]

Hi,

This program is working really nice. Please let me know, what should I do to achieve the following:
Currently we are replacing the City Name with number. Now I need to extend this little bit. In the sheet2 i.e. "City". In the first column there is the list of the city. Now I have added column B with some text in front of each city.
When I will run the function in sheet1. I want to replace the city name in sheet1, with the corresponding text in the column B in front of that city.
e.g. In sheet2 i.e. "City":
Sr. No. Column A Column B
1 Auckland City of Sails

Now in sheet1 stead of replacing "Auckland" with 1, it will replace it with text "City of Sails".
Please help me to do this. Thanks.

[Leith Ross]

Hello prayami,

Can you post the updated workbook so I can use it for testing?

[prayami]

Thank you Leith, Please find the updated file.
One more thing: The previous file is giving error if there is no match. I tried to press button in your attached file and it is giving error. I think because none of the text match.

[Leith Ross]

Hello prayami,

Here is the updated macro. It has been added to the attached workbook.

' Thread:  http://www.excelforum.com/excel-programming/789158-match-and-replace-with-index.html
' Poster:  prayami
' Written: August 24, 2011
' Updated: August 26, 2011 - Substitute alternate text for city and state
' Author:  Leith Ross

Sub ReplaceCityState()

  Dim Cell As Range
  Dim CityRng As Range
  Dim CityWks As Worksheet
  Dim FindWhat As Variant
  Dim Match As Range
  Dim Rng As Range
  Dim StateRng As Range
  Dim StateWks As Worksheet
  Dim Wks As Worksheet
  
  
    Set CityWks = Worksheets("City")
    Set StateWks = Worksheets("State")
    
    Set CityRng = CityWks.Range("A1").CurrentRegion
    Set StateRng = StateWks.Range("A1").CurrentRegion
    
    Set Wks = Worksheets("Suburb_City_State")
    Set Rng = Wks.Range("A1").CurrentRegion
    
      Set Rng = Intersect(Rng, Rng.Offset(1, 0)).Resize(RowSize:=Rng.Rows.Count - 1)
      
      For Each Cell In Rng.Columns(2).Cells
        FindWhat = Cell.Item(1, 1).Value
          Set Match = CityRng.Find(FindWhat, , xlValues, xlWhole, xlByRows, xlNext, False)
          If Not Match Is Nothing Then Cell.Item(1, 1).Value = Match.Offset(0, 1)
          
        FindWhat = Cell.Item(1, 2).Value
          Set Match = StateRng.Find(FindWhat, , xlValues, xlWhole, xlByRows, xlNext, False)
          If Not Match Is Nothing Then Cell.Item(1, 2).Value = Match.Offset(0, 1)
      Next Cell
      
End Sub

[prayami]

Thanks Leith. You are really helpful.