none
matching words from transliteration and replacing using vba RRS feed

  • Question

  • Questionmatching words from transliteration and replacing using vba

    There is txt files
    here this structure (C:/partdata.txt)
    I can't attach txt file on this forum, so here the link
    https://dropmefiles.com/vzgGv


    there is another file with transliteration
    C:/23.txt

    it contains
    old            new
    MAKFA    МАКФА
    makar    макароны
    макар.    макароны
    MAKFA    МАКФА
    Макар    макароны
    макар.    макароны
    мак    макароны
    Мак    макароны
    макф        макфа
    маkфа       макфа 
    kрупа       крупа
    https://dropmefiles.com/O7VWu

    old column is word which must be replaced, but new column is on what word from old column must be replased in partdata.txt in GOODS_NAME column.

    example of partdata.txt
    ID ITEM_SUM BARCODE QUANTITY PRICE NDS10 NDS18 GOODS_NAME ID_C_REGCODES_CASH_VOUCHER DISCOUNTNAME DISCOUNTSUM
    3972 43.99 1 43.99 4 0 2049750 MAKFA Макар.УЛИТКИ 450г 3971 0

    GOODS_NAME columns
    MAKFA Макар.УЛИТКИ
    so Макар. there is in old column of 23.txt and it should be replaced on макароны in partdata.txt from new column.

    how to create vba script in separate file 1.xlsx(script must be run from xlsx, cause txt can have 10M rows) that could be match the words from the good_names column of partdata.txt file with the words in the translit file 23.txt,
    and if it found the word in good_names column of partdata.txt that there is in the old column of translit file 23.txt, then replace it word on new one from the new column of 23 txt in good_names column of partdata.txt like in my example
    .
    output
    ID ITEM_SUM BARCODE QUANTITY PRICE NDS10 NDS18 GOODS_NAME ID_C_REGCODES_CASH_VOUCHER DISCOUNTNAME DISCOUNTSUM
    3972 43.99 1 43.99 4 0 2049750 MAKFA макароны УЛИТКИ 450г 3971 0       
    Thursday, October 18, 2018 1:00 PM

All replies

  • Hi merzavazeh,

    For convenience, I recommend that you copy all two txt files, and then import the data from the two copies into the Excel worksheet separately. I assume that the data of the copy of the /partdata.txt is imported into the first worksheet, and the 23.txt copy of the data is imported into the second sheet.

    Then refer to the following code:

    Sub test3()
      Dim rowCount As Integer
      rowCount = Sheet1.Range("A" & rows.Count).End(xlUp).Row
      rowCount2 = Sheet2.Range("A" & rows.Count).End(xlUp).Row
      MsgBox rowCount
      MsgBox rowCount2
      For i = 2 To rowCount
        For j = 2 To rowCount2
          If Sheet1.Range("H" & i).Value = Sheet2.Range("A" & j).Value Then
            Sheet1.Range("H" & i).Value = Sheet2.Range("B" & j)
          End If
        Next j
      Next i
    End Sub

    After the code runs the above code, copy the data from the first worksheet to a TXT file, which is the result file you want.

    Hopefully it helps you.

    Best Regards,
    Bruce


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Friday, October 19, 2018 9:10 AM
    Moderator
  • Hi merzavazeh,

    For convenience, I recommend that you copy all two txt files, and then import the data from the two copies into the Excel worksheet separately. I assume that the data of the copy of the /partdata.txt is imported into the first worksheet, and the 23.txt copy of the data is imported into the second sheet.

    Then refer to the following code:

    Sub test3()
      Dim rowCount As Integer
      rowCount = Sheet1.Range("A" & rows.Count).End(xlUp).Row
      rowCount2 = Sheet2.Range("A" & rows.Count).End(xlUp).Row
      MsgBox rowCount
      MsgBox rowCount2
      For i = 2 To rowCount
        For j = 2 To rowCount2
          If Sheet1.Range("H" & i).Value = Sheet2.Range("A" & j).Value Then
            Sheet1.Range("H" & i).Value = Sheet2.Range("B" & j)
          End If
        Next j
      Next i
    End Sub

    After the code runs the above code, copy the data from the first worksheet to a TXT file, which is the result file you want.

    Hopefully it helps you.

    Best Regards,
    Bruce


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    i try do it

     here file xlsx (16mb)

    https://dropmefiles.com/Fg2Cz

    so when i run code, it mark yellow this string, why?

      rowCount = Sheet1.Range("A" & Rows.Count).End(xlUp).Row

    Friday, October 19, 2018 10:01 AM
  • Sunday, October 21, 2018 6:39 AM
  • Hi merzavazeh,

    Is the data for the Good_names column in Excel the correct data? If it is correct, you need to use a regular expression to replace the old string of the Good_names column.

    Hopefully it helps you.

    Best Regards,
    Bruce

    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Monday, October 29, 2018 2:44 AM
    Moderator