Removing entries in excel with the same prefix but different suffix

Question

I need help with my spreadsheet

the spreadsheet looks something like this:

Hostname	A header	Another header
host1-dns	text 11	text 12
host1-ftp	text 11	text 12
host1-ntp	text 11	text 12
host1-vip	text 11	text 12
host2-dns	text 21	text 22
host2-ftp	text 21	text 22
host2-ntp	text 21	text 22
host3-dns	text 31	text 32
host3-ftp	text 31	text 32
host4	text 41	text 42
host5-sans	text 51	text 52

I need to create a table eliminating duplicate hosts. In my table:

• host1-dns, host1-ftp, host1-ntp, host1-vip is just one host and should be counted once
• host names (prefixes) does not have uniform number of characters
• suffixes do not have uniform number of characters as well
• prefixes and suffices are separated by a hyphen

In the end, the table should look like this:

Hostname	A header	Another header
host1	text 11	text 12
host2	text 21	text 22
host3	text 31	text 32
host4	text 41	text 42
host5	text 51	text 52

I am open to any implementation, formula, or macro.

Thanks in advance :-)

Answer 1

Add helper column with formula:

=LEFT(A2,FIND("-",A2&"-")-1)

then use Remove duplicates dialog for last three column:

Answer 2

Uniquify Table

• In this solution it is assumed that your table starts in cell A1, has headers and has three columns, and you want to replace it with the result.

The Code

Option Explicit

Sub uniquifyTable()
    With Range("A1").CurrentRegion
        With .Columns(1).Resize(.Rows.Count - 1).Offset(1)
            Dim Data As Variant: Data = .Value
            Dim i As Long
            For i = 1 To UBound(Data, 1)
                Data(i, 1) = Left(Data(i, 1), _
                    InStr(1, Data(i, 1) & "-", "-") - 1)
            Next i
            .Value = Data
        End With
        .RemoveDuplicates Columns:=Array(1, 2, 3), Header:=xlYes
    End With
End Sub

Answer 3

You can do this fairly easily using Power Query (available in Excel 2010+)

Paste the M-Code into the Advanced Editor

On Line 2, ensure the referenced table name is the same as that of your original data Table.

Note that the Headers should split into a number of columns depending on the number of unique headers per hostname.

Examine the Applied Steps window and the embedded comments to understand what is going on.

M Code

let
    Source = Excel.CurrentWorkbook(){[Name="tblHosts"]}[Content],

    //split off the part after the hyphen
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Hostname", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Hostname"}),

    //Group by hostnames
    #"Grouped Rows" = Table.Group(#"Split Column by Delimiter", {"Hostname"}, 
           {{"Grouped", each _, type table [Hostname=nullable text, A header=nullable text, Another header=nullable text]}}),

    //combine headers into a unique, sorted list
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Header", each 
        List.Sort(
            List.Distinct(
                List.Combine(
                    {Table.Column([Grouped],"A header"),
                     Table.Column([Grouped],"Another header")
                    })
        ))),

    //split the list into a single item per column
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Header", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Extracted Values", "Header", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv))
    
in
    #"Split Column by Delimiter1"