Splitting data from one sheet into 2 other sheets. Data is skewed

[afaust]

Hi,

I have a problem with a large data file, and I'd like to get some help. I
believe I require an array to be able to separate the data the way I want it,
however, I'm not quite sure on how to go about it.

The way the data is set ip is like this;

Column A - Date and hour (data comes in every hour)
Column's B-F Hard or Soft (rows could be all hard, or all soft, or any
combination of the 2 words)
Columns G-AL Data points referring to columns B-F. ie
Link column B(row) to range G(row)-M(row)
link column C(row) to range N(row)-R(row)
link column D(row) to range S(row)-X(row)
link column E(row) to range Y(row)-AD(row)
link column F(row) to range AE(row)-AL(row)

What I need is to take column A (date-time), verify columns b-f to see if
they are hard or soft, and transfer the linked data to sheet hard or sheet
soft.....
example 12/04/08 11:00 hard hard soft soft hard (data points from column G
to column AL) i would transfer column a to both hard and soft sheets (i need
the date and time) and transfer ranges G-M, N-R and AE-AL to the hard sheet
and the ranges S-X and Y-AD to the soft sheet on the same line. Having to go
through over 12000 lines of data in this way is just too difficult for me at
the moment. Thank you for any help that can be offered.

Allan

 

[Bernie Deitrick]

Allan,

Try the macro below. The assumption that I've made is that any value in columns B-F, row 2 onward
are valid sheet names (if the cell is not blank), and that your sheet with the data is named "Data
Sheet"

HTH,
Bernie
MS Excel MVP



