Split text in cell and delete suplicates

[avalite]

Hello everyone,

I am working with an excel file which has more than 100 entries. Different items are separate by grade, such as A, AA, AAA or Prime (See attached). I want the grade removed from the item name and then delete all duplicate entries. Can someone helped me with it as I am new to VBA and my code is not really working. Thanks in advance.

 

[sandy666]

With your example:

Description		Description
Bottom Flats A		Bottom Flats
Bottom Flats AA
Bottom Flats Prime
Bottom Flats AAA

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TBD = Table.TransformColumns(Source, {{"Description", each Text.BeforeDelimiter(_, " ", 1), type text}}),
    Distinct = Table.Distinct(TBD)
in
    Distinct

btw. this is NOT vba

 

[avalite]

Sorry, i am not sure how to run this query. Can you please help.

Thanks in advance

 

[sandy666]

• your source should be a table (Excel Table)
• use Ctrl+T
• check the name of the table in the Name Manager (here it is Table1)
• copy M code from the post
• To view the Query Editor without loading or editing an existing workbook query, from the Data - New Query (or Get External Data section in the Power Query ribbon tab), select From Other Sources > Blank Query
• or Data tab, then Get Data > From Other Sources > Blank Query
• from the ribbon open Advanced Editor and replace all there with copied code
• if the name of the table from Name Manager is different than in the code change the name in the code to proper name - both should be the same
• then from the ribbon select Close&Load

 

[Michael M]

Maybe this then

Sub MM1()
Dim lr As Long, r As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
For r = lr To 2 Step -1
    If InStr(Cells(r, 1), "Bottom Flats") And InStr(Cells(r - 1, 1), "Bottom Flats") Then
        Cells(r, 1).Value = "Bottom Flats"
        Rows(r - 1).Delete
End If
Next r
End Sub