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]

Try the following

If the headings on your sheets have this structure:
typed_word_1, typed_word_2, typed_word_3 and so on.
I want to know if the names of the headers contain the texts: "typed_word_"

start_time_1, start_time_2, start_time_3 and so on
submit_time_1, submit_time_2, an so on.
As you commented, the order doesn't matter, but I want to know if the names of the headers contain the texts: "start_time_", "submit_time_"

In this line of the macro put the names of the sheets that contain data:
arr = Array("Sheet12", "Sheet13")

In these lines of the macro put the name of the Output sheet:
Sheets("Output").Range("A2:I" & Rows.Count).ClearContents
Sheets("Output").Range("A" & Rows.Count).End(3)(2).Resize(k, 9).Value = b

Run the following code in a module.

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
  Dim colW As Long, colS As Long, colD As Long
 
  arr = Array("Sheet12", "Sheet13")
  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
   
    Set f = sh.Range("A1", sh.Cells(1, lc)).Find("typed_word", , xlValues, xlPart, xlByColumns, xlPrevious, False)
    colW = f.Column
    Set f = sh.Range("A1", sh.Cells(1, lc)).Find("submit_time", , xlValues, xlPart, xlByColumns, xlPrevious, False)
    colS = f.Column
    Set f = sh.Range("A1", sh.Cells(1, lc)).Find("date", , xlValues, xlPart, xlByColumns, xlPrevious, False)
    colD = f.Column
    ReDim b(1 To (colW - 5) * (lr - 1), 1 To 9)
    k = 1
    For i = 2 To UBound(a, 1)
      RespN = ""
      b(k, 1) = a(i, 1)
      b(k, 9) = a(i, colD)
      vacio = True
      For j = 6 To colW
        If a(i, j) <> "" Then
          RespN = Val(RespN) + 1
          b(k, 1) = a(i, 1)
          b(k, 9) = a(i, colD)
          b(k, 2) = a(i, j)
          b(k, 4) = RespN
          '
          For m = colW + 1 To colS
            If LCase(a(1, m)) = LCase("start_time_") & RespN Then
              b(k, 5) = a(i, m)
            End If
            If LCase(a(1, m)) = LCase("submit_time_") & RespN Then
              b(k, 6) = a(i, m)
            End If
          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
      Next j
    Next i
    Sheets("Output").Range("A" & Rows.Count).End(3)(2).Resize(k, 9).Value = b
  Next w
  '
  MsgBox "Done"
End Sub

 

[pthwaites]

Hi Dante Amor,

Many thanks indeed for this. With the code you've sent, I'm getting some of the right data but not everything. Below is a sample. You'll see that in some cases the response times etc are adding themsevles as new responses, nothing is being added to the RespStart (etc.) columns.

To answer your question about the column headers - they are all "typed_word_1", "typed_word_2" etc., never only "typed_word" or "typed_word_". Same with the headers start_time_1, start_time_2, submit_time_1, submit_time_2, and so on.

Thanks,

Peter

KMJun Responses.xlsm
	A	B	C	D	E	F	G	H	I	J
