PDA

View Full Version : [SOLVED] Transpose data from row into columns based upon certain criteria



VBXDUMB
08-13-2013, 01:44 AM
Hi everyone, Im using excel 2007 and new to excel vba and i need help in writing a macro.
I dont know how to explain my problem clearly hopefully the information below will help

In worksheet 1 there is data comprising as stated below:

A (trial) B(arena) C D(treatment) E(distance) F(velocity) G(time)
trial 1 1 start 0:00:20 vehicle 47.23 6.24 7.56
trial 1 1 0:00:20-0:00:40 vehicle 169.08 8.45 20
trial 1 1 0:00:40 - 0:00:60 vehicle
.
.
. 1:50:00-1:50:20
trial 1 2 start 0:00:20 saline 47.23 6.24 7.56
trial 1 2
trial 1 2
.
.
.
.

what i want in worksheet 2 is:

A
trial 1 1 1 .............. 2 2 ............ 3
arena 1 2 3 ..............1 2 ............1
treatment vehicle vehicle saline ..............saline vehicle ............am
start 0:00:20 47.23
0:00:20-0:00:40
0:00:40-0:00:60
:
:
1:29:40-1:30:00


In worksheet 1

> each start-0:00:20 represent start of new arena
> each time interval is 20 seconds
> the data to be copied is distance which is in column E but the only be copied if the time value in column F is 20 setting the value of distance that fulfill the condition of time =20 as the new start value
> Total of 90 cells need to be copy paste into sheet 2 taking the first value that met the condition as mentioned above.
> Repeated for all the arenas.

Sorry if my explainations are not clear

stanleydgrom
08-13-2013, 04:45 PM
VBXDUMB,

Welcome to the VBA Express forum.

What version of Excel are you using?

To get the most precise answer, it is best to upload/attach a sample workbook (sensitive data changed) that contains an example of your raw data on one worksheet, and on another worksheet your desired results.

The structure and data types of the sample workbook must exactly duplicate the real workbook. Include a clear and explicit explanation of your requirements.

To attach your workbook, scroll down and click on the Go Advanced button, then scroll down and click on the Manage Attachments button.

VBXDUMB
08-13-2013, 06:26 PM
Hi fren, I have attach the sample file hope you can help me sort this problem.

The cells highlighted with yellow is the starting and ending point data that need to be copied.
the red remarks the data that not needed.
Thank you for your kindness to help me....: pray2:

stanleydgrom
08-14-2013, 03:57 AM
VBXDUMB,

Thanks for the workbook.

I have not been able to determine the logic to go from worksheet Day1 Raw to worksheet Day1 Rearrange.

Click on the Reply to Thread button, and just put the word BUMP in the post. Then, click on the Post Quick Reply button, and someone else will assist you.

VBXDUMB
08-14-2013, 05:55 PM
BUMP

Doug Robbins
08-14-2013, 11:45 PM
The following code works to the point in the Day1 Raw data where the #VALUE! appears in Column H, inserting the data into a Sheet with the Tab of "Result"

