Sorting/Filtering data into a few major groups

wesleyterrill

New Member
Joined
Feb 9, 2016
Messages
15
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

<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 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

<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!
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

initialize

New Member
Joined
Dec 7, 2015
Messages
29
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


Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,224
Messages
5,594,916
Members
413,952
Latest member
JGer

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 MrExcel.com.
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 "mrexcel.com".
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
Top