1
2	brave								brave 0 0 0 3 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
3	wish	god		1					wish 0 1 1 2 god rich 0.016174863 11.69169836 7.705510337 12.34210383 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
4	wish	rich		2			0	0	wish 0 1 1 2 god rich 0.016174863 11.69169836 7.705510337 12.34210383 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
5	raise	animal		1					raise 0 2 2 1 animal food house mother father weakness 2.983911672 7.188053521 5.185717672 8.28921192 14.86275253 16.24712819 18.03229053 24.27178981 15.71277508 17.23095964 18.8161858 26.47368445 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
6	raise	food		2			0	0	raise 0 2 2 1 animal food house mother father weakness 2.983911672 7.188053521 5.185717672 8.28921192 14.86275253 16.24712819 18.03229053 24.27178981 15.71277508 17.23095964 18.8161858 26.47368445 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
7	raise	house		3			0	0	raise 0 2 2 1 animal food house mother father weakness 2.983911672 7.188053521 5.185717672 8.28921192 14.86275253 16.24712819 18.03229053 24.27178981 15.71277508 17.23095964 18.8161858 26.47368445 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
8	raise	mother		4			0	0	raise 0 2 2 1 animal food house mother father weakness 2.983911672 7.188053521 5.185717672 8.28921192 14.86275253 16.24712819 18.03229053 24.27178981 15.71277508 17.23095964 18.8161858 26.47368445 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
9	raise	father		5			0	0	raise 0 2 2 1 animal food house mother father weakness 2.983911672 7.188053521 5.185717672 8.28921192 14.86275253 16.24712819 18.03229053 24.27178981 15.71277508 17.23095964 18.8161858 26.47368445 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
10	raise	weakness		6			0	0	raise 0 2 2 1 animal food house mother father weakness 2.983911672 7.188053521 5.185717672 8.28921192 14.86275253 16.24712819 18.03229053 24.27178981 15.71277508 17.23095964 18.8161858 26.47368445 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
11	ahead	forsee		1					ahead 0 3 3 4 forsee 2.899188643 5.701968404 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
12	trip	travel		1					trip 0 4 4 0 travel hardness landscape hotel friends family cousin 1.532526553 4.518811932 3.60033316 6.570271207 7.955005623 14.64627402 20.60169549 26.40738439 10.54083904 18.29875993 23.5871669 28.55894321 28.84286387 34.03190173 29.7766795 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
13	trip	hardness		2			0	0	trip 0 4 4 0 travel hardness landscape hotel friends family cousin 1.532526553 4.518811932 3.60033316 6.570271207 7.955005623 14.64627402 20.60169549 26.40738439 10.54083904 18.29875993 23.5871669 28.55894321 28.84286387 34.03190173 29.7766795 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
14	trip	landscape		3			0	0	trip 0 4 4 0 travel hardness landscape hotel friends family cousin 1.532526553 4.518811932 3.60033316 6.570271207 7.955005623 14.64627402 20.60169549 26.40738439 10.54083904 18.29875993 23.5871669 28.55894321 28.84286387 34.03190173 29.7766795 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
15	trip	hotel		4			0	0	trip 0 4 4 0 travel hardness landscape hotel friends family cousin 1.532526553 4.518811932 3.60033316 6.570271207 7.955005623 14.64627402 20.60169549 26.40738439 10.54083904 18.29875993 23.5871669 28.55894321 28.84286387 34.03190173 29.7766795 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
16	trip	friends		5			0	0	trip 0 4 4 0 travel hardness landscape hotel friends family cousin 1.532526553 4.518811932 3.60033316 6.570271207 7.955005623 14.64627402 20.60169549 26.40738439 10.54083904 18.29875993 23.5871669 28.55894321 28.84286387 34.03190173 29.7766795 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
17	trip	family		6			0	0	trip 0 4 4 0 travel hardness landscape hotel friends family cousin 1.532526553 4.518811932 3.60033316 6.570271207 7.955005623 14.64627402 20.60169549 26.40738439 10.54083904 18.29875993 23.5871669 28.55894321 28.84286387 34.03190173 29.7766795 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
18	trip	cousin		7			0	0	trip 0 4 4 0 travel hardness landscape hotel friends family cousin 1.532526553 4.518811932 3.60033316 6.570271207 7.955005623 14.64627402 20.60169549 26.40738439 10.54083904 18.29875993 23.5871669 28.55894321 28.84286387 34.03190173 29.7766795 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
19	trip	1.532527		8			0	0	trip 0 4 4 0 travel hardness landscape hotel friends family cousin 1.532526553 4.518811932 3.60033316 6.570271207 7.955005623 14.64627402 20.60169549 26.40738439 10.54083904 18.29875993 23.5871669 28.55894321 28.84286387 34.03190173 29.7766795 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
20	trip	4.518812		9			0	0	trip 0 4 4 0 travel hardness landscape hotel friends family cousin 1.532526553 4.518811932 3.60033316 6.570271207 7.955005623 14.64627402 20.60169549 26.40738439 10.54083904 18.29875993 23.5871669 28.55894321 28.84286387 34.03190173 29.7766795 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
21	trip	3.600333		10			0	0	trip 0 4 4 0 travel hardness landscape hotel friends family cousin 1.532526553 4.518811932 3.60033316 6.570271207 7.955005623 14.64627402 20.60169549 26.40738439 10.54083904 18.29875993 23.5871669 28.55894321 28.84286387 34.03190173 29.7766795 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
22	trip	6.570271		11			0	0	trip 0 4 4 0 travel hardness landscape hotel friends family cousin 1.532526553 4.518811932 3.60033316 6.570271207 7.955005623 14.64627402 20.60169549 26.40738439 10.54083904 18.29875993 23.5871669 28.55894321 28.84286387 34.03190173 29.7766795 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
23	trip	7.955006		12			0	0	trip 0 4 4 0 travel hardness landscape hotel friends family cousin 1.532526553 4.518811932 3.60033316 6.570271207 7.955005623 14.64627402 20.60169549 26.40738439 10.54083904 18.29875993 23.5871669 28.55894321 28.84286387 34.03190173 29.7766795 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
24	trip	14.64627		13			0	0	trip 0 4 4 0 travel hardness landscape hotel friends family cousin 1.532526553 4.518811932 3.60033316 6.570271207 7.955005623 14.64627402 20.60169549 26.40738439 10.54083904 18.29875993 23.5871669 28.55894321 28.84286387 34.03190173 29.7766795 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
25	trip	20.6017		14			0	0	trip 0 4 4 0 travel hardness landscape hotel friends family cousin 1.532526553 4.518811932 3.60033316 6.570271207 7.955005623 14.64627402 20.60169549 26.40738439 10.54083904 18.29875993 23.5871669 28.55894321 28.84286387 34.03190173 29.7766795 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
26	trip	26.40738		15			0	0	trip 0 4 4 0 travel hardness landscape hotel friends family cousin 1.532526553 4.518811932 3.60033316 6.570271207 7.955005623 14.64627402 20.60169549 26.40738439 10.54083904 18.29875993 23.5871669 28.55894321 28.84286387 34.03190173 29.7766795 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
27	trip	10.54084		16			0	0	trip 0 4 4 0 travel hardness landscape hotel friends family cousin 1.532526553 4.518811932 3.60033316 6.570271207 7.955005623 14.64627402 20.60169549 26.40738439 10.54083904 18.29875993 23.5871669 28.55894321 28.84286387 34.03190173 29.7766795 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
28	trip	18.29876		17			0	0	trip 0 4 4 0 travel hardness landscape hotel friends family cousin 1.532526553 4.518811932 3.60033316 6.570271207 7.955005623 14.64627402 20.60169549 26.40738439 10.54083904 18.29875993 23.5871669 28.55894321 28.84286387 34.03190173 29.7766795 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
29	trip	23.58717		18			0	0	trip 0 4 4 0 travel hardness landscape hotel friends family cousin 1.532526553 4.518811932 3.60033316 6.570271207 7.955005623 14.64627402 20.60169549 26.40738439 10.54083904 18.29875993 23.5871669 28.55894321 28.84286387 34.03190173 29.7766795 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
30	trip	28.55894		19			0	0	trip 0 4 4 0 travel hardness landscape hotel friends family cousin 1.532526553 4.518811932 3.60033316 6.570271207 7.955005623 14.64627402 20.60169549 26.40738439 10.54083904 18.29875993 23.5871669 28.55894321 28.84286387 34.03190173 29.7766795 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
31	trip	28.84286		20			0	0	trip 0 4 4 0 travel hardness landscape hotel friends family cousin 1.532526553 4.518811932 3.60033316 6.570271207 7.955005623 14.64627402 20.60169549 26.40738439 10.54083904 18.29875993 23.5871669 28.55894321 28.84286387 34.03190173 29.7766795 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
32	trip	34.0319		21			0	0	trip 0 4 4 0 travel hardness landscape hotel friends family cousin 1.532526553 4.518811932 3.60033316 6.570271207 7.955005623 14.64627402 20.60169549 26.40738439 10.54083904 18.29875993 23.5871669 28.55894321 28.84286387 34.03190173 29.7766795 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
33	trip	29.77668		22			0	0	trip 0 4 4 0 travel hardness landscape hotel friends family cousin 1.532526553 4.518811932 3.60033316 6.570271207 7.955005623 14.64627402 20.60169549 26.40738439 10.54083904 18.29875993 23.5871669 28.55894321 28.84286387 34.03190173 29.7766795 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
34	trip	minjoon kim		23			0	0	trip 0 4 4 0 travel hardness landscape hotel friends family cousin 1.532526553 4.518811932 3.60033316 6.570271207 7.955005623 14.64627402 20.60169549 26.40738439 10.54083904 18.29875993 23.5871669 28.55894321 28.84286387 34.03190173 29.7766795 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
35	trip	1		24			0	0	trip 0 4 4 0 travel hardness landscape hotel friends family cousin 1.532526553 4.518811932 3.60033316 6.570271207 7.955005623 14.64627402 20.60169549 26.40738439 10.54083904 18.29875993 23.5871669 28.55894321 28.84286387 34.03190173 29.7766795 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
36	trip	2019_Sep_03_1124		25			0	0	trip 0 4 4 0 travel hardness landscape hotel friends family cousin 1.532526553 4.518811932 3.60033316 6.570271207 7.955005623 14.64627402 20.60169549 26.40738439 10.54083904 18.29875993 23.5871669 28.55894321 28.84286387 34.03190173 29.7766795 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
37	trip	ContinuousWA		26			0	0	trip 0 4 4 0 travel hardness landscape hotel friends family cousin 1.532526553 4.518811932 3.60033316 6.570271207 7.955005623 14.64627402 20.60169549 26.40738439 10.54083904 18.29875993 23.5871669 28.55894321 28.84286387 34.03190173 29.7766795 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
38	trip	3.1.5		27			0	0	trip 0 4 4 0 travel hardness landscape hotel friends family cousin 1.532526553 4.518811932 3.60033316 6.570271207 7.955005623 14.64627402 20.60169549 26.40738439 10.54083904 18.29875993 23.5871669 28.55894321 28.84286387 34.03190173 29.7766795 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
39	trip	60.07666		28			0	0	trip 0 4 4 0 travel hardness landscape hotel friends family cousin 1.532526553 4.518811932 3.60033316 6.570271207 7.955005623 14.64627402 20.60169549 26.40738439 10.54083904 18.29875993 23.5871669 28.55894321 28.84286387 34.03190173 29.7766795 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
40	trip	trip 0 4 4 0 travel hardness landscape hotel friends family cousin 1.532526553 4.518811932 3.60033316 6.570271207 7.955005623 14.64627402 20.60169549 26.40738439 10.54083904 18.29875993 23.5871669 28.55894321 28.84286387 34.03190173 29.7766795 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343		29			0	0	trip 0 4 4 0 travel hardness landscape hotel friends family cousin 1.532526553 4.518811932 3.60033316 6.570271207 7.955005623 14.64627402 20.60169549 26.40738439 10.54083904 18.29875993 23.5871669 28.55894321 28.84286387 34.03190173 29.7766795 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
41	travel	trip		1					2019_Sep_05_1108
42	travel	family		2			0	0	2019_Sep_05_1108
43	travel	food		3			0	0	2019_Sep_05_1108
44	travel	mountain		4			0	0	2019_Sep_05_1108
45	travel	river		5			0	0	2019_Sep_05_1108
46	travel	experience		6			0	0	2019_Sep_05_1108
47	travel	church		7			0	0	2019_Sep_05_1108
48	travel	counrt		8			0	0	2019_Sep_05_1108
49	travel	2.509141		9			0	0	2019_Sep_05_1108
50	travel	5.709913		10			0	0	2019_Sep_05_1108
51	travel	9.126095		11			0	0	2019_Sep_05_1108
52	travel	10.99284		12			0	0	2019_Sep_05_1108
53	travel	15.39267		13			0	0	2019_Sep_05_1108
54	travel	19.02556		14			0	0	2019_Sep_05_1108
55	travel	24.49247		15			0	0	2019_Sep_05_1108
56	travel	31.47543		16			0	0	2019_Sep_05_1108
57	travel	4.19236		17			0	0	2019_Sep_05_1108
58	travel	8.47583		18			0	0	2019_Sep_05_1108
59	travel	10.47589		19			0	0	2019_Sep_05_1108
60	travel	14.75814		20			0	0	2019_Sep_05_1108
61	travel	17.3088		21			0	0	2019_Sep_05_1108
62	travel	22.87513		22			0	0	2019_Sep_05_1108
63	travel	26.30789		23			0	0	2019_Sep_05_1108
64	father	favorite		1					2019_Sep_05_1108
65	father	love		2			0	0	2019_Sep_05_1108
66	father	considerable		3			0	0	2019_Sep_05_1108
67	father	kind		4			0	0	2019_Sep_05_1108
68	father	learnable		5			0	0	2019_Sep_05_1108
69	father	insurance		6			0	0	2019_Sep_05_1108
70	family	mother		1					2019_Sep_05_1108
71	family	brother		2			0	0	2019_Sep_05_1108
72	family	cousin		3			0	0	2019_Sep_05_1108
73	family	daughter		4			0	0	2019_Sep_05_1108
74	family	father		5			0	0	2019_Sep_05_1108
75	family	grandfather		6			0	0	2019_Sep_05_1108
76	family	lovely		7			0	0	2019_Sep_05_1108
77	family	similar		8			0	0	2019_Sep_05_1108
78	family	2.017479		9			0	0	2019_Sep_05_1108
79	family	4.683506		10			0	0	2019_Sep_05_1108
80	family	9.583209		11			0	0	2019_Sep_05_1108
81	family	14.01636		12			0	0	2019_Sep_05_1108
82	family	18.96622		13			0	0	2019_Sep_05_1108
83	family	22.04955		14			0	0	2019_Sep_05_1108
84	family	27.71578		15			0	0	2019_Sep_05_1108
85	family	34.14851		16			0	0	2019_Sep_05_1108
86	family	3.602336		17			0	0	2019_Sep_05_1108
87	family	6.849691		18			0	0	2019_Sep_05_1108
88	family	11.54869		19			0	0	2019_Sep_05_1108
89	family	17.19854		20			0	0	2019_Sep_05_1108
90	family	20.2651		21			0	0	2019_Sep_05_1108
91	family	25.73179		22			0	0	2019_Sep_05_1108
92	family	29.78145		23			0	0	2019_Sep_05_1108
93	hardness	difficult		1					2019_Sep_05_1108
94	god	church		1					2019_Sep_05_1108
95	god	religion		2			0	0	2019_Sep_05_1108
96	god	godness		3			0	0	2019_Sep_05_1108
97	god	christian		4			0	0	2019_Sep_05_1108
98	god	islam		5			0	0	2019_Sep_05_1108
99	god	love		6			0	0	2019_Sep_05_1108
100	god	jesus		7			0	0	2019_Sep_05_1108
101	god	trinity		8		31.79752	0	31.79752	2019_Sep_05_1108
102	god	2.266768		9	32.66539	37.29776	0.867868	5.500241	2019_Sep_05_1108
103	god	4.06529		10	37.73166		0.433903	-37.2978	2019_Sep_05_1108
104	god	8.683086		11			0	0	2019_Sep_05_1108
105	god	13.56613		12			0	0	2019_Sep_05_1108
106	god	18.49766		13			0	0	2019_Sep_05_1108
107	god	24.58215		14			0	0	2019_Sep_05_1108
108	god	26.99893		15			0	0	2019_Sep_05_1108
109	god	29.31538		16			0	0	2019_Sep_05_1108
110	god	3.782146		17			0	0	2019_Sep_05_1108
111	god	7.18134		18			0	0	2019_Sep_05_1108
112	god	11.4148		19			0	0	2019_Sep_05_1108
113	god	17.33142		20			0	0	2019_Sep_05_1108
114	god	21.91518		21			0	0	2019_Sep_05_1108
115	god	25.83084		22			0	0	2019_Sep_05_1108
116	god	28.76505		23			0	0	2019_Sep_05_1108
117	god	redemption		24			0	0	2019_Sep_05_1108
118	god	sa		25			0	0	2019_Sep_05_1108
119	hotel	house		1					2019_Sep_05_1108
120	hotel	landscape		2			0	0	2019_Sep_05_1108
121	hotel	sleep		3			0	0	2019_Sep_05_1108
122	hotel	food		4			0	0	2019_Sep_05_1108
123	hotel	breakfast		5			0	0	2019_Sep_05_1108
124	hotel	dinner		6			0	0	2019_Sep_05_1108
125	hotel	expensive		7			0	0	2019_Sep_05_1108
126	hotel	travel		8		35.66379	0	35.66379	2019_Sep_05_1108
127	hotel	1.549754		9			-35.6638	-35.6638	2019_Sep_05_1108
128	hotel	4.050198		10			0	0	2019_Sep_05_1108
129	hotel	7.416898		11			0	0	2019_Sep_05_1108
130	hotel	11.49999		12			0	0	2019_Sep_05_1108
131	hotel	13.56685		13			0	0	2019_Sep_05_1108
132	hotel	16.51644		14			0	0	2019_Sep_05_1108
133	hotel	20.93293		15			0	0	2019_Sep_05_1108
134	hotel	34.11556		16			0	0	2019_Sep_05_1108
135	hotel	3.433378		17			0	0	2019_Sep_05_1108
136	hotel	6.932722		18			0	0	2019_Sep_05_1108
137	hotel	9.216039		19			0	0	2019_Sep_05_1108
138	hotel	12.81527		20			0	0	2019_Sep_05_1108
139	hotel	16.16524		21			0	0	2019_Sep_05_1108
140	hotel	18.26445		22			0	0	2019_Sep_05_1108
141	hotel	26.93088		23			0	0	2019_Sep_05_1108
KMJ_Output_Master

 

