Extracting data from multiple sheets

[pthwaites]

Hi all,

I'm looking for some help with a time-consuming issue. I have some psycholinguistic data which is contained across numerous sheets. These are outputs from a program called Psychopy. I need to extract that some parts of that data from those sheets and insert it, transposed and in some cases having mathematical calculations applied to it, into another sheet.

An example is attached. What we have in the source sheets is one cue word (i.e. words which respondents saw) per row, and then responses (I words that respondents wrote) in the same row. For "brave" there were no responses; for "wish" the responses were "god" and "rich", for example. I need these to be transposed so that we have one cue and one response per row in the new table. In the next column in the output sheet we have "iteration". This can be left blank as it's easy to fill in manually. Then we've got RespN. This is the order in which this word was given in response to its cue, and it corresponds to the number in the "typed_word_" column in the source sheets. I.e. if a word received 7 responses, like "trip" in the source sheet, then there should be 7 rows for that cue, numbered 1-7 in the order of their occurrence.

Then we've got RespStartActual and RespEndActual. These correspond simply to the "Start_Time_" and "Submit_Time_" values for that response in the source sheet. Next is RespStartRelative and RespEndRelative. These reflect when the participant started and finished their response, relative to the time that they finished their previous response to that cue only. So the RespStartRelative and RespEndRelative values for the first response to each cue are exactly the same as the RespStartActual and RespEndActual values; but subsequent responses need to have these columns calculated: RespStartRelative = RespStartActual minus the RespEndActual of the previous response (e.g., in the output sheet, G4=E4-F3), and RespEndRelative = RespEndActual minus the RespEndActual of the previous response (e.g. H4=F4-F3). Again, this calculation ONLY needs to happen for the 2nd response and above to each cue. You'll note that the "Start_Time_" and "Submit_Time_" values in the source sheets are not straightforwardly ordered - we don't have "Start_Time_1", "Start_Time_2", "Start_Time_3" etc, and then "Submit_Time_1", "Submit_Time_2", "Submit_Time_3" etc, but instead the columns are bit jumbled. This is because the software only creates a new column when it needs it, so if the first response gets only two responses (as in the example), then we'll have "Start_Time_1", "Start_Time_2", "Submit_Time_1", "Submit_Time_2" before we get "Start_Time_3" etc. So whatever solution anyone might come up with needs to be able to handle that.

Then we have "date". That can straightforwardly be taken from the "date" column of the source sheet.

I'll say again - the data is contained in multiple sheets, so I need a solution that can pull data from different sources, either all at once or in batches, and place it into one target output sheet.

I don't know a huge amount about Excel, but I figure this is going to be a sizeable task because it seems (to me at least) to be pretty complex, so if this is more than anyone would work on on a voluntary basis, maybe someone could point me in the direction of a paid service where I could get this done without too much expense?

Thanks a lot,

Peter

Source sheet:

Source sheet example.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG
1	prompt	trials.thisRepN	trials.thisTrialN	trials.thisN	trials.thisIndex	typed_word_1	typed_word_2	typed_word_3	typed_word_4	typed_word_5	typed_word_6	typed_word_7	start_time_1	start_time_2	submit_time_1	submit_time_2	start_time_3	start_time_4	start_time_5	start_time_6	submit_time_3	submit_time_4	submit_time_5	submit_time_6	start_time_7	start_time_8	submit_time_7	participant	session	date	expName	psychopyVersion	frameRate
2	brave	0	0	0	3																								1	2019_Sep_03_1124	ContinuousWA	3.1.5	60.0766634
3	wish	0	1	1	2	god	rich						0.01617486	11.6916984	7.705510337	12.34210383													1	2019_Sep_03_1124	ContinuousWA	3.1.5	60.0766634
4	raise	0	2	2	1	animal	food	house	mother	father	weakness		2.98391167	7.18805352	5.185717672	8.28921192	14.8627525	16.2471282	18.0322905	24.2717898	15.71277508	17.23095964	18.8161858	26.47368445					1	2019_Sep_03_1124	ContinuousWA	3.1.5	60.0766634
5	ahead	0	3	3	4	forsee							2.89918864		5.701968404														1	2019_Sep_03_1124	ContinuousWA	3.1.5	60.0766634
6	trip	0	4	4	0	travel	hardness	landscape	hotel	friends	family	cousin	1.53252655	4.51881193	3.60033316	6.570271207	7.95500562	14.646274	20.6016955	26.4073844	10.54083904	18.29875993	23.5871669	28.55894321	28.8428639	34.0319017	29.7766795		1	2019_Sep_03_1124	ContinuousWA	3.1.5	60.0766634
ContinuousWA_2019_S

