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!
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!
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
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
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
-- 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
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.
posted by thewildgreen at 4:21 PM on January 28, 2011
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
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
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
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
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
This thread is closed to new comments.
posted by Oddly at 11:54 AM on January 28, 2011