Printable View
I have a list that needs to be transposed into 8 different tables depending on a variable in the name. Basically, I get sheet 1 from our data base and need to make it look like sheet 2. Some of the rows are not used at all. I think I need to use an array, but have never been taught how to use them. Any ideas, suggestions, teaching moments would be greatly appreciated.
Thanks!
I think that VBA would be a smarter way of creating the tables rather than array formulae, but would you care to share with us the rules that determine what goes into which table, I can't see anyone sitting down and trying to figure it out themselves.
I can see how an explanation would be helpful :blush. Each subject has 3 replicates (n=1,2,3) of each timepoint (0m, 5m, 10m etc) on sheet 1. Each timepoint has its own table on sheet 2. The table should be populated by subject 1 n 1,2,3 subject 2 n 1,2,3 across. Basically, I've been copying column A and paste transpose the first 12, then the next 12. The number of subjects and the number of replicates may change, but the timepoints will always be 0m,5m,10m etc). Please let me know if this makes sense.
Thank you!
Hoping someone can take a look at this again.
Thanks!
it stops half way through. Can someone get me back on the path?
[VBA]Sub CreatePlate()
Dim rcell As Range
Dim rData As Range
Dim stStudy As String
Dim n, A, B, C, D, E, F, G, H As Integer
Set rData = Worksheets(1).Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
'stStudy = InputBox("Study Number", , "TNJS") & " "
'rData.Replace What:=stStudy, Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
n = 1
A = 1
B = 1
C = 1
D = 1
E = 1
F = 1
G = 1
H = 1
For Each rcell In rData
If n < 13 And InStr(rcell.Value, " 0m") <> 0 Then
Worksheets(2).Cells(5, A + 1).Value = rcell.Value
n = n + 1
A = A + 1
ElseIf n < 25 And InStr(rcell.Value, " 0m") <> 0 Then
Worksheets(2).Cells(6, B + 1).Value = rcell.Value
n = n + 1
B = B + 1
ElseIf n < 37 And InStr(rcell.Value, " 0m") <> 0 Then
Worksheets(2).Cells(7, C + 1).Value = rcell.Value
n = n + 1
C = C + 1
ElseIf n < 49 And InStr(rcell.Value, " 0m") <> 0 Then
Worksheets(2).Cells(8, D + 1).Value = rcell.Value
n = n + 1
D = D + 1
ElseIf n < 61 And InStr(rcell.Value, " 0m") <> 0 Then
Worksheets(2).Cells(9, E + 1).Value = rcell.Value
n = n + 1
E = E + 1
ElseIf n < 73 And InStr(rcell.Value, " 0m") <> 0 Then
Worksheets(2).Cells(10, F + 1).Value = rcell.Value
n = n + 1
F = F + 1
ElseIf n < 85 And InStr(rcell.Value, " 0m") <> 0 Then
Worksheets(2).Cells(11, G + 1).Value = rcell.Value
n = n + 1
G = G + 1
ElseIf n < 97 And InStr(rcell.Value, " 0m") <> 0 Then
Worksheets(2).Cells(12, H + 1).Value = rcell.Value
n = n + 1
H = H + 1
End If
Next rcell
n = 1
A = 1
B = 1
C = 1
D = 1
E = 1
F = 1
G = 1
H = 1
For Each rcell In rData
If n < 13 And InStr(rcell.Value, " 5m") <> 0 Then
Worksheets(2).Cells(17, A + 1).Value = rcell.Value
n = n + 1
A = A + 1
ElseIf n < 25 And InStr(rcell.Value, " 5m") <> 0 Then
Worksheets(2).Cells(18, B + 1).Value = rcell.Value
n = n + 1
B = B + 1
ElseIf n < 37 And InStr(rcell.Value, " 5m") <> 0 Then
Worksheets(2).Cells(19, C + 1).Value = rcell.Value
n = n + 1
C = C + 1
ElseIf n < 49 And InStr(rcell.Value, " 5m") <> 0 Then
Worksheets(2).Cells(20, D + 1).Value = rcell.Value
n = n + 1
D = D + 1
ElseIf n < 61 And InStr(rcell.Value, " 5m") <> 0 Then
Worksheets(2).Cells(21, E + 1).Value = rcell.Value
n = n + 1
E = E + 1
ElseIf n < 73 And InStr(rcell.Value, " 5m") <> 0 Then
Worksheets(2).Cells(22, F + 1).Value = rcell.Value
n = n + 1
F = F + 1
ElseIf n < 85 And InStr(rcell.Value, " 5m") <> 0 Then
Worksheets(2).Cells(23, G + 1).Value = rcell.Value
n = n + 1
G = G + 1
ElseIf n < 97 And InStr(rcell.Value, " 5m") <> 0 Then
Worksheets(2).Cells(24, H + 1).Value = rcell.Value
n = n + 1
H = H + 1
End If
Next rcell
End Sub
[/VBA]