Output sheet:

Output sheet Example.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Cue	Response	Iteration	RespN	RespStartActual	RespEndActual	RespStartRelative	RespEndRelative	Date
2	brave		1						2019_Sep_03_1124
3	wish	god	1	1	0.016174863	7.705510337	0.016174863	7.705510337	2019_Sep_03_1124
4	wish	rich	1	2	11.69169836	12.34210383	3.986188023	4.636593493	2019_Sep_03_1124
5	raise	animal	1	1	2.983911672	5.185717672	2.983911672	5.185717672	2019_Sep_03_1124
6	raise	food	1	2	7.188053521	8.28921192	2.002335849	3.103494248	2019_Sep_03_1124
7	raise	house	1	3	14.86275253	15.71277508	6.57354061	7.42356316	2019_Sep_03_1124
8	raise	mother	1	4	16.24712819	17.23095964	0.53435311	1.51818456	2019_Sep_03_1124
9	raise	father	1	5	18.03229053	18.8161858	0.80133089	1.58522616	2019_Sep_03_1124
10	raise	weakness	1	6	24.27178981	26.47368445	5.45560401	7.65749865	2019_Sep_03_1124
11	ahead	forsee	1	1	2.899188643	5.701968404	2.899188643	5.701968404	2019_Sep_03_1124
12	trip	travel	1	1	1.532526553	3.60033316	1.532526553	3.60033316	2019_Sep_03_1124
13	trip	hardness	1	2	4.518811932	6.570271207	0.918478772	2.969938047	2019_Sep_03_1124
14	trip	landscape	1	3	7.955005623	10.54083904	1.384734416	3.970567833	2019_Sep_03_1124
15	trip	hotel	1	4	14.64627402	18.29875993	4.10543498	7.75792089	2019_Sep_03_1124
16	trip	friends	1	5	20.60169549	23.5871669	2.30293556	5.28840697	2019_Sep_03_1124
17	trip	family	1	6	26.40738439	28.55894321	2.82021749	4.97177631	2019_Sep_03_1124
18	trip	cousin	1	7	28.84286387	29.7766795	0.28392066	1.21773629	2019_Sep_03_1124
19	travel	trip	2	1	2.5091412	4.1923603	2.5091412	4.1923603	2019_Sep_05_1108
20	travel	family	2	2	5.7099127	8.4758296	1.5175524	4.2834693	2019_Sep_05_1108
21	travel	food	2	3	9.1260952	10.475894	0.6502656	2.0000644	2019_Sep_05_1108
22	travel	mountain	2	4	10.9928356	14.7581385	0.5169416	4.2822445	2019_Sep_05_1108
23	travel	river	2	5	15.3926713	17.3088049	0.6345328	2.5506664	2019_Sep_05_1108
24	travel	experience	2	6	19.0255601	22.875127	1.7167552	5.5663221	2019_Sep_05_1108
25	travel	church	2	7	24.4924736	26.3078907	1.6173466	3.4327637	2019_Sep_05_1108
26	father	favorite	2	1	5.0331644	8.7321527	5.0331644	8.7321527	2019_Sep_05_1108
27	father	love	2	2	11.0500298	12.8991182	2.3178771	4.1669655	2019_Sep_05_1108
28	father	considerable	2	3	15.5329469	19.6990097	2.6338287	6.7998915	2019_Sep_05_1108
29	father	kind	2	4	21.1161565	23.699029	1.4171468	4.0000193	2019_Sep_05_1108
30	father	learnable	2	5	25.3991698	32.1144219	1.7001408	8.4153929	2019_Sep_05_1108
31	father	insurance	2	6	34.5321865	38.1147891	2.4177646	6.0003672	2019_Sep_05_1108
32	family	mother	2	1	2.0174789	3.6023359	2.0174789	3.6023359	2019_Sep_05_1108
Sheet1