[CODE][Dim i As Long, j As Long, k As Long
Dim arrData As Variant
Dim lngArena As Long
Dim lngTrial As Long
Dim strTreatment As String
Dim Dist As Single
Dim dur As Long
Dim t1 As String
Dim t1sec As String
Dim t1min As String
Dim t2min As String
Dim t1hr As String
Dim t2hr As String
Dim t2 As String
Dim t2Sec As String
With Sheets("Result").Range("A1")
.Offset(0, 0) = "Trial"
.Offset(1, 0) = "Arena"
.Offset(2, 0) = "Treatment"
.Offset(3, 0) = "Start-0:00:20"
For i = 1 To 269
Select Case i Mod 3
Case 1
t1sec = "20"
t2Sec = "40"
Case 2
t1sec = "40"
t2Sec = "00"
Case Else
t1sec = "00"
t2Sec = "20"
End Select
Select Case Int(i / 3)
Case Is < 60
t1min = Format(Int(i / 3), "00")
t1hr = "0"
If t2Sec = "00" Then
t2min = Format(Val(t1min) + 1, "00")
If t2min = "60" Then
t2min = "00"
t2hr = "1"
Else
t2hr = "0"
End If
Else
t2min = t1min
End If
Case Else
t1min = Format(Int(i / 3) - 60, "00")
t1hr = "1"
If t2Sec = "00" Then
t2min = Format(Val(t1min) + 1, "00")
Else
t2min = t1min
End If
End Select
.Offset(i + 3, 0) = t1hr & ":" & t1min & ":" & t1sec & "-" & t2hr & ":" & t2min & ":" & t2Sec
Next i
End With
With Sheets("Day1 Raw").Range("A5")
arrData = .CurrentRegion
lngArena = .Offset(0, 1)
lngTrial = Val(Right(.Offset(0, 0), 1))
strTreatment = .Offset(0, 3)
j = 1
k = 3
For i = 1 To UBound(arrData, 1)
If Val(Right(arrData(i, 1), 1)) = lngTrial Then
Sheets("Result").Range("A1").Offset(0, j) = lngTrial
If arrData(i, 2) = lngArena Then
Sheets("Result").Range("A1").Offset(1, j) = lngArena
Sheets("Result").Range("A1").Offset(2, j) = strTreatment
If Format(.Offset(i - 1, 7)) = "20" Then
Sheets("Result").Range("A1").Offset(k, j) = .Offset(i - 1, 5)
k = k + 1
End If
Else
lngArena = arrData(i, 2)
Sheets("Result").Range("A1").Offset(1, j) = lngArena
Sheets("Result").Range("A1").Offset(2, j) = strTreatment
j = j + 1
k = 3
If .Offset(i - 1, 7) = 20 Then
Sheets("Result").Range("A1").Offset(k, j) = .Offset(i - 1, 5)
k = k + 1
End If
End If
Else
lngTrial = Val(Right(arrData(i, 1), 1))
lngArena = arrData(i, 2)
Sheets("Result").Range("A1").Offset(1, j) = lngArena
Sheets("Result").Range("A1").Offset(2, j) = strTreatment
j = j + 1
k = 3
If .Offset(i - 1, 7) = 20 Then
Sheets("Result").Range("A1").Offset(k, j) = .Offset(i - 1, 5)
k = k + 1
End If
End If
Next i
End With
/CODE]

It would also crash on the cells containing #DIV/0! in column H.

Further, there is no apparent logic for the exclusion of the cells highlighted in Red.

snb
08-15-2013, 02:13 AM
I'd use


Sub M_snb()
On Error Resume Next
sn = Sheets("Day1 Rearrange").Cells(1).CurrentRegion
sp = Application.Index(sn, 0, 1)
sq = Sheets("Day1 Raw").Cells(5, 1).CurrentRegion.Resize(, 7)

For j = 1 To UBound(sq)
If Val(sq(j, 6)) <> 0 Then sn(Application.Match(sq(j, 3), sp, 0), 12 * (Val(Mid(sq(j, 1), 6)) - 1) + 1 + sq(j, 2)) = sq(j, 6)
Next

Sheet3.Cells(1).Resize(UBound(sn), UBound(sn, 2)) = sn
End Sub

VBXDUMB
08-15-2013, 10:18 PM
Hi Doug Robbins,

Thank you very for your code but the problem is it stop running and shows run time error 13 "type mismatch"

the code that i have problem with is "If Format(.Offset(i - 1, 7)) = "20" Then"

And hopefully you will let me know what the code is doing? I'm probably not as proficient a VBA programmer as you are assuming!


Hi snb, thank you for your concern too but when i run the code there is nothing happened....hopefully you can guide me through the code....

snb
08-16-2013, 02:09 AM
remove the empty column A in 'Day 1 Rearrange'
Empty the data in sheet 'Day 1 Rearrange' first.

Doug Robbins
08-16-2013, 02:31 AM
The Run time error 13 "type mismatch" is caused by the #VALUE that appears in cell H107 of the Day1 Raw sheet.

Note that I prefaced my response with the statement "The following code works to the point in the Day1 Raw data where the #VALUE! appears in Column H"

I also made the following observations:

"It would also crash on the cells containing #DIV/0! in column H."

and

"Further, there is no apparent logic for the exclusion of the cells highlighted in Red."

Doubtless SNB may be back with some further information, but it appears to me that his code relies on the existence of a populated Day1 Rearrange Sheet and it produces a replica of it on a third worksheet.