[DanteAmor]

Many thanks indeed for this. With the code you've sent, I'm getting some of the right data but not everything. Below is a sample. You'll see that in some cases the response times etc are adding themsevles as new responses, nothing is being added to the RespStart (etc.) columns.

I do not understand what the problem is.

The test was done with the data you presented in post #1. Did you try that data?
Try with that data, the macro gives the same result that you put in post #1.
After doing that test and confirming that the macro delivers the results; try other data, you tell me what data you tried, what result you want and what results the macro delivered.

To answer your question about the column headers - they are all "typed_word_1", "typed_word_2" etc., never only "typed_word" or "typed_word_". Same with the headers start_time_1, start_time_2, submit_time_1, submit_time_2, and so on.

That's what I mean, that headings start with a text followed by a number. typed_word_# , start_time_#, submit_time_#.

 

[pthwaites]

Below is what I get when I run the VBA on the example data I uploaded before. The cues and responses are all in the right place and numbered correctly (up until row 18). But then from row 19 onwards, the Start_Time_ data has entered itself in the response column. It should not be here - Start_Time_# should be in column E and Submit_Time_# in Column F. Currently there is nothing in those columns.

Also, the value in Column I should be taken from Column AD of the source sheet, but it appears to contain the content of AH.

Thanks,

Peter

