Big an complicated project

[Little_Ghost]

Hi eveybody,

I'm working on a big and complicated project. This type of project will return every so many months and I don't want to have to do this whole thing by hande every time. let me go into a little more detail:

I get a txt file supplied full of data (I think it's outputted from a database). I can get the data into excel in 2 neat columns but then the the problems starts.

The first column will be a code ranging from AA-0000 to ZZ-ZZZZ and everything in between (currently it's 104,085 entrys so it's spread out over 2 worksheets).
In the first column some blank lines, lines with '---' and lines with a header as well but those are irrelevant.
The second column will be just be some description.

How it eventually needs to be is that every row starting with an 'A' in column A has to go on a new sheet titled 'A'. Then every row starting with 'B' should be placed on a sheet titled 'B' etc etc. In the end you would have 26 sheets; one for every letter.

I tried so many options I don't even remember what I tried (or what not for that matter :S) and I'm getting realy frustrated with this whole project. I'm at my wits end on how to proceed (or start over) so all help is MORE then welcome!

Hope one of you people know what I could do or can help me out.

Kind regards,

- Little_Ghost

 

[Little_Ghost]

Little_Ghost,

What version of Excel are you using?

You will generally get much more help (and faster) in this forum if you can post your small samples (what you have and what you expect to achieve) directly in the forum.

To attach screenshots, see below in my Signature block: Post a screen shot with one of these:

If you are not able to give us screenshots, see below in my Signature block: You can upload your workbook to Box Net, and provide us with a link to your workbook.

We're using Excel Version 2002 (10.687.6870) SP3.
I've uploaded an example file which you can download here:
http://www.megaupload.com/?d=HCTSBNI0

Hope this helps you guys out :S

- Little_Ghost

 

[hiker95]

Little_Ghost,

I have tried 2 times to download your workbook from megaupload. It is not working.

You can upload your workbook to Box Net, mark the workbook for sharing, and provide us with a link to your workbook.

 

[Little_Ghost]

Little_Ghost,

I have tried 2 times to download your workbook from megaupload. It is not working.

You can upload your workbook to Box Net, mark the workbook for sharing, and provide us with a link to your workbook.

Weird.... let's try this:
http://www.box.net/shared/7sx8hto6rc1ib8irzefy

 

[hiker95]

Little_Ghost,

Thanks for the workbook.

currently it's 104,085 entrys so it's spread out over 2 worksheets

What are the actual worksheet names for the above?

Does you actual worksheets already have column C with the formula:
=LEFT(A1,1)

What is the actual last column used in the actual two worksheets?

 

[Little_Ghost]

Little_Ghost,

Thanks for the workbook.

What are the actual worksheet names for the above?

Does you actual worksheets already have column C with the formula:
=LEFT(A1,1)

What is the actual last column used in the actual two worksheets?

Worksheet names are not per-defined. I can tell people to call it 'sheet 1' and 'sheet 2' or whatever I'd like.
Currently the sheet is called 'error_codes_600a' and 'error_codes_600b'.
This ill change with release. So 600 will probably become 700. Wheter there are going to be steps in between (610, 657, whatever) I don't know yet but for the moment I will be using this so when there have to be changes done in the script that is not a problem. For future use I can (and will) just write a little manual on how to do things.

No, column C is empty. The 'first letter' macro should fill that in, getting it's information from column A.

There are 2 columns with data:
A, which holds the error code, and
B, which holds the description.

- Little_Ghost

 

[snoopyhr]

Just out of curiosity..... have you tried pivot table and then use the show pages option ?

 

[Little_Ghost]

Just out of curiosity..... have you tried pivot table and then use the show pages option ?

No.
I tried working with pivot tables a while a go but the just don't work for me.
I can't get the data I need out of those things :S

- Little_Ghost

 

[Little_Ghost]

I've just been playing around with the whole pivot table thing but it seems to be to much data for it.

It gives me the error: "A field in your source data has more unique items then can be used in a pivot table report. Microsoft Excel may not be able to create the report, or may create the report without the data from this field."
Also, it would create seperate pages (tested with small amout of data) but you would have a lot of unused and unwanted table "stuff" floating around.

The macro's still seem to be/generate the best option/outcome when the would work together :S

- Little_Ghost

 

[hiker95]

Little_Ghost,


Your two worksheets with the raw data must be the first two worksheets in your workbook (no natter what their names).


Sample raw data worksheets:

Excel Workbook
	A	B
1	AC-00C1	option 1
2	AC-00C0	option 2
3	AC-00BF	option 3
4	BC-00BE	option 4
5	BC-00BA	option 5
6	CC-00BB	option 6
7	DC-00BC	option 7
8	FC-00BD	option 8
9	GC-00C2	option 9
10	JC-00C3	option 10
11
error_codes_600a

Excel Workbook
	A	B
1	AC-00C1	option 11
2	AC-00C0	option 22
3	AC-00BF	option 33
4	BC-00BE	option 44
5	BC-00BA	option 55
6	CC-00BB	option 66
7	DC-00BC	option 77
8	FC-00BD	option 88
9	GC-00C2	option 99
10	JC-00C3	option 1010
11
error_codes_600b

After the macro in the individual worksheets:

Excel Workbook
	A	B
1	AC-00C1	option 1
2	AC-00C0	option 2
3	AC-00BF	option 3
4	AC-00C1	option 11
5	AC-00C0	option 22
6	AC-00BF	option 33
7
A

Excel Workbook
	A	B
1	BC-00BE	option 4
2	BC-00BA	option 5
3	BC-00BE	option 44
4	BC-00BA	option 55
5
B

Excel Workbook
	A	B
1	CC-00BB	option 6
2	CC-00BB	option 66
3
C

Excel Workbook
	A	B
1	DC-00BC	option 7
2	DC-00BC	option 77
3
D

Excel Workbook
	A	B
1	FC-00BD	option 8
2	FC-00BD	option 88
3
F

Excel Workbook
	A	B
1	GC-00C2	option 9
2	GC-00C2	option 99
3
G

Excel Workbook
	A	B
1	JC-00C3	option 10
2	JC-00C3	option 1010
3
J

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Option Base 1
Sub DistributeRows()
' hiker95, 09/20/2011
' http://www.mrexcel.com/forum/showthread.php?t=574321
Dim wA(), w As Long, r As Long
Dim ws As Worksheet, nr As Long
Application.ScreenUpdating = False
For w = 1 To 2 Step 1
  wA = Worksheets(w).Range("A1", Worksheets(w).Range("A" & Rows.Count).End(xlUp)).Resize(, 3).Value
  For r = 1 To UBound(wA)
    wA(r, 3) = Left(wA(r, 1), 1)
  Next r
  For r = 1 To UBound(wA)
    If Not Evaluate("ISREF(" & wA(r, 3) & "!A1)") Then Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = wA(r, 3)
    Set ws = Worksheets(wA(r, 3))
    nr = ws.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
    If nr = 2 And ws.Range("A1") = "" Then nr = 1
    ws.Range("A" & nr) = wA(r, 1)
    ws.Range("B" & nr) = wA(r, 2)
  Next r
  Erase wA
Next w
Worksheets(1).Activate
Application.ScreenUpdating = True
End Sub

Then run the DistributeRows macro.

 

[Little_Ghost]

Hiker,

It works like a charm yet there is 1 error that breaks the whole thing off:
= TST EVENT =============================================================================

I figured out that it is the '=' sign at the beginning that causes the problem but I don't know how to solve this (I get the biggest part of the code but that's where it stops I'm afraid).


- Little_Ghost