Extracting data from multiple sheets

pthwaites

New Member
Joined
Apr 14, 2020
Messages
13
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAG
1prompttrials.thisRepNtrials.thisTrialNtrials.thisNtrials.thisIndextyped_word_1typed_word_2typed_word_3typed_word_4typed_word_5typed_word_6typed_word_7start_time_1start_time_2submit_time_1submit_time_2start_time_3start_time_4start_time_5start_time_6submit_time_3submit_time_4submit_time_5submit_time_6start_time_7start_time_8submit_time_7participantsessiondateexpNamepsychopyVersionframeRate
2brave000312019_Sep_03_1124ContinuousWA3.1.560.0766634
3wish0112godrich0.0161748611.69169847.70551033712.3421038312019_Sep_03_1124ContinuousWA3.1.560.0766634
4raise0221animalfoodhousemotherfatherweakness2.983911677.188053525.1857176728.2892119214.862752516.247128218.032290524.271789815.7127750817.2309596418.816185826.4736844512019_Sep_03_1124ContinuousWA3.1.560.0766634
5ahead0334forsee2.899188645.70196840412019_Sep_03_1124ContinuousWA3.1.560.0766634
6trip0440travelhardnesslandscapehotelfriendsfamilycousin1.532526554.518811933.600333166.5702712077.9550056214.64627420.601695526.407384410.5408390418.2987599323.587166928.5589432128.842863934.031901729.776679512019_Sep_03_1124ContinuousWA3.1.560.0766634
ContinuousWA_2019_S


Output sheet:
Output sheet Example.xlsx
ABCDEFGHIJ
1CueResponseIterationRespNRespStartActualRespEndActualRespStartRelativeRespEndRelativeDate
2brave12019_Sep_03_1124
3wishgod110.0161748637.7055103370.0161748637.7055103372019_Sep_03_1124
4wishrich1211.6916983612.342103833.9861880234.6365934932019_Sep_03_1124
5raiseanimal112.9839116725.1857176722.9839116725.1857176722019_Sep_03_1124
6raisefood127.1880535218.289211922.0023358493.1034942482019_Sep_03_1124
7raisehouse1314.8627525315.712775086.573540617.423563162019_Sep_03_1124
8raisemother1416.2471281917.230959640.534353111.518184562019_Sep_03_1124
9raisefather1518.0322905318.81618580.801330891.585226162019_Sep_03_1124
10raiseweakness1624.2717898126.473684455.455604017.657498652019_Sep_03_1124
11aheadforsee112.8991886435.7019684042.8991886435.7019684042019_Sep_03_1124
12triptravel111.5325265533.600333161.5325265533.600333162019_Sep_03_1124
13triphardness124.5188119326.5702712070.9184787722.9699380472019_Sep_03_1124
14triplandscape137.95500562310.540839041.3847344163.9705678332019_Sep_03_1124
15triphotel1414.6462740218.298759934.105434987.757920892019_Sep_03_1124
16tripfriends1520.6016954923.58716692.302935565.288406972019_Sep_03_1124
17tripfamily1626.4073843928.558943212.820217494.971776312019_Sep_03_1124
18tripcousin1728.8428638729.77667950.283920661.217736292019_Sep_03_1124
19traveltrip212.50914124.19236032.50914124.19236032019_Sep_05_1108
20travelfamily225.70991278.47582961.51755244.28346932019_Sep_05_1108
21travelfood239.126095210.4758940.65026562.00006442019_Sep_05_1108
22travelmountain2410.992835614.75813850.51694164.28224452019_Sep_05_1108
23travelriver2515.392671317.30880490.63453282.55066642019_Sep_05_1108
24travelexperience2619.025560122.8751271.71675525.56632212019_Sep_05_1108
25travelchurch2724.492473626.30789071.61734663.43276372019_Sep_05_1108
26fatherfavorite215.03316448.73215275.03316448.73215272019_Sep_05_1108
27fatherlove2211.050029812.89911822.31787714.16696552019_Sep_05_1108
28fatherconsiderable2315.532946919.69900972.63382876.79989152019_Sep_05_1108
29fatherkind2421.116156523.6990291.41714684.00001932019_Sep_05_1108
30fatherlearnable2525.399169832.11442191.70014088.41539292019_Sep_05_1108
31fatherinsurance2634.532186538.11478912.41776466.00036722019_Sep_05_1108
32familymother212.01747893.60233592.01747893.60233592019_Sep_05_1108
Sheet1
Cell Formulas
RangeFormula
G4,G27:G31,G20:G25,G13:G18,G6:G10G4=E4-F3
H4,H27:H31,H20:H25,H13:H18,H6:H10H4=F4-F3
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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.
VBA Code:
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
 