Source sheet example.xlsx
	A	B	C	D	E	F	G	H	I
2	brave								brave 0 0 0 3 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
3	wish	god		1					wish 0 1 1 2 god rich 0.016174863 11.69169836 7.705510337 12.34210383 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
4	wish	rich		2			0	0	wish 0 1 1 2 god rich 0.016174863 11.69169836 7.705510337 12.34210383 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
5	raise	animal		1					raise 0 2 2 1 animal food house mother father weakness 2.983911672 7.188053521 5.185717672 8.28921192 14.86275253 16.24712819 18.03229053 24.27178981 15.71277508 17.23095964 18.8161858 26.47368445 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
6	raise	food		2			0	0	raise 0 2 2 1 animal food house mother father weakness 2.983911672 7.188053521 5.185717672 8.28921192 14.86275253 16.24712819 18.03229053 24.27178981 15.71277508 17.23095964 18.8161858 26.47368445 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
7	raise	house		3			0	0	raise 0 2 2 1 animal food house mother father weakness 2.983911672 7.188053521 5.185717672 8.28921192 14.86275253 16.24712819 18.03229053 24.27178981 15.71277508 17.23095964 18.8161858 26.47368445 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
8	raise	mother		4			0	0	raise 0 2 2 1 animal food house mother father weakness 2.983911672 7.188053521 5.185717672 8.28921192 14.86275253 16.24712819 18.03229053 24.27178981 15.71277508 17.23095964 18.8161858 26.47368445 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
9	raise	father		5			0	0	raise 0 2 2 1 animal food house mother father weakness 2.983911672 7.188053521 5.185717672 8.28921192 14.86275253 16.24712819 18.03229053 24.27178981 15.71277508 17.23095964 18.8161858 26.47368445 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
10	raise	weakness		6			0	0	raise 0 2 2 1 animal food house mother father weakness 2.983911672 7.188053521 5.185717672 8.28921192 14.86275253 16.24712819 18.03229053 24.27178981 15.71277508 17.23095964 18.8161858 26.47368445 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
11	ahead	forsee		1					ahead 0 3 3 4 forsee 2.899188643 5.701968404 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
12	trip	travel		1					trip 0 4 4 0 travel hardness landscape hotel friends family cousin 1.532526553 4.518811932 3.60033316 6.570271207 7.955005623 14.64627402 20.60169549 26.40738439 10.54083904 18.29875993 23.5871669 28.55894321 28.84286387 34.03190173 29.7766795 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
13	trip	hardness		2			0	0	trip 0 4 4 0 travel hardness landscape hotel friends family cousin 1.532526553 4.518811932 3.60033316 6.570271207 7.955005623 14.64627402 20.60169549 26.40738439 10.54083904 18.29875993 23.5871669 28.55894321 28.84286387 34.03190173 29.7766795 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
14	trip	landscape		3			0	0	trip 0 4 4 0 travel hardness landscape hotel friends family cousin 1.532526553 4.518811932 3.60033316 6.570271207 7.955005623 14.64627402 20.60169549 26.40738439 10.54083904 18.29875993 23.5871669 28.55894321 28.84286387 34.03190173 29.7766795 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
15	trip	hotel		4			0	0	trip 0 4 4 0 travel hardness landscape hotel friends family cousin 1.532526553 4.518811932 3.60033316 6.570271207 7.955005623 14.64627402 20.60169549 26.40738439 10.54083904 18.29875993 23.5871669 28.55894321 28.84286387 34.03190173 29.7766795 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
16	trip	friends		5			0	0	trip 0 4 4 0 travel hardness landscape hotel friends family cousin 1.532526553 4.518811932 3.60033316 6.570271207 7.955005623 14.64627402 20.60169549 26.40738439 10.54083904 18.29875993 23.5871669 28.55894321 28.84286387 34.03190173 29.7766795 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
17	trip	family		6			0	0	trip 0 4 4 0 travel hardness landscape hotel friends family cousin 1.532526553 4.518811932 3.60033316 6.570271207 7.955005623 14.64627402 20.60169549 26.40738439 10.54083904 18.29875993 23.5871669 28.55894321 28.84286387 34.03190173 29.7766795 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
18	trip	cousin		7			0	0	trip 0 4 4 0 travel hardness landscape hotel friends family cousin 1.532526553 4.518811932 3.60033316 6.570271207 7.955005623 14.64627402 20.60169549 26.40738439 10.54083904 18.29875993 23.5871669 28.55894321 28.84286387 34.03190173 29.7766795 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
19	trip	1.532527		8			0	0	trip 0 4 4 0 travel hardness landscape hotel friends family cousin 1.532526553 4.518811932 3.60033316 6.570271207 7.955005623 14.64627402 20.60169549 26.40738439 10.54083904 18.29875993 23.5871669 28.55894321 28.84286387 34.03190173 29.7766795 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
20	trip	4.518812		9			0	0	trip 0 4 4 0 travel hardness landscape hotel friends family cousin 1.532526553 4.518811932 3.60033316 6.570271207 7.955005623 14.64627402 20.60169549 26.40738439 10.54083904 18.29875993 23.5871669 28.55894321 28.84286387 34.03190173 29.7766795 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
21	trip	3.600333		10			0	0	trip 0 4 4 0 travel hardness landscape hotel friends family cousin 1.532526553 4.518811932 3.60033316 6.570271207 7.955005623 14.64627402 20.60169549 26.40738439 10.54083904 18.29875993 23.5871669 28.55894321 28.84286387 34.03190173 29.7766795 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
22	trip	6.570271		11			0	0	trip 0 4 4 0 travel hardness landscape hotel friends family cousin 1.532526553 4.518811932 3.60033316 6.570271207 7.955005623 14.64627402 20.60169549 26.40738439 10.54083904 18.29875993 23.5871669 28.55894321 28.84286387 34.03190173 29.7766795 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
23	trip	7.955006		12			0	0	trip 0 4 4 0 travel hardness landscape hotel friends family cousin 1.532526553 4.518811932 3.60033316 6.570271207 7.955005623 14.64627402 20.60169549 26.40738439 10.54083904 18.29875993 23.5871669 28.55894321 28.84286387 34.03190173 29.7766795 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
24	trip	14.64627		13			0	0	trip 0 4 4 0 travel hardness landscape hotel friends family cousin 1.532526553 4.518811932 3.60033316 6.570271207 7.955005623 14.64627402 20.60169549 26.40738439 10.54083904 18.29875993 23.5871669 28.55894321 28.84286387 34.03190173 29.7766795 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
25	trip	20.6017		14			0	0	trip 0 4 4 0 travel hardness landscape hotel friends family cousin 1.532526553 4.518811932 3.60033316 6.570271207 7.955005623 14.64627402 20.60169549 26.40738439 10.54083904 18.29875993 23.5871669 28.55894321 28.84286387 34.03190173 29.7766795 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
26	trip	26.40738		15			0	0	trip 0 4 4 0 travel hardness landscape hotel friends family cousin 1.532526553 4.518811932 3.60033316 6.570271207 7.955005623 14.64627402 20.60169549 26.40738439 10.54083904 18.29875993 23.5871669 28.55894321 28.84286387 34.03190173 29.7766795 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
27	trip	10.54084		16			0	0	trip 0 4 4 0 travel hardness landscape hotel friends family cousin 1.532526553 4.518811932 3.60033316 6.570271207 7.955005623 14.64627402 20.60169549 26.40738439 10.54083904 18.29875993 23.5871669 28.55894321 28.84286387 34.03190173 29.7766795 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
28	trip	18.29876		17			0	0	trip 0 4 4 0 travel hardness landscape hotel friends family cousin 1.532526553 4.518811932 3.60033316 6.570271207 7.955005623 14.64627402 20.60169549 26.40738439 10.54083904 18.29875993 23.5871669 28.55894321 28.84286387 34.03190173 29.7766795 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
29	trip	23.58717		18			0	0	trip 0 4 4 0 travel hardness landscape hotel friends family cousin 1.532526553 4.518811932 3.60033316 6.570271207 7.955005623 14.64627402 20.60169549 26.40738439 10.54083904 18.29875993 23.5871669 28.55894321 28.84286387 34.03190173 29.7766795 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
30	trip	28.55894		19			0	0	trip 0 4 4 0 travel hardness landscape hotel friends family cousin 1.532526553 4.518811932 3.60033316 6.570271207 7.955005623 14.64627402 20.60169549 26.40738439 10.54083904 18.29875993 23.5871669 28.55894321 28.84286387 34.03190173 29.7766795 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
31	trip	28.84286		20			0	0	trip 0 4 4 0 travel hardness landscape hotel friends family cousin 1.532526553 4.518811932 3.60033316 6.570271207 7.955005623 14.64627402 20.60169549 26.40738439 10.54083904 18.29875993 23.5871669 28.55894321 28.84286387 34.03190173 29.7766795 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
32	trip	34.0319		21			0	0	trip 0 4 4 0 travel hardness landscape hotel friends family cousin 1.532526553 4.518811932 3.60033316 6.570271207 7.955005623 14.64627402 20.60169549 26.40738439 10.54083904 18.29875993 23.5871669 28.55894321 28.84286387 34.03190173 29.7766795 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
33	trip	29.77668		22			0	0	trip 0 4 4 0 travel hardness landscape hotel friends family cousin 1.532526553 4.518811932 3.60033316 6.570271207 7.955005623 14.64627402 20.60169549 26.40738439 10.54083904 18.29875993 23.5871669 28.55894321 28.84286387 34.03190173 29.7766795 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
KMJ

 