Cell Formulas
Range	Formula
G4,G27:G31,G20:G25,G13:G18,G6:G10	G4	=E4-F3
H4,H27:H31,H20:H25,H13:H18,H6:H10	H4	=F4-F3

 

[DanteAmor]

Hi Peter:

Extracting data with added sheet.xlsm
	AA	AB	AC	AD	AE	AF
1	submit_time_6	submit_time_7	typed_word_9	typed_word_10	start_time_9	start_time_10
Sheet2

On sheet2, in columns AC you have mixed the columns "typed_word" with the columns "start_time" and "submit_time" and you did not mention that in the original requirement.
I do not know your data, I only follow your instructions, but if you do not mention how your data is, then obviously the macro simply will not work.
Done, I modified the code to search "word", "start" and "submit" without order.

Try this:

Sub Extracting_data()
  Dim arr As Variant, w As Variant, a() As Variant, RespN As Variant
  Dim sh As Worksheet, f As Range, vacio As Boolean
  Dim i As Long, j As Long, k As Long, lr As Long, lc As Long, m As Long, n As Long
  Dim colW As Long, colD As Long
 
  arr = Array("Sheet1", "Sheet2")
  Sheets("Output").Range("A2:I" & Rows.Count).ClearContents
 
  For Each w In arr
    Set sh = Sheets(w)
    Erase a
    lr = sh.Range("A" & Rows.Count).End(3).Row
    lc = sh.Cells(1, Columns.Count).End(1).Column
    a = sh.Range("A1", sh.Cells(lr, lc)).Value2
   
    colW = WorksheetFunction.CountIf(sh.Rows(1), "typed_word*")
    Set f = sh.Range("A1", sh.Cells(1, lc)).Find("date", , xlValues, xlPart, xlByColumns, xlPrevious, False)
    colD = f.Column
    ReDim b(1 To colW * (lr - 1), 1 To 9)
    k = 1
    For i = 2 To UBound(a, 1)
      RespN = ""
      b(k, 1) = a(i, 1)           'Cue
      b(k, 9) = a(i, colD)        'Date
      vacio = True
      For j = 1 To colW
        Set f = sh.Rows(1).Find("typed_word_" & j, , xlValues, xlWhole)
        If Not f Is Nothing Then
          If sh.Cells(i, f.Column) <> "" Then
            RespN = Val(RespN) + 1
            b(k, 1) = a(i, 1)     'Cue
            b(k, 9) = a(i, colD)  'Date
            b(k, 4) = RespN       'RespN
            '
            For m = 6 To lc
              Select Case LCase(a(1, m))
                Case LCase("typed_word_") & RespN:  b(k, 2) = a(i, m)
                Case LCase("start_time_") & RespN:  b(k, 5) = a(i, m)
                Case LCase("submit_time_") & RespN: b(k, 6) = a(i, m)
              End Select
            Next m
            '
            If RespN = 1 Then
              b(k, 7) = b(k, 5)
              b(k, 8) = b(k, 6)
            Else
              b(k, 7) = b(k, 5) - b(k - 1, 6)
              b(k, 8) = b(k, 6) - b(k - 1, 6)
            End If
            '
            k = k + 1
            vacio = False
          Else
            If vacio Then k = k + 1
            Exit For
          End If
        End If
      Next j
    Next i
    Sheets("Output").Range("A" & Rows.Count).End(3)(2).Resize(k, 9).Value = b
  Next w
  '
  MsgBox "Done"
End Sub

It is important that the column structures retain what you mentioned at the beginning. That is, if you decide to put a sheet with the "date" in the first column, in the second column a "word", then "start", then 20 empty columns, then "submit", then combined cells. You need to comment on it.

 

[pthwaites]

Thank you SO much for this Dante Amor. You've saved me dozens of hours of work. The sheet now works perfectly. Apologies for the oversight on the second sheet. The source sheets were all automatically generated by the software I was using, and vary as a result of the participants' actions during their tests. But I should have checked them more closely before I posted my issue. Sorry for the inconvenience, and many thanks once again.

Peter

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.