Upvote 0
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
ABCDEFGHIJ
1
2bravebrave 0 0 0 3 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
3wishgod1wish 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
4wishrich200wish 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
5raiseanimal1raise 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
6raisefood200raise 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
7raisehouse300raise 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
8raisemother400raise 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
9raisefather500raise 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
10raiseweakness600raise 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
11aheadforsee1ahead 0 3 3 4 forsee 2.899188643 5.701968404 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
12triptravel1trip 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
13triphardness200trip 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
14triplandscape300trip 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
15triphotel400trip 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
16tripfriends500trip 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
17tripfamily600trip 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
18tripcousin700trip 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
19trip1.532527800trip 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
20trip4.518812900trip 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
21trip3.6003331000trip 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
22trip6.5702711100trip 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
23trip7.9550061200trip 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
24trip14.646271300trip 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
25trip20.60171400trip 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
26trip26.407381500trip 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
27trip10.540841600trip 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
28trip18.298761700trip 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
29trip23.587171800trip 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
30trip28.558941900trip 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
31trip28.842862000trip 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
32trip34.03192100trip 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
33trip29.776682200trip 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
34tripminjoon kim2300trip 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
35trip12400trip 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
36trip2019_Sep_03_11242500trip 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
37tripContinuousWA2600trip 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
38trip3.1.52700trip 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
39trip60.076662800trip 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
40triptrip 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.076663432900trip 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
41traveltrip12019_Sep_05_1108
42travelfamily2002019_Sep_05_1108
43travelfood3002019_Sep_05_1108
44travelmountain4002019_Sep_05_1108
45travelriver5002019_Sep_05_1108
46travelexperience6002019_Sep_05_1108
47travelchurch7002019_Sep_05_1108
48travelcounrt8002019_Sep_05_1108
49travel2.5091419002019_Sep_05_1108
50travel5.70991310002019_Sep_05_1108
51travel9.12609511002019_Sep_05_1108
52travel10.9928412002019_Sep_05_1108
53travel15.3926713002019_Sep_05_1108
54travel19.0255614002019_Sep_05_1108
55travel24.4924715002019_Sep_05_1108
56travel31.4754316002019_Sep_05_1108
57travel4.1923617002019_Sep_05_1108
58travel8.4758318002019_Sep_05_1108
59travel10.4758919002019_Sep_05_1108
60travel14.7581420002019_Sep_05_1108
61travel17.308821002019_Sep_05_1108
62travel22.8751322002019_Sep_05_1108
63travel26.3078923002019_Sep_05_1108
64fatherfavorite12019_Sep_05_1108
65fatherlove2002019_Sep_05_1108
66fatherconsiderable3002019_Sep_05_1108
67fatherkind4002019_Sep_05_1108
68fatherlearnable5002019_Sep_05_1108
69fatherinsurance6002019_Sep_05_1108
70familymother12019_Sep_05_1108
71familybrother2002019_Sep_05_1108
72familycousin3002019_Sep_05_1108
73familydaughter4002019_Sep_05_1108
74familyfather5002019_Sep_05_1108
75familygrandfather6002019_Sep_05_1108
76familylovely7002019_Sep_05_1108
77familysimilar8002019_Sep_05_1108
78family2.0174799002019_Sep_05_1108
79family4.68350610002019_Sep_05_1108
80family9.58320911002019_Sep_05_1108
81family14.0163612002019_Sep_05_1108
82family18.9662213002019_Sep_05_1108
83family22.0495514002019_Sep_05_1108
84family27.7157815002019_Sep_05_1108
85family34.1485116002019_Sep_05_1108
86family3.60233617002019_Sep_05_1108
87family6.84969118002019_Sep_05_1108
88family11.5486919002019_Sep_05_1108
89family17.1985420002019_Sep_05_1108
90family20.265121002019_Sep_05_1108
91family25.7317922002019_Sep_05_1108
92family29.7814523002019_Sep_05_1108
93hardnessdifficult12019_Sep_05_1108
94godchurch12019_Sep_05_1108
95godreligion2002019_Sep_05_1108
96godgodness3002019_Sep_05_1108
97godchristian4002019_Sep_05_1108
98godislam5002019_Sep_05_1108
99godlove6002019_Sep_05_1108
100godjesus7002019_Sep_05_1108
101godtrinity831.79752031.797522019_Sep_05_1108
102god2.266768932.6653937.297760.8678685.5002412019_Sep_05_1108
103god4.065291037.731660.433903-37.29782019_Sep_05_1108
104god8.68308611002019_Sep_05_1108
105god13.5661312002019_Sep_05_1108
106god18.4976613002019_Sep_05_1108
107god24.5821514002019_Sep_05_1108
108god26.9989315002019_Sep_05_1108
109god29.3153816002019_Sep_05_1108
110god3.78214617002019_Sep_05_1108
111god7.1813418002019_Sep_05_1108
112god11.414819002019_Sep_05_1108
113god17.3314220002019_Sep_05_1108
114god21.9151821002019_Sep_05_1108
115god25.8308422002019_Sep_05_1108
116god28.7650523002019_Sep_05_1108
117godredemption24002019_Sep_05_1108
118godsa25002019_Sep_05_1108
119hotelhouse12019_Sep_05_1108
120hotellandscape2002019_Sep_05_1108
121hotelsleep3002019_Sep_05_1108
122hotelfood4002019_Sep_05_1108
123hotelbreakfast5002019_Sep_05_1108
124hoteldinner6002019_Sep_05_1108
125hotelexpensive7002019_Sep_05_1108
126hoteltravel835.66379035.663792019_Sep_05_1108
127hotel1.5497549-35.6638-35.66382019_Sep_05_1108
128hotel4.05019810002019_Sep_05_1108
129hotel7.41689811002019_Sep_05_1108
130hotel11.4999912002019_Sep_05_1108
131hotel13.5668513002019_Sep_05_1108
132hotel16.5164414002019_Sep_05_1108
133hotel20.9329315002019_Sep_05_1108
134hotel34.1155616002019_Sep_05_1108
135hotel3.43337817002019_Sep_05_1108
136hotel6.93272218002019_Sep_05_1108
137hotel9.21603919002019_Sep_05_1108
138hotel12.8152720002019_Sep_05_1108
139hotel16.1652421002019_Sep_05_1108
140hotel18.2644522002019_Sep_05_1108
141hotel26.9308823002019_Sep_05_1108
KMJ_Output_Master
 
