Sorting/Filtering data into a few major groups

[wesleyterrill]

Hey guys! Basically, I have some bulk data in Excel and I need help sorting/filtering the data into a more usable format (it will eventually be saved at a CSV). The sheet that I'm referencing has 25 or so columns of data, most of it is useless. I've simplified it into the table below to show what I'm looking to do.

First Name	Last Name	Title	Company	Personal #	Company #	Personal City	Company City
John	Adams	Director	Flow Inc	555-0000	555-1099	Dallas	Seattle
Courtney	Merrill	Sales Manager	Flow Inc	555-1111	555-1099	Seattle	Seattle
Jameson	Brown	VP of Sales	Stark Industries	555-2222	555-8800	Eugene	Portland
Mary	Parker	Inside Sales	Flow Inc	555-3333	555-1099	Tacoma	Seattle
Cindy	Wurth	Director	ABC Co.	555-4444	555-2626	Sacramento	Los Angeles
Todd	Baker	HR Manager	Stark Industries	555-5555	555-8800	Bend	Portland

<tbody>
</tbody>

What I'd like to do on the next sheet is sort each individual person by company BUT I'd like the first row of each group to represent the company only. To put it simply, I will be creating an Account record for the company and an Individual record for each person (ideally, the system we use will make a link between Account and Individual). I'm imagining the 2nd sheet to look something like this:

Account Name	First Name	Last Name	Title	Phone #	City
Flow Inc				555-1099	Seattle
Flow Inc	John	Adams	Director	555-0000	Dallas
Flow Inc	Courtney	Merrill	Sales Manager	555-1111	Seattle
Flow Inc	Mary	Parker	Inside Sales	555-3333	Tacoma
Stark Industries				555-8800	Portland
Stark Industries	Jameson	Brown	VP of Sales	555-2222	Eugene
Stark Industries	Todd	Baker	HR Manager	555-5555	Bend
ABC Co.				555-2626	Los Angeles
ABC Co.	Cindy	Wurth	Director	555-4444	Sacramento

<tbody>
</tbody>

I'm sure there are many ways to approach this, does anybody have any suggestions? I'm playing around with IF formulas right now, but that's about as complex of a formula that I can handle! Keep in mind that the data I'm filtering will have hundreds/thousands of rows of data and there will be a minimum of 50 different companies. I don't think it will be a simple Index or cell reference since the data I'm filtering will be different every time. Let me know if you have any ideas! I appreciate the help, thank you!

 

[initialize]

hmmm... maybe pivotTable? Im not sure.
But if it was me the easiest way would be to use VBA. I pasted the code below.
However this is assuming the columns are as you have shown in the example. If you have extra useless columns or the columns are in diff order u will have to change some number around in the code. It's pretty straightforward

Sub newTable()
    
    Dim rng As Range, companytable As Range, temp As Range, companyNames As Range
    Dim prevCompName As String, switch As String
    
    Set rng = Application.InputBox("Select a range", "Obtain Range Object", Type:=8)
    Set companytable = rng.CurrentRegion
    
    Application.ScreenUpdating = False
    With companytable
        'sort the table by company name and switching company columns to first column of table
        .Sort key1:=.Range(Cells(1, 4), Cells(.Rows.count, 4)), order1:=xlAscending, Header:=xlYes
        .Columns(1).insert
        .Columns(4).Cut Range("A1")
        .Columns(4).Delete
    End With
    
    'resize the table size because of the previous delete
    Set companytable = companytable.CurrentRegion
    
    With companytable
        Set companyNames = .Range("A2:A" & .Rows.count)
        .Cells(1, 5).Value = "Phone #"
        .Cells(1, 7).Value = "City"
        
        prevCompName = ""
        'this loop will create the 'extra' company info row
        For Each temp In companyNames
            
            If temp.Value <> prevCompName Then
                .Rows(temp.Row).Copy
                temp.insert shift:=xlDown
                
                switch = temp.Offset(0, 4).Value
                temp.Offset(-1, 4).Value = temp.Offset(0, 5).Value
                temp.Offset(-1, 5).Value = switch
                
                switch = temp.Offset(0, 6).Value
                temp.Offset(-1, 6).Value = temp.Offset(0, 7).Value
                temp.Offset(-1, 7).Value = switch
            
                temp.Offset(-1, 1).ClearContents
                temp.Offset(-1, 2).ClearContents
                temp.Offset(-1, 3).ClearContents
            End If
            prevCompName = temp.Value
        
        Next temp
        
        'deleting the useless columns
        .Columns(6).Delete
        .Columns(7).Delete
        
    End With
    Application.ScreenUpdating = True


End Sub

This will ask the user to select one cell in the table, so vba will know where your table is, then it will reorganize the whole table.