[DanteAmor]

These are your data from post #1

varios 17jul2020.xlsm
	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.07666343
3	wish	0	1	1	2	god	rich						0.016174863	11.69169836	7.705510337	12.34210383													1	2019_Sep_03_1124	ContinuousWA	3.1.5	60.07666343
4	raise	0	2	2	1	animal	food	house	mother	father	weakness		2.983911672	7.188053521	5.185717672	8.28921192	14.86275253	16.24712819	18.03229053	24.27178981	15.71277508	17.23095964	18.8161858	26.47368445					1	2019_Sep_03_1124	ContinuousWA	3.1.5	60.07666343
5	ahead	0	3	3	4	forsee							2.899188643		5.701968404														1	2019_Sep_03_1124	ContinuousWA	3.1.5	60.07666343
6	trip	0	4	4	0	travel	hardness	landscape	hotel	friends	family	cousin	1.532526553	4.518811932	3.60033316	6.570271207	7.955005623	14.64627402	20.60169549	26.40738439	10.54083904	18.29875993	23.5871669	28.55894321	28.84286387	34.03190173	29.7766795		1	2019_Sep_03_1124	ContinuousWA	3.1.5	60.07666343
Sheet12

These are the results of the macro, only 17 lines. All data matches your data included in your post #1.

varios 17jul2020.xlsm
	A	B	C	D	E	F	G	H	I