Upvote 0
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_#.
 
Upvote 0
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
ABCDEFGHI
2bravebrave 0 0 0 3 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
3wishgod1wish 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
4wishrich200wish 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
5raiseanimal1raise 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
6raisefood200raise 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
7raisehouse300raise 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
8raisemother400raise 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
9raisefather500raise 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
10raiseweakness600raise 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
11aheadforsee1ahead 0 3 3 4 forsee 2.899188643 5.701968404 minjoon kim 1 2019_Sep_03_1124 ContinuousWA 3.1.5 60.07666343
12triptravel1trip 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
13triphardness200trip 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
14triplandscape300trip 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
15triphotel400trip 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
16tripfriends500trip 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
17tripfamily600trip 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
18tripcousin700trip 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
19trip1.532527800trip 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
20trip4.518812900trip 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
21trip3.6003331000trip 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
22trip6.5702711100trip 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
23trip7.9550061200trip 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
24trip14.646271300trip 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
25trip20.60171400trip 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
26trip26.407381500trip 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
27trip10.540841600trip 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
28trip18.298761700trip 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
29trip23.587171800trip 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
30trip28.558941900trip 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
31trip28.842862000trip 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
32trip34.03192100trip 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
33trip29.776682200trip 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
 
Upvote 0
These are your data from post #1

