Sorting/Filtering data into a few major groups


New Member
Feb 9, 2016
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 NameLast NameTitleCompanyPersonal #Company #Personal CityCompany City
JohnAdamsDirectorFlow Inc555-0000555-1099DallasSeattle
CourtneyMerrillSales ManagerFlow Inc555-1111555-1099SeattleSeattle
JamesonBrownVP of SalesStark Industries555-2222555-8800EugenePortland
MaryParkerInside SalesFlow Inc555-3333555-1099TacomaSeattle
CindyWurthDirectorABC Co.555-4444555-2626SacramentoLos Angeles
ToddBakerHR ManagerStark Industries555-5555555-8800BendPortland


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 NameFirst NameLast NameTitlePhone #City
Flow Inc555-1099Seattle
Flow IncJohnAdamsDirector555-0000Dallas
Flow IncCourtneyMerrillSales Manager555-1111Seattle
Flow IncMaryParkerInside Sales555-3333Tacoma
Stark Industries555-8800Portland
Stark IndustriesJamesonBrownVP of Sales555-2222Eugene
Stark IndustriesToddBakerHR Manager555-5555Bend
ABC Co.555-2626Los Angeles
ABC Co.CindyWurthDirector555-4444Sacramento


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!

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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(4).Cut Range("A1")
    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
                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
    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.
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back