I am not sure that it does any more than what the following code will do, albeit quite bit more speedily, but I am happy to be corrected on that:


Dim i As Long, j As Long
Dim arrData As Variant
arrData = Sheets("Day1 Rearrange").Range("B1").CurrentRegion
For i = 1 To UBound(arrData, 1)
For j = 1 To UBound(arrData, 2)
Sheets("Result").Range("B1").Offset(i - 1, j - 1) = arrData(i, j)
Next j
Next i

snb
08-16-2013, 04:35 AM
@Doug

You missed my last post.

VBXDUMB
08-19-2013, 01:05 AM
@Doug Robbins and @snb, i need the vba code that will give me the results as in the
"Day1 Rearrange" sheet using the data obtained from the "Day1 Raw" sheet.

Doug Robbins
08-19-2013, 02:07 AM
The code that I have given you will do that EXCEPT for the errors in the raw data and\or your wanting to exclude some of the data without having provided the logic for doing so.

VBXDUMB
08-19-2013, 06:49 PM
hi @Doug Robbins, Thank you for your concern.

The conditions to work on this workbook is that,

I want to start copy the values in column F in "Day1 Raw" sheet into "Results" along with the trial,arena,treatment.

Each "Start-0:00:20" represent new arena.

Only start copy if the value in column H is 20.

Number of cells to be copy is 90 cells calculated starting from the cell that first have the value 20 in column H after the column C that have the "Start-0:00:20" value.

After 90 cells have been copied then paste it all as new range of "Start-0:00:20" till "0:29:40-0:30:00" in "Results" sheet.

Then repeat the procedure again for the same arena in "Day1 Raw" sheet for another 180 cells.

For this repeat,i have indicated in "Day1 Rearranged" sheets in sample workbook, for example: Arena 1, highlighted the cells with red which means it does not need to be copied. the yellow cell above the red region represent the last last cell which has been copied during the 90 cell copy process. Then the red region is not to be copied. and the another yellow cell after the red region is where it will start to be copied. i need 180 cells to be copied starting from where the value in column H is 20. Then continue to paste it in "Results "sheet after the "0:29:40 -0:30:00".

It has been more than 3 weeks i have been working with this but cant find any solutions.
Im really very sorry if i cant make my self clear here.
Hopefully u can help me to sort this out.

Doug Robbins
08-19-2013, 08:20 PM
OK, for Trial 1, Arena 1 where the Treatment is Vehicle, you process 90 rows starting with the first row in which column H contains 20, then you by-pass 17 rows and then process another 180 rows.

Then for Trial 1, Arena 2, where the Treatment is Amp1, you process 90 rows starting with the first row in which column H contains 20, then you by-pass 20 rows and then process another 180 rows.

Then for Trial 1, Arena 3, where the Treatment is Amp2, you process 90 rows starting with the first row in which column H contains 20, then you by-pass 24 rows and then process another 180 rows.

Then for Trial 1, Arena 4, where the Treatment is Amp3, you process 90 rows starting with the first row in which column H contains 20, then you by-pass 31 rows and then process another 180 rows.

Then for Trial 1, Arena 5, where the Treatment is Vehicle, you process 90 rows starting with the first row in which column H contains 20, then you by-pass ??

Where's the logic in the decision to by pass 17, 20, 24, 31, ?? rows

While the code that I have given you could be modified to deal with the situation where column G contains either 0 or - that causes the #DIV/0! or #VALUE! errors, which my code cannot handle at the moment, you will need to supply the logic for which rows are to be by-passed or you would need to pre-process the data to eliminate the rows that are to be bypassed.

Another illogical feature of what you are doing is that due to the by-passing of rows, the time intervals in column C of the Day1 Raw are out of sync with those in column B of Day1 Rearrange for the records that are processed after those that are by-passed.

VBXDUMB
08-21-2013, 12:12 AM
Owh ok @Doug Robbins, now i understand my mistake... im sorry for that....

And i need ur help to create VBA that can perform summation of 15 cells in each column untill all the values in each column are finish calculated. This should for every column .
The result that i mentioned above should be like the example of data in range AD24 : EF42 in sample workbook i attached above. Hopefully you will help on this.: pray2: Its in sheet "Day1 Rearrange".