varios 17jul2020.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAG
1prompttrials.thisRepNtrials.thisTrialNtrials.thisNtrials.thisIndextyped_word_1typed_word_2typed_word_3typed_word_4typed_word_5typed_word_6typed_word_7start_time_1start_time_2submit_time_1submit_time_2start_time_3start_time_4start_time_5start_time_6submit_time_3submit_time_4submit_time_5submit_time_6start_time_7start_time_8submit_time_7participantsessiondateexpNamepsychopyVersionframeRate
2brave000312019_Sep_03_1124ContinuousWA3.1.560.07666343
3wish0112godrich0.01617486311.691698367.70551033712.3421038312019_Sep_03_1124ContinuousWA3.1.560.07666343
4raise0221animalfoodhousemotherfatherweakness2.9839116727.1880535215.1857176728.2892119214.8627525316.2471281918.0322905324.2717898115.7127750817.2309596418.816185826.4736844512019_Sep_03_1124ContinuousWA3.1.560.07666343
5ahead0334forsee2.8991886435.70196840412019_Sep_03_1124ContinuousWA3.1.560.07666343
6trip0440travelhardnesslandscapehotelfriendsfamilycousin1.5325265534.5188119323.600333166.5702712077.95500562314.6462740220.6016954926.4073843910.5408390418.2987599323.587166928.5589432128.8428638734.0319017329.776679512019_Sep_03_1124ContinuousWA3.1.560.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
ABCDEFGHI
1CueResponseIterationRespNRespStartActualRespEndActualRespStartRelativeRespEndRelativeDate
2brave2019_Sep_03_1124
3wishgod10.0161748637.7055103370.0161748637.7055103372019_Sep_03_1124
4wishrich211.6916983612.342103833.9861880234.6365934932019_Sep_03_1124
5raiseanimal12.9839116725.1857176722.9839116725.1857176722019_Sep_03_1124
6raisefood27.1880535218.289211922.0023358493.1034942482019_Sep_03_1124
7raisehouse314.8627525315.712775086.573540617.423563162019_Sep_03_1124
8raisemother416.2471281917.230959640.534353111.518184562019_Sep_03_1124
9raisefather518.0322905318.81618580.801330891.585226162019_Sep_03_1124
10raiseweakness624.2717898126.473684455.455604017.657498652019_Sep_03_1124
11aheadforsee12.8991886435.7019684042.8991886435.7019684042019_Sep_03_1124
12triptravel11.5325265533.600333161.5325265533.600333162019_Sep_03_1124
13triphardness24.5188119326.5702712070.9184787722.9699380472019_Sep_03_1124
14triplandscape37.95500562310.540839041.3847344163.9705678332019_Sep_03_1124
15triphotel414.6462740218.298759934.105434987.757920892019_Sep_03_1124
16tripfriends520.6016954923.58716692.302935565.288406972019_Sep_03_1124
17tripfamily626.4073843928.558943212.820217494.971776312019_Sep_03_1124
18tripcousin728.8428638729.77667950.283920661.217736292019_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.
 
Upvote 0
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?
 
Upvote 0
Another try.
In a new book create 2 sheets, sheet1 and output sheet.
On sheet1 put the information:
varios 17jul2020.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAG
1prompttrials.thisRepNtrials.thisTrialNtrials.thisNtrials.thisIndextyped_word_1typed_word_2typed_word_3typed_word_4typed_word_5typed_word_6typed_word_7start_time_1start_time_2submit_time_1submit_time_2start_time_3start_time_4start_time_5start_time_6submit_time_3submit_time_4submit_time_5submit_time_6start_time_7start_time_8submit_time_7participantsessiondateexpNamepsychopyVersionframeRate
2brave000312019_Sep_03_1124ContinuousWA3.1.560.07666343
3wish0112godrich0.01617486311.691698367.70551033712.3421038312019_Sep_03_1124ContinuousWA3.1.560.07666343
4raise0221animalfoodhousemotherfatherweakness2.9839116727.1880535215.1857176728.2892119214.8627525316.2471281918.0322905324.2717898115.7127750817.2309596418.816185826.4736844512019_Sep_03_1124ContinuousWA3.1.560.07666343
5ahead0334forsee2.8991886435.70196840412019_Sep_03_1124ContinuousWA3.1.560.07666343
6trip0440travelhardnesslandscapehotelfriendsfamilycousin1.5325265534.5188119323.600333166.5702712077.95500562314.6462740220.6016954926.4073843910.5408390418.2987599323.587166928.5589432128.8428638734.0319017329.776679512019_Sep_03_1124ContinuousWA3.1.560.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.
 
Upvote 0
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:

 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top