2

I have a list of fundamental frequency data taken from full sentences, such that I have values for different times (50 time points per word) for all words in the sentence. The data looks like this (but 50 points for each word, 5-8 words per phrase, 20 phrases per set, 3 sets... the data adds up quickly!):

    Speaker Accent   rowLabel ActualTime    F0
1     102-1      N     {SL} 0.00000000 105.954
2     102-1      N     {SL} 0.03757985 105.954
3     102-1      N     {SL} 0.07515970 105.954
4     102-1      N     {SL} 0.11273954 105.954
...
130   102-1      N    NOVEL   2.201260 115.82318
131   102-1      N    NOVEL   2.208244 112.42151
132   102-1      N    NOVEL   2.215228 108.82458
133   102-1      N    NOVEL   2.222212 105.19330
...
1328   102-1      N    NYLON   23.90094 113.44101
1329   102-1      N    NYLON   23.90693 113.01347
1330   102-1      N    NYLON   23.91292 112.44099
1331   102-1      N    NYLON   23.91890 111.77279

What I am trying to do is take a subset of this huge dataset based on a list of target words I have (which would fall under the 'rowLabel' category). I want to find those target words within the Excel file and copy the data for the entire row that has one of those target words in the rowLabel column (so in the example above, the words labeled NOVEL or NYLON are both on my list, so I would be looking for those as well as 38 other target words).

How would I get all of those rows (in this example, 130-133, and 1328-1331) based on searching for the rowLabel words from a list of 40 target words)?

And is it possible to get this to copy to another spreadsheet, rather than replacing the current one?

pnuts
  • 58,317
  • 11
  • 87
  • 139
Marissa
  • 345
  • 1
  • 5
  • 17

1 Answers1

1

If I understand correctly, a PivotTable should suit:

Add labels to columns A and C.
Select A1:F1331, Insert > Tables - PivotTable, PivotTable and choose whether in New or Existing Worksheet.
Drag to areas as indicated from Choose fields to add to report:

SO19107065 example

Filter rowLabelwith Select Multiple Items and the words of choice. Ensure you PT is totalled for rows and columns.
Double-click on the bottom RH corner cell of the PT.

pnuts
  • 58,317
  • 11
  • 87
  • 139