1	Cue	Response	Iteration	RespN	RespStartActual	RespEndActual	RespStartRelative	RespEndRelative	Date
2	brave								2019_Sep_03_1124
3	wish	god		1	0.016174863	7.705510337	0.016174863	7.705510337	2019_Sep_03_1124
4	wish	rich		2	11.69169836	12.34210383	3.986188023	4.636593493	2019_Sep_03_1124
5	raise	animal		1	2.983911672	5.185717672	2.983911672	5.185717672	2019_Sep_03_1124
6	raise	food		2	7.188053521	8.28921192	2.002335849	3.103494248	2019_Sep_03_1124
7	raise	house		3	14.86275253	15.71277508	6.57354061	7.42356316	2019_Sep_03_1124
8	raise	mother		4	16.24712819	17.23095964	0.53435311	1.51818456	2019_Sep_03_1124
9	raise	father		5	18.03229053	18.8161858	0.80133089	1.58522616	2019_Sep_03_1124
10	raise	weakness		6	24.27178981	26.47368445	5.45560401	7.65749865	2019_Sep_03_1124
11	ahead	forsee		1	2.899188643	5.701968404	2.899188643	5.701968404	2019_Sep_03_1124
12	trip	travel		1	1.532526553	3.60033316	1.532526553	3.60033316	2019_Sep_03_1124
13	trip	hardness		2	4.518811932	6.570271207	0.918478772	2.969938047	2019_Sep_03_1124
14	trip	landscape		3	7.955005623	10.54083904	1.384734416	3.970567833	2019_Sep_03_1124
15	trip	hotel		4	14.64627402	18.29875993	4.10543498	7.75792089	2019_Sep_03_1124
16	trip	friends		5	20.60169549	23.5871669	2.30293556	5.28840697	2019_Sep_03_1124
17	trip	family		6	26.40738439	28.55894321	2.82021749	4.97177631	2019_Sep_03_1124
18	trip	cousin		7	28.84286387	29.7766795	0.28392066	1.21773629	2019_Sep_03_1124
Output

