Spreadsheet Data Sorting

January 28, 2011 9:39 AM

Excel Spreadsheet - AutoMagically re-arrange data from one sheet to many based upon Alphabet?

Could someone with the knowledge please explain how I might re-arrange a spreadsheet containing a single column of words into diffferent sheets where each sheet lists a certain section of the original data, by initial letter?

So I currently have

x1 worksheet with a huge words list in 'A' and would like to have

x25 other worksheets with words starting with 'B' on sheet number 2, 'C' on sheet number 3 and so on...

IE Take list of words in 'A', and if they start with a 'B' put them in sheet 2 column A, if they start with a 'C' in sheet 3 column 'A', if they start with a 'D' in sheet column 'A' and so on until 'Z'

Hopefully I've been clear enough :) I think what I need as well as this 'basic' solution is a pivot table - which I'm just starting to read up on... thank you!

posted by DrtyBlvd to Computers & Internet (7 answers total)

Is this something you just need to do just once? If so, why not sort the column on the original sheet alphabetically, then cut and paste to new sheets?
posted by Oddly at 11:54 AM on January 28, 2011

Response by poster: Yes, only once; however there are nearly 173,000 entries - I'm hoping for an elegant solution with some form of formula!

Cut 'n' paste will be the last resort - and maybe the only one? :) Guess my expectation of this being a simple task is misplaced!
posted by DrtyBlvd at 12:52 PM on January 28, 2011

This isn't a pivot table, as you have no table. You're only looking at 26 sheets and 25 cut and paste operations. You can shift-select a range of words and cut and paste the whole block at once. I don't think it'll take as long as you fear.

I don't think a formula will work here; you might be able to use a filter, or some VBscript. But I'm assuming this is out of league. The sheets aspect means simple unix tools on .csv are out.
posted by pwnguin at 2:51 PM on January 28, 2011

Seconding what pwnguin said. Seems to me like a macro is needed.

-- Cycle through all the words on the main page
-- Pick the first letter (left(cell, 1))
-- Copy to the last line+1 on the appropriate sheet
-- Go to the next word on the main page
posted by inigo2 at 3:53 PM on January 28, 2011

Best answer: Hmmm... strangely enough, just yesterday there was someone asking to do almost the reverse!

Here's a macro I put together. You'll have to first sort your main sheet alphabetically (A to Z) before you run this macro. It will create a new sheet for each letter and copy the words that start with the letter to that sheet. It worked ok with my crude test case.


Sub WordSplitter()

' WordSplitter Macro

Dim mainSheetName As String
Dim currentLetter As String
Dim startLetter As String
Dim i As Integer
Dim rowNum As Integer
Dim lastRowNum As Integer

mainSheetName = ActiveSheet.Name 'Remember the main sheet name
Application.ScreenUpdating = False

rowNum = 0
lastRowNum = 1

For i = 0 To 25

Worksheets(mainSheetName).Activate 'Activate the main sheet
currentLetter = Chr(Asc("A") + i)

'Search until the start of next letter
Do
rowNum = rowNum + 1
startLetter = UCase(Mid(ActiveSheet.Cells(rowNum, 1), 1, 1))
Loop While startLetter = currentLetter

startLetter = UCase(Mid(ActiveSheet.Cells(lastRowNum, 1), 1, 1))

'Copy/paste only if there is at least one cell
If lastRowNum < rowNum And startLetter = currentLetter Then
ActiveSheet.Range(Cells(lastRowNum, 1), Cells(rowNum - 1, 1)).Select
Selection.Copy

'Create new sheet and paste
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = currentLetter
Worksheets(currentLetter).Activate
ActiveSheet.Range("A1").Select
ActiveSheet.Paste

lastRowNum = rowNum
Else
rowNum = rowNum - 1
End If

Next i

Worksheets(mainSheetName).Activate 'Activate the main sheet
Range("A1").Select

End Sub

posted by thewildgreen at 4:21 PM on January 28, 2011

Response by poster: Thank you thewildgreen - wow at the complexity though.

When I run this, I get an error after it has done the A's & B's - with the line

Do
"rowNum = rowNum + 1"


and instead of C's, and the rest, I get a blank sheet - any thoughts as to why this might be happening at all?

Thank you for your help and answers all!
posted by DrtyBlvd at 1:14 PM on January 29, 2011

Response by poster: thewildgreen graciously told me to change the line at top;

Dim rowNum As Integer
Dim lastRowNum As Integer

...by replacing 'As Integer' with 'As Long' , on account of my having too many lines - I reached the limit of 'integer', apparently.

Result! Instant 26 pages with alpha breakdown.

As I said to thewildgreen,

*I bow to the Excel God* :)

Thank you.
posted by DrtyBlvd at 10:25 AM on January 31, 2011