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.

[TABLE="width: 500"]
<tbody>[TR]
[TD]First Name[/TD]
[TD]Last Name[/TD]
[TD]Title[/TD]
[TD]Company[/TD]
[TD]Personal #[/TD]
[TD]Company #[/TD]
[TD]Personal City[/TD]
[TD]Company City[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Adams[/TD]
[TD]Director[/TD]
[TD]Flow Inc[/TD]
[TD]555-0000[/TD]
[TD]555-1099[/TD]
[TD]Dallas[/TD]
[TD]Seattle[/TD]
[/TR]
[TR]
[TD]Courtney[/TD]
[TD]Merrill[/TD]
[TD]Sales Manager[/TD]
[TD]Flow Inc[/TD]
[TD]555-1111[/TD]
[TD]555-1099[/TD]
[TD]Seattle[/TD]
[TD]Seattle[/TD]
[/TR]
[TR]
[TD]Jameson[/TD]
[TD]Brown[/TD]
[TD]VP of Sales[/TD]
[TD]Stark Industries[/TD]
[TD]555-2222[/TD]
[TD]555-8800[/TD]
[TD]Eugene[/TD]
[TD]Portland[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]Parker[/TD]
[TD]Inside Sales[/TD]
[TD]Flow Inc[/TD]
[TD]555-3333[/TD]
[TD]555-1099[/TD]
[TD]Tacoma[/TD]
[TD]Seattle[/TD]
[/TR]
[TR]
[TD]Cindy[/TD]
[TD]Wurth[/TD]
[TD]Director[/TD]
[TD]ABC Co.[/TD]
[TD]555-4444[/TD]
[TD]555-2626[/TD]
[TD]Sacramento[/TD]
[TD]Los Angeles[/TD]
[/TR]
[TR]
[TD]Todd[/TD]
[TD]Baker[/TD]
[TD]HR Manager[/TD]
[TD]Stark Industries[/TD]
[TD]555-5555[/TD]
[TD]555-8800[/TD]
[TD]Bend[/TD]
[TD]Portland[/TD]
[/TR]
</tbody>[/TABLE]

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:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Account Name[/TD]
[TD]First Name[/TD]
[TD]Last Name[/TD]
[TD]Title[/TD]
[TD]Phone #[/TD]
[TD]City[/TD]
[/TR]
[TR]
[TD]Flow Inc[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]555-1099[/TD]
[TD]Seattle[/TD]
[/TR]
[TR]
[TD]Flow Inc[/TD]
[TD]John[/TD]
[TD]Adams[/TD]
[TD]Director[/TD]
[TD]555-0000[/TD]
[TD]Dallas[/TD]
[/TR]
[TR]
[TD]Flow Inc[/TD]
[TD]Courtney[/TD]
[TD]Merrill[/TD]
[TD]Sales Manager[/TD]
[TD]555-1111[/TD]
[TD]Seattle[/TD]
[/TR]
[TR]
[TD]Flow Inc[/TD]
[TD]Mary[/TD]
[TD]Parker[/TD]
[TD]Inside Sales[/TD]
[TD]555-3333[/TD]
[TD]Tacoma[/TD]
[/TR]
[TR]
[TD]Stark Industries[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]555-8800[/TD]
[TD]Portland[/TD]
[/TR]
[TR]
[TD]Stark Industries[/TD]
[TD]Jameson[/TD]
[TD]Brown[/TD]
[TD]VP of Sales[/TD]
[TD]555-2222[/TD]
[TD]Eugene[/TD]
[/TR]
[TR]
[TD]Stark Industries[/TD]
[TD]Todd[/TD]
[TD]Baker[/TD]
[TD]HR Manager[/TD]
[TD]555-5555[/TD]
[TD]Bend[/TD]
[/TR]
[TR]
[TD]ABC Co.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]555-2626[/TD]
[TD]Los Angeles[/TD]
[/TR]
[TR]
[TD]ABC Co.[/TD]
[TD]Cindy[/TD]
[TD]Wurth[/TD]
[TD]Director[/TD]
[TD]555-4444[/TD]
[TD]Sacramento[/TD]
[/TR]
</tbody>[/TABLE]

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.