Solved: Reorganizing Data

[blumfeld0]

Hi. I have attached an excel file that contains one worksheet with as much detail as I can provide to aid in helping me reorganize data I have.
I would very much appreciate any help on this issue. Thank you very much!!

[Bob Phillips]

Do the A's for 20th July come after the last 19th July etc.?

[blumfeld0]

Hello. Thank you for the question.
Each date has it own excel worksheet (or file). July19th has nothing to do with july20th etc.
each date has its own data set with letters potentially(but not neccesarily) ranging from A->Z
thanks!

[p45cal]

This macro works on the current selection so make sure you have the whole block of data selected before pressing Button 1 on the 'Copy' sheet in the attached file. It's rough and ready but it seems to work. Note that A-Z is 26 and you've got 8 columns with a spacing column between each which means if you have the whole gamut from A to Z you'll only have 22 columns to spare in versions of Excel up to Excel 2003.
The block of data doesn't have to start in column A, it will sort the data according to the 2nd column of the selection and place the data in line with the top row of the selection. The first set of data remains in place.
p45cal

the code:[vba]Sub blah()
Selection.Sort Key1:=Selection.Range("B4"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal
SelTopRow = Selection.Row
SelWidth = Selection.Columns.Count
batchno = 0
rw = 1
startrow = 1
Do Until rw > Selection.Rows.Count
Do Until Selection.Cells(startrow, 2) <> Selection.Cells(rw + 1, 2)
rw = rw + 1
Loop
If batchno > 0 Then
Set mysource = Selection.Rows(startrow & ":" & rw)
Set mydest = Selection.Cells(1, 1).Offset(, batchno * (SelWidth + 1)).Resize(rw - startrow + 1, SelWidth)
mysource.Copy mydest
mysource.Clear
End If
rw = rw + 1: startrow = rw: batchno = batchno + 1
Loop
End Sub
[/vba]

[blumfeld0]

thank for your work on this.
i know excel ok. but i know very very little about macros and how to edit them save them and run them. in this way, i do not how to edit your code to accomadate my particular data. for example, i have many many more rows and letters.
is there any if statements or any other alternatives i can use instead?
thank you!!!

[p45cal]

The first words of my post were: "This macro works on the current selection" so it will work on more letters and a bigger area. It will work on as big an area as you select before you run it.

I can't think of an easier solution than the one I'm giving you.

Copy the code into your own workbook, select the area you want to convert then go to the standard toolbar and:
Tools|Macro|Macros, highlight the macro 'blah' and click 'Run'

To test it out on your own, larger, data, why not copy that data to the worksheet attached to my last post and see if it does what you want? That way you won't have to copy the code before you're sure it works.

I suggest you have a look at:
http://www.anthony-vba.kefra.com/vba/vbabasic1.htm
as an intro to macros (remember, you have posted to a group called VBA Express - which means macros!). Or Google for say "excel macro tutorial".

[blumfeld0]

thank you p45cal very much. i have implented your macro and it works wonderfully. i very much appreciate your help.
i just have a couple of small questions before i label this thread solved. this is addressed in the attached excel file "sample.xls"
thank you!

[p45cal]

i just have a couple of small questions before i label this thread solved

A few points:
1. It has in fact been solved and works exactly as you asked. You've moved the goal posts; in the first file you expressly say "I do not need a black colr separation for my final result" and in your more recent request you say the direct opposite. In the first file you don't even mention headers and in the second you introduce them and want them copied over too. The phrase 'milking it' comes to mind.
2. I don't give a monkey's whether it's labelled 'solved' or not.
3. You're using this forum as a free programming service, and it's working, however people helping you would normally want to see more evidence of you helping yourself.
OK. Moralistic rant over. High horse dismounted.

All your requests have been addressed in the attached file.

To introduce a button onto the sheet: Go to the standard toolbar and choose View|Toolbars and make sure that the 'Forms' toolbar IS checked, and also that the 'Control Toolbx' is NOT.
On the 'Forms' toolbar, click the button image then click on the sheet where you want the button to appear. Immediately, a dialogue box appears with the title 'Assign Macro', highlight 'blah' and click OK. Now click anywhere on the sheet to take the selection off the button. That's it.

I've added a bit of code at the beginning of the macro, which you can delete if you want. It tries to make the right selection for you by selecting the current region, (a region bound by completely blank columns and rows) but gives you the chance to adjust it if it gets it wrong, or to cancel. Just start the macro with a cell selected which is somewhere in the range you want to process. Here's the code so you can identify it:[vba]Selection.CurrentRegion.Select
On Error GoTo cancelled
Set UserRange = Application.InputBox("Confirm or adjust the range to process (include the headers)", "Confirm range to process", Selection.Address, , , , , 8)
UserRange.Select
On Error GoTo 0
[/vba]
It's an odd company you work with, sending you options chains like this. It's the sort of thing you can cut and paste off the internet.

[blumfeld0]

Thank you again p45cal. Sorry if I caused you any trouble and hope I did not anger you. I did not mean to.
Your help is very much appreciated.