Option Explicit
Sub TryNow()
'Column A - Date and hour (data comes in every hour)
'Column 's B-F Hard or Soft (rows could be all hard, or all soft, or any
'combination of the 2 words)
'Columns G-AL Data points referring to columns B-F. ie
'Link column B(row) to range G(row)-M(row)
'link column C(row) to range N(row)-R(row)
'link column D(row) to range S(row)-X(row)
'link column E(row) to range Y(row)-AD(row)
'link column F(row) to range AE(row)-AL(row)

Dim myR As Long
Dim myRow As Long
Dim myCol As Integer
Dim mySht As String
Dim myCols As String

With Worksheets("Data Sheet")
For myR = 2 To .Cells(Rows.Count, 2).End(xlUp).Row
For myCol = 2 To 6
mySht = .Cells(myR, myCol).Value
If mySht = "" Then GoTo BlankCell
myRow = Sheets(mySht).Cells(Rows.Count, 1).End(xlUp)(2).Row
Sheets(mySht).Cells(myRow, 1).Value = .Cells(myR, 1).Value
myCols = Application.WorksheetFunction.Choose _
(myCol - 1, "G:M", "N:R", "S:X", "Y:AD", "AE:AL")
Sheets(mySht).Cells(myRow, 1).Value = .Cells(myR, 1).Value
Sheets(mySht).Cells(myRow, 2).Resize(, Range(myCols).Columns.Count).Value = _
Intersect(.Cells(myR, 1).EntireRow, .Range(myCols)).Value
BlankCell:
Next myCol
Next myR
End With

End Sub

 

[afaust]

Beautiful.... thank you very much... it works great, however, there is one
small problem. The data points are references to other objects, and having a
new line for each of the references moves the data out of those reference
points.... ie the data in columns G-M at 11:00 on 12/01/08 should be in
column G-M on sheet hard on the same line as the date/time and on the same
line as the hard data from 11:00 on 12/01/08 in columns n-r.
Just in case, to show it better;
Data was;

12/01/08 11:00 hard hard hard soft hard 1 2 3 4 5 6 7 8 9 10(whereby 1 and 2
would be reffed to the first hard 3 and for to 2nd for the purposes of
explanation) and the macro converts that to

12/01/08 11:00 1 2
12/01/08 11:00 3 4
etc
and it should be;
12/01/08 11:00 1 2 3 4 5 6 - - 9 10
on one sheet and
12/01/08 11:00 - - - - - - 7 8 - -

because once this is all said and done, I need to take daily averages of the
different data references...
I really appreciate the help with this, just what you've done to date has
been a major help.....

Allan

 

[afaust]

And one thing that I'd like to do at the same time is make sure I understand
what was done;

Option Explicit

Why this?

Sub TryNow()
'Column A - Date and hour (data comes in every hour)
'Column 's B-F Hard or Soft (rows could be all hard, or all soft, or any
'combination of the 2 words)
'Columns G-AL Data points referring to columns B-F. ie
'Link column B(row) to range G(row)-M(row)
'link column C(row) to range N(row)-R(row)
'link column D(row) to range S(row)-X(row)
'link column E(row) to range Y(row)-AD(row)
'link column F(row) to range AE(row)-AL(row)

Dim myR As Long
Dim myRow As Long
Dim myCol As Integer
Dim mySht As String
Dim myCols As String

'arranging the variables so that some are numbers, some are text, right?

With Worksheets("Data") 'I changed this to data which is my main sheet

For myR = 5 To .Cells(Rows.Count, 2).End(xlUp).Row 'I changed this to 5 since it starts at row 5 due to headers.... so I take it this takes the number from 5 to essentially the last row (end up), but why the rows.count, 2? that part I don't understand...

For myCol = 2 To 6 'the reference to the columns hard and soft...
mySht = .Cells(myR, myCol).Value ' takes the value hard or soft and puts it into the value mySht
If mySht = "" Then GoTo BlankCell 'kind of an error loop in case the sheet doesn't exist?
myRow = Sheets(mySht).Cells(Rows.Count, 1).End(xlUp)(2).Row ' goes to sheet hard or soft and takes the next open line?
Sheets(mySht).Cells(myRow, 1).Value = .Cells(myR, 1).Value 'adds in the value of the first row ie time/date?

myCols = Application.WorksheetFunction.Choose _
(myCol - 1, "G:M", "N:R", "S:X", "Y:AD", "AE:AL")
Sheets(mySht).Cells(myRow, 1).Value = .Cells(myR, 1).Value
Sheets(mySht).Cells(myRow, 2).Resize(, Range(myCols).Columns.Count).Value = _
Intersect(.Cells(myR, 1).EntireRow, .Range(myCols)).Value

This part above is what I have the most trouble with.....

BlankCell: ' the error loop area and going to next value...
Next myCol
Next myR
End With

End Sub

The reason why I ask this, is because I want to understand it, and not just
ask questions to get stuff done for me... I understand the programming in
most cases, I just have a darn hard time to start something from scratch....
I'm in the process of picking up a couple of excel books to help me out with
this as I go along, and as I mentioned earlier, I really appreciate this help.

Allan

 

[Bernie Deitrick]

Allan,

Try this version. I will try to answer your questions in another message, in reply to that post.

HTH,
Bernie
MS Excel MVP

Sub TryNow2()
'Column A - Date and hour (data comes in every hour)
'Column 's B-F Hard or Soft (rows could be all hard, or all soft, or any
'combination of the 2 words)
'Columns G-AL Data points referring to columns B-F. ie
'Link column B(row) to range G(row)-M(row)
'link column C(row) to range N(row)-R(row)
'link column D(row) to range S(row)-X(row)
'link column E(row) to range Y(row)-AD(row)
'link column F(row) to range AE(row)-AL(row)

Dim myR As Long
Dim myRow As Long
Dim myCol As Integer
Dim mySht As String
Dim myCols As String

With Worksheets("Data Sheet")
For myR = 2 To .Cells(Rows.Count, 2).End(xlUp).Row
For myCol = 2 To 6
mySht = .Cells(myR, myCol).Value
If mySht = "" Then GoTo BlankCell
If Application.WorksheetFunction.CountIf(.Cells(myR, 2).Resize(, myCol - 1), mySht) > 1
Then
myRow = Sheets(mySht).Cells(Rows.Count, 1).End(xlUp).Row
Else
myRow = Sheets(mySht).Cells(Rows.Count, 1).End(xlUp)(2).Row
End If
myCols = Application.WorksheetFunction.Choose _
(myCol - 1, "G:M", "N:R", "S:X", "Y:AD", "AE:AL")
Sheets(mySht).Cells(myRow, 1).Value = .Cells(myR, 1).Value
Intersect(Sheets(mySht).Cells(myRow, 1).EntireRow, _
Sheets(mySht).Range(myCols).Offset(, -5)).Value = _
Intersect(.Cells(myR, 1).EntireRow, .Range(myCols)).Value
BlankCell:
Next myCol
Next myR
End With

End Sub

 

[Bernie Deitrick]

Allan,

See my replies in-line.

HTH,
Bernie
MS Excel MVP

And one thing that I'd like to do at the same time is make sure I understand
what was done;


Why this?

Option Explicit tells Excel to make sure that the variables are properly dimensioned. It is helpful
to do this so that if you have a variable, say, strSomeVariable that if you happen to type it in
strSomeVarable (note the misspelling), your mistake will be caught. The other thing is that if you
set up your variables as a mix of upper case and lower case, and get into the habit of always typing
them in lower case, Excel will convert the case of your typed variable to the dimensioned style, so
that you know absolutely that you typped it in correctly.

For example:

Dim myStr As String

If you type mystr Excel will convert it to myStr; if you type mystrr, then Excel will not convert it
and you will immediately be made aware of your typo.

Overall, just good programming practice.

'arranging the variables so that some are numbers, some are text, right?

Yes. You dimension the variables based on what you want to use them for: since myR and myRow will
be assigned the row number, I dimmed them as Long, since that is the type of number that Excel uses
for row numbers. Note that if I were certain that I would never have rows > the Integer limit
(32,768) then I could have used Integer as the type.

With Worksheets("Data") 'I changed this to data which is my main sheet

That was the correct change.

2 is column B, and .Cells(Rows.Count,2) is the bottom most cell in column B. End(xlUp) takes you up
to the last filled cell of column B, which allows your code to take into account the actual extent
of the data.

No. It prevents the code from trying to find a sheet without a name, which would create an error.
Conversely, I could have handled the error, but it is just as easy as preventing the error from
occuring. Of course, if I had handle the error, it would have also handled the error where, for
example, your value is misspelled or corresponds to a shee that doesn't exist - by not handling the
error automatically, you are made aware of the problem.

Exactly.

Yes.


This part above is what I have the most trouble with.....

Since your data is associated with different columns, and there is no easy to discern relationship
(like, the same number of columns of data - the first is 7 columns, the next 5...) I just hard coded
the column association.

The second line was a mistake, that was accidentally copied over again (but it does no harm, so you
wouldn't notice an effect)

The third line (and the fourth, though they are really one line because of the continuation
character (the space and underscore at the end of the third line) wer used to extract the associated
data from the columns where it resides (Intersect(.Cells(myR, 1).EntireRow, .Range(myCols)).Value) ,
and paste it into the cells starting in column B, expanded to the number of columns of data (the
resize part).


HTH,
Bernie
MS Excel MVP

 

[afaust]

If Application.WorksheetFunction.CountIf(.Cells(myR, 2).Resize(, myCol - 1),
mySht) > 1
Then

there is a syntax error somewhere on this line....

 

[Bernie Deitrick]

Allan,

That line word-wrapped, so you just need to put all three of those onto one line, by taking out the
linefeeds at the ends, or by putting in continuation characters.

If Application.WorksheetFunction.CountIf(.Cells(myR, 2). _
Resize(, myCol - 1), mySht) > 1 Then

HTH,
Bernie
MS Excel MVP

 

[afaust]

It's ok now... the then shifted to the next line..... once I put it back, it
was ok..... and just to mention.... absolutely fantastic.... exactly what I
wanted.... I really appreciate this help......

I'll go through that last part to see what was done, if my understanding is
ok with it.... thanx again...

Allan

 

[Bernie Deitrick]

absolutely fantastic.... exactly what I

wanted.... I really appreciate this help......

Great!

Thanks for letting me know....

Bernie
MS Excel MVP

 

[afaust]

I really appreciate the help.... I want to go through the macro later to see
if I got the logic down if you don't mind.... It might be only later in the
week due to other stuff I have going, but the point is to make sure I learn,
and not just take stuff for granted.....

Allan