Логически разобрать строку в excel, чтобы обрезать рядом дубликатов

Предположим, что строка: 

item1, item1N, item1Z, item1fhg, item1_any_letters, item2, item3, item3N, item3H

Моя цель выводится просто 

item1, item2, item3

В настоящее время это около 100 000 строк файла Excel, но при необходимости его можно временно перенести в другую программу и т. д.

По существу, мне нужно определить дубликаты (любую начальную фразу, заканчивающуюся числом) без учета букв после числа. Некоторые фразы могут иметь ,например, "Brand item2, Brand item34", а также, единственным определяющим фактором дубликата является любая и вся терминология после число.

Есть идеи, с чего начать? Каждая строка обычно содержит от 2 до 500 значений, разделенных запятой и пробелом. Ни одна запятая не следует за конечным значением.

3 3
excel vba csv duplicates formula

3 ответа:

Sub Tester()

    Dim re As Object, match As Object
    Dim dict As Object
    Dim arr, arrItems, x As Long, y As Long
    Dim val, matches, valMatch


    Set dict = CreateObject("scripting.dictionary")
    Set re = CreateObject("VBScript.RegExp")
    re.Pattern = "([\w ]+\d+)"
    re.ignorecase = True
    re.Global = True

    arr = ActiveSheet.Range("A1:A100").Value

    For x = LBound(arr, 1) To UBound(arr, 1)
        arrItems = Split(arr(x, 1), ",")
        dict.RemoveAll
        For y = LBound(arrItems) To UBound(arrItems)

            val = Trim(arrItems(y))

            If re.Test(val) Then
               Set matches = re.Execute(val)
               valMatch = matches(0).Value
               If Not dict.exists(valMatch) Then dict.Add valMatch, 1
            End If
        Next y

        Debug.Print arr(x, 1)
        Debug.Print Join(dict.keys, ",") 'where do you want this?

    Next x

End Sub
3

Подход VBA, который чем-то похож на подход Тима для первого пути

  1. используйте a RegExp для удаления недопустимых символов (символов после числа и перед запятой)

  2. Устраните дубликаты с помощью
    а) использовать a Dictionary
    b) встроенная функция удаления дубликатов Excel (запись на лист) 

    Const strDelim = ", "

Sub TestMe()
Dim strTest As String
Dim x
strTest = "item1, item1N, item1Z, item1fhg, item1_any_letters, item2, item3, item3N, item3H"
x = Split(DeDupe(strTest), strDelim)
'fix last element
x(UBound(x)) = Left$(x(UBound(x)), Len(x(UBound(x))) - 1)
Call Method2(x)
End Sub

Sub Method2(ByVal x)
Dim objDic As Object
Dim y As Variant
Set objDic = CreateObject("Scripting.Dictionary")
Dim lngRow As Long
For lngRow = LBound(x) To UBound(x)
objDic(x(lngRow)) = 1
Next lngRow
MsgBox Join(objDic.keys, strDelim)
End Sub      

Function DeDupe(strIn As String) As String
Dim objRegex As Object
Set objRegex = CreateObject("vbscript.regexp")
With objRegex
.Global = True
.Pattern = "(.+?\d+)[^\d]+(,|$)"
DeDupe = .Replace(strIn, "$1,")
End With
End Function

Option B

    'another potential option. Not applied in this code
    Sub Method1(ByVal x)
    Dim y As Variant
    Dim rng1 As Range
    With ActiveSheet
    .[a1].Resize(UBound(x) + 1, 1) = Application.Transpose(x)
    .Columns("A").RemoveDuplicates Columns:=1, Header:=xlNo
    y = Application.Transpose(Range([a1], Cells(Rows.Count, "A").End(xlUp)))
    End With
    MsgBox Join(y, strDelim)
    End Sub
2

Это, вероятно, несовершенно, так как это быстрый хак, который удаляет только самые правые нецифровые строки. Вам понадобится некоторое знание regexp, чтобы настроить его на свои нужды.

В любом случае, следуйте инструкциям "установка", приведенным здесь , сохраните модуль, и вы сможете написать в своем листе формулу, такую как

=S(A1;"[^0-9]*$";"")

В, скажем, ячейке B1. Если ячейка А1 содержит "элемент 1234 бла бла", то B1 будет теперь содержать пункт "1234". Перетащите формулу во все ячейки столбца B и сохраните значения в другой файл Excel для сортировки (или вы можете попробовать сортировку и подсчет на месте).

К сожалению, я не считаю, что делать это в 100 000+ ячеек является практичным (я даже советую не подводить итоги на месте).

Вам было бы гораздо лучше, если бы вы установили textools (sed, grep, uniq...) для Windows, и запуск вашего файла через фильтр. Предполагая, что каждая строка представляет один элемент, как указано выше, фильтр, такой как

sed -e 's/^\([^0-9][^0-9]*[0-9][0-9]*\).*/\1/g' | sort | uniq -c | sort -rn

Получит ваш файл 100 000 строк и верните что-то вроде

79283 Item 1
 1234 Item 2
  993 Item 3
  ..........

(На некоторых платформах вы могли бы написать (\D+\d+) вместо ([^0-9]..., но я не уверен в поведении окон).

Еще лучшим выбором инструментов был бы (Strawberry)Perl, который также поддерживает CSV, или язык Python.

0