Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

There's some data like this (with over 500,000 entries)

Name    |    State
----         -----
Billy        Utah
Sue          California
Joe          Utah
Sally        California
John         Michigan

What i'm trying to do is export into a new spreadsheet with the data for each state (so it'd create files like California.csv and Utah.csv etc..

Is there a quick automated way to do something like that?

share|improve this question
    
Do you have access to a *nix system? Cygwin perhaps? This is very very easy with standard UNIX tools. –  terdon Aug 21 '13 at 19:00
    
Is the original data in an Excel workbook or in a CSV? –  Excellll Aug 22 '13 at 14:31
    
It's in an excel workbook –  Talon Aug 22 '13 at 19:29

3 Answers 3

Assuming 'Name' in column A, and 'State' in column B:

In column C, use the formula ="echo """&A1&""" >>"&B1&".csv".

This will append (hence the >>) each line to the file (state).csv.

Copy the contents of column C to the clipboard and paste into a command prompt window (remembering to cd to the correct directory first).

(Note - untested - check the result looks sensible first.)

share|improve this answer

This is cobbled together, but it works for me. Change the destination and the "State" header if need be. You can also change the filter if your worksheet is more complicated than just two columns.

Option Explicit

Sub CreateCSVfromWS()
Dim ws As Worksheet
    Application.ScreenUpdating = False

    Call Filter
    Call MakeMonthSheets

        For Each ws In ActiveWorkbook.Worksheets
            ws.SaveAs "C:\Destination\" & ws.Name & ".csv", xlCSV
        Next

    Application.ScreenUpdating = True
End Sub

Sub Filter()
Columns("A:B").Select
    Selection.AutoFilter
    ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Add Key:=Range _
        ("B:B"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Sub MakeMonthSheets()

    Dim rngState As Range
    Dim rngCell As Range
    Dim sh As Worksheet
    Dim shDest As Worksheet
    Dim rngNext As Range

    Const sLNHEADER As String = "State"

    Set sh = ThisWorkbook.Sheets("Sheet1")
    Set rngState = sh.UsedRange.Find(sLNHEADER, , xlValues, xlWhole)

    'Make sure you found something
    If Not rngState Is Nothing Then
        'Go through each cell in the column
        For Each rngCell In Intersect(rngState.EntireColumn, sh.UsedRange).Cells
            'skip the header and empty cells
            If Not IsEmpty(rngCell.Value) And rngCell.Address <> rngState.Address Then
                'see if a sheet already exists
                On Error Resume Next
                    Set shDest = sh.Parent.Sheets(rngCell.Value)
                On Error GoTo 0

                'if it doesn't exist, make it
                If shDest Is Nothing Then
                    Set shDest = sh.Parent.Worksheets.Add
                    shDest.Name = rngCell.Value
                End If

                'Find the next available row
                Set rngNext = shDest.Cells(shDest.Rows.Count, 1).End(xlUp).Offset(1, 0)

                'Copy and paste
                Intersect(rngCell.EntireRow, sh.UsedRange).Copy rngNext

                'reset the destination sheet
                Set shDest = Nothing
            End If
        Next rngCell
    End If

End Sub
share|improve this answer

Here's a simple but somewhat tedious way to do it. It's tedious to set up, but simple to execute. There are some simple changes that don't quite match the question (but are close) and are simpler to do.

  1. Set up the master workbook as the example in the question
  2. Set up a workbook for the first state (you wanted different files) with the name of the state in A1 and a simple conditional =IF([master.xlsx]Sheet1!$B2=$A$1,[master.xlsx]Sheet1!$A2,"") in A2, then copy for the length of the name list. Note that the row is not absoluted, so you'll get blanks and the names for state in A1.
  3. Set up a macro to copy tab 1 values to tab 2 and sort, so the blanks fall off the end.
  4. Copy the first state workbook to form the remainder states; changing the state name

It would be easier to set up a tab per state and do it in one workbook; you could name the tab per the state and use the tab name in the conditional formula. There's probably a way to do this in one step with array formulas, but I can't remember how right now.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.