If you try again with the original data, only 5 records. Headings in row 1 and with the texts as in your sample. The data from cell A2 to cell AG6.
The result is 17 records in the "output" sheet.

Check your original example, there should be no more data after row 6.

 

[pthwaites]

I must be doing something wrong - your data looks perfect but mine looks different when I run the VBA. I'm not too familiar with the process for implementing macros, so maybe something's going wrong there. Let me describe what I'm doing.

- I'm opening the source file and renaming the sheet "Sheet12", as in your example.
- I copy/paste the target sheet into a new worksheet and name it "Output" (the result is the same if I change the code to the name of the worksheet, rather than changing the name of the worksheet to match the code)
- I go to "Developer", then "Visual Basic"
- I go to "Inset", then "Module"
- I paste your code into the window that pops up. I click save.
- Then I click run.

The result I've got each time is the same as I posted above, with 33 lines instead of 18 and nothing in columns E and F. Do you have any idea where I might be going wrong?

 

[DanteAmor]

Another try.
In a new book create 2 sheets, sheet1 and output sheet.
On sheet1 put the information:

varios 17jul2020.xlsm
	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.07666343
3	wish	0	1	1	2	god	rich						0.016174863	11.69169836	7.705510337	12.34210383													1	2019_Sep_03_1124	ContinuousWA	3.1.5	60.07666343
4	raise	0	2	2	1	animal	food	house	mother	father	weakness		2.983911672	7.188053521	5.185717672	8.28921192	14.86275253	16.24712819	18.03229053	24.27178981	15.71277508	17.23095964	18.8161858	26.47368445					1	2019_Sep_03_1124	ContinuousWA	3.1.5	60.07666343
5	ahead	0	3	3	4	forsee							2.899188643		5.701968404														1	2019_Sep_03_1124	ContinuousWA	3.1.5	60.07666343
6	trip	0	4	4	0	travel	hardness	landscape	hotel	friends	family	cousin	1.532526553	4.518811932	3.60033316	6.570271207	7.955005623	14.64627402	20.60169549	26.40738439	10.54083904	18.29875993	23.5871669	28.55894321	28.84286387	34.03190173	29.7766795		1	2019_Sep_03_1124	ContinuousWA	3.1.5	60.07666343
Sheet1

In a module put the macro.
In the macro change this:
arr = Array("Sheet12", "Sheet13")
For this:
arr = Array("Sheet1")
Run the macro.

 

[DanteAmor]

Try my file:

Extracting data.xlsm

drive.google.com

 

[pthwaites]

Thanks for this. Your file worked great for the first sheet, so I added a second sheet and tried to extract the data from that as well. It seemed to revert to the same problems for the data in the new sheet. Here's a link:

Extracting data with added sheet.xlsm

drive.google.com