Extract data from filtered list

[syang]

I have this table of all my students for the semester.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]NAME[/TD]
[TD]CLASS[/TD]
[TD]WHAT IS YOUR FAVORITE FOOD?[/TD]
[/TR]
[TR]
[TD]Blanca[/TD]
[TD]Day[/TD]
[TD]Mexican[/TD]
[/TR]
[TR]
[TD]Chong[/TD]
[TD]Night[/TD]
[TD]Pizza[/TD]
[/TR]
[TR]
[TD]David[/TD]
[TD]Day[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Eva[/TD]
[TD]Night[/TD]
[TD]Ice Cream[/TD]
[/TR]
[TR]
[TD]Fran[/TD]
[TD]Day[/TD]
[TD]Nachos[/TD]
[/TR]
[TR]
[TD]Frankie[/TD]
[TD]Day[/TD]
[TD]Pizza[/TD]
[/TR]
[TR]
[TD]Gretchen[/TD]
[TD]Day[/TD]
[TD]Ice Cream[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Night[/TD]
[TD]Burgers[/TD]
[/TR]
[TR]
[TD]Mandy[/TD]
[TD]Night[/TD]
[TD]Pizza[/TD]
[/TR]
[TR]
[TD]Pablo[/TD]
[TD]Night[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Penelope[/TD]
[TD]Day[/TD]
[TD]Candy[/TD]
[/TR]
[TR]
[TD]Prince[/TD]
[TD]Day[/TD]
[TD]Fruit[/TD]
[/TR]
[TR]
[TD]Richard[/TD]
[TD]Day[/TD]
[TD]Apples[/TD]
[/TR]
[TR]
[TD]Sandra[/TD]
[TD]Night[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sheila[/TD]
[TD]Night[/TD]
[TD]Fries[/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD]Day[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

When I filter by Class>Night, I get this:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]NAME[/TD]
[TD]CLASS[/TD]
[TD]WHAT IS YOUR FAVORITE FOOD?[/TD]
[/TR]
[TR]
[TD]Chong[/TD]
[TD]Night[/TD]
[TD]Pizza[/TD]
[/TR]
[TR]
[TD]Eva[/TD]
[TD]Night[/TD]
[TD]Ice Cream[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Night[/TD]
[TD]Burgers[/TD]
[/TR]
[TR]
[TD]Mandy[/TD]
[TD]Night[/TD]
[TD]Pizza[/TD]
[/TR]
[TR]
[TD]Pablo[/TD]
[TD]Night[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sandra[/TD]
[TD]Night[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sheila[/TD]
[TD]Night[/TD]
[TD]Fries[/TD]
[/TR]
</tbody>[/TABLE]

I use this formula to calculate the # of night students I have, which is awesome because it ignores my hidden values: =SUBTOTAL(3, B2:B1000)
This formula returns the value 7. Just what I need.

My question is, is there a 'subtotal' type formula for non-numerical values? I am trying to extract a simple list of all the answers to WHAT IS YOUR FAVORITE FOOD? in a column by itself. When I use this formula =C2 and drag the formula down, I get the list below. When I filter my main list to show Night students, the list remains the same because it won't ignore hidden values. I want the list to ONLY include answers from my Night students. I tried using a Pivot Table but it groups similar answers, which I don't want. If my list says Pizza 3 times, I want Pizza to show up 3 times.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]WHAT IS YOUR FAVORITE FOOD?[/TD]
[/TR]
[TR]
[TD]Mexican[/TD]
[/TR]
[TR]
[TD]Pizza[/TD]
[/TR]
[TR]
[TD]Tacos[/TD]
[/TR]
[TR]
[TD]Ice Cream[/TD]
[/TR]
[TR]
[TD]Nachos[/TD]
[/TR]
[TR]
[TD]Pizza[/TD]
[/TR]
[TR]
[TD]Ice Cream[/TD]
[/TR]
[TR]
[TD]Burgers[/TD]
[/TR]
[TR]
[TD]Pizza[/TD]
[/TR]
[TR]
[TD]Potatoes[/TD]
[/TR]
[TR]
[TD]Candy[/TD]
[/TR]
[TR]
[TD]Fruit[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[/TR]
[TR]
[TD]Fries[/TD]
[/TR]
[TR]
[TD]Fries[/TD]
[/TR]
[TR]
[TD]Soda[/TD]
[/TR]
</tbody>[/TABLE]

Thank you! I hope this makes sense.

 

[Aladin Akyurek]

Let A:C house the data.

In F1 enter: Night.

F3, control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($C$2:$C$17,SMALL(IF(1-($C$2:$C$17=""),
  IF($B$2:$B$17=$F$1,ROW($C$2:$C$17)-ROW($C$2)+1)),
  ROWS($F$3:F3))),"")

 

[circledchicken]

Hi,

It's not clear why you want a list of that kind?
Why does the PivotTable with the associated count not suffice?

Here are a couple of formula options you can try:

Excel 2013

	A	B	C
NAME	CLASS	WHAT IS YOUR FAVORITE FOOD?
Chong	Night	Pizza
Eva	Night	Ice Cream
John	Night	Burgers
Mandy	Night	Pizza
Pablo	Night
Sandra	Night
Sheila	Night	Fries
WHAT IS YOUR FAVORITE FOOD?
Pizza	Pizza
Pizza	Ice Cream
Ice Cream	Burgers
Burgers	Pizza
Fries	Fries

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]

[TD="align: right"][/TD]

[TD="align: center"]21[/TD]

[TD="align: right"][/TD]

[TD="align: center"]22[/TD]

[TD="align: right"][/TD]

[TD="align: center"]23[/TD]

[TD="align: right"][/TD]

[TD="align: center"]24[/TD]

[TD="align: right"][/TD]

</tbody>

Sheet1​

In A20 and copy down (if you want similar foods next to each other) - note you need to press CTRL-SHIFT-ENTER rather than just ENTER as this is an array formula:

=IFERROR(
    INDEX(
      C$2:C$17,
      SMALL(
        IF(
          SUBTOTAL(
            3,
            OFFSET(C$2:C$17, ROW(C$2:C$17) - ROW(C$2), 0, 1)) > 0,
          MATCH("~" & C$2:C$17, C$2:C$17 & "", 0)),
        ROWS(A$20:A20))),
    "")

Or, in B20 and copy down (if you want the foods in the order they appear in your list) - again you need to press CTRL-SHIFT-ENTER rather than just ENTER as this is an array formula:

=IFERROR(
    INDEX(
      C$2:C$17,
      SMALL(
        IF(
          SUBTOTAL(
            3,
            OFFSET(C$2:C$17, ROW(C$2:C$17) - ROW(C$2), 0, 1)) > 0,
          ROW(C$2:C$17) - ROW(C$2) + 1),
        ROWS(B$20:B20))),
    "")

 

[syang]

Hi circledchicken,

My data is actually much more complex than what I've pasted above. I am creating a report to show all the answers to 30 survey questions per class. I have about 50 different classes. This is the one survey question that differs from the rest because it allows survey takers to answer freely. Since there are so many classes, my report is user friendly in that you can filter the main list and all the values on the second sheet (averages, totals, charts, etc). automatically change based on my selection. The reason why I didn't like the Pivot Table is because it grouped similar items. Otherwise, it would work perfectly for what I need. For example, 3 people answered Pizza but on my report, it only shows Pizza once which makes it seem like there's only one person who likes pizza. I realize I could copy and paste the filtered part of that question onto the report each time, but I was just wondering if there was a better way.

Thank you circledchicken and Aladin Akyurek for the feedback.

 

[circledchicken]

Hi syang,

With a PivotTable you can drag the Row Label field again into the Values area to get the associated count of each item. Also, if you want the entries repeated with the item order preserved you can add an OrderId or other identifier column as the first row label.

But perhaps this isn't desirable with your current setup. In any case did the formula approaches in posts 2 or 3 solve your problem or are you still looking for a solution?

 

[syang]

Those are both great tips! I ended up adding the NAME as the first Row Label, then changed my formulas on the second sheet. Before I had the formulas:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Row 1:[/TD]
[TD] =IF('Pre and Post Comparison Helper'!A253=0,"",'Pre and Post Comparison Helper'!A253)[/TD]
[/TR]
[TR]
[TD]Row 2:[/TD]
[TD] =IF('Pre and Post Comparison Helper'!A254=0,"",'Pre and Post Comparison Helper'!A254)[/TD]
[/TR]
[TR]
[TD]Row 3:[/TD]
[TD] =IF('Pre and Post Comparison Helper'!A255=0,"",'Pre and Post Comparison Helper'!A255)[/TD]
[/TR]
[TR]
[TD]Row 4:[/TD]
[TD] =IF('Pre and Post Comparison Helper'!A256=0,"",'Pre and Post Comparison Helper'!A256)[/TD]
[/TR]
[TR]
[TD]Row 5:[/TD]
[TD] =IF('Pre and Post Comparison Helper'!A257=0,"",'Pre and Post Comparison Helper'!A257)[/TD]
[/TR]
[TR]
[TD]Row 6:[/TD]
[TD] =IF('Pre and Post Comparison Helper'!A258=0,"",'Pre and Post Comparison Helper'!A258)[/TD]
[/TR]
[TR]
[TD]Row 7:[/TD]
[TD] =IF('Pre and Post Comparison Helper'!A259=0,"",'Pre and Post Comparison Helper'!A259)[/TD]
[/TR]
[TR]
[TD]Row 8:[/TD]
[TD] =IF('Pre and Post Comparison Helper'!A260=0,"",'Pre and Post Comparison Helper'!A260)[/TD]
[/TR]
</tbody>[/TABLE]


Which gave me the results:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Row 1:[/TD]
[TD]Chong[/TD]
[/TR]
[TR]
[TD]Row 2:[/TD]
[TD]Pizza[/TD]
[/TR]
[TR]
[TD]Row 3:[/TD]
[TD]Eva[/TD]
[/TR]
[TR]
[TD]Row 4:[/TD]
[TD]Ice Cream[/TD]
[/TR]
[TR]
[TD]Row 5:[/TD]
[TD]John[/TD]
[/TR]
[TR]
[TD]Row 6:[/TD]
[TD]Burgers[/TD]
[/TR]
[TR]
[TD]Row 7:[/TD]
[TD]Mandy[/TD]
[/TR]
[TR]
[TD]Row 8:[/TD]
[TD]Pizza[/TD]
[/TR]
</tbody>[/TABLE]


Since I don’t want the names, I simply deleted all the ODD formulas, giving me the result I was looking for:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Row 1:[/TD]
[TD]Pizza[/TD]
[/TR]
[TR]
[TD]Row 2:[/TD]
[TD]Ice Cream[/TD]
[/TR]
[TR]
[TD]Row 3:[/TD]
[TD]Burgers[/TD]
[/TR]
[TR]
[TD]Row 4:[/TD]
[TD]Pizza[/TD]
[/TR]
</tbody>[/TABLE]


Thanks for the help!!!!!!!

 

[Aladin Akyurek]

Those are both great tips! I ended up adding the NAME as the first Row Label, then changed my formulas on the second sheet. Before I had the formulas:

[TABLE="class: grid, width: 500"]
<TBODY>[TR]
[TD]Row 1:
[/TD]
[TD]=IF('Pre and Post Comparison Helper'!A253=0,"",'Pre and Post Comparison Helper'!A253)
[/TD]
[/TR]
[TR]
[TD]Row 2:
[/TD]
[TD]=IF('Pre and Post Comparison Helper'!A254=0,"",'Pre and Post Comparison Helper'!A254)
[/TD]
[/TR]
[TR]
[TD]Row 3:
[/TD]
[TD]=IF('Pre and Post Comparison Helper'!A255=0,"",'Pre and Post Comparison Helper'!A255)
[/TD]
[/TR]
[TR]
[TD]Row 4:
[/TD]
[TD]=IF('Pre and Post Comparison Helper'!A256=0,"",'Pre and Post Comparison Helper'!A256)
[/TD]
[/TR]
[TR]
[TD]Row 5:
[/TD]
[TD]=IF('Pre and Post Comparison Helper'!A257=0,"",'Pre and Post Comparison Helper'!A257)
[/TD]
[/TR]
[TR]
[TD]Row 6:
[/TD]
[TD]=IF('Pre and Post Comparison Helper'!A258=0,"",'Pre and Post Comparison Helper'!A258)
[/TD]
[/TR]
[TR]
[TD]Row 7:
[/TD]
[TD]=IF('Pre and Post Comparison Helper'!A259=0,"",'Pre and Post Comparison Helper'!A259)
[/TD]
[/TR]
[TR]
[TD]Row 8:
[/TD]
[TD]=IF('Pre and Post Comparison Helper'!A260=0,"",'Pre and Post Comparison Helper'!A260)
[/TD]
[/TR]
</TBODY>[/TABLE]


Which gave me the results:

[TABLE="class: grid, width: 500"]
<TBODY>[TR]
[TD]Row 1:
[/TD]
[TD]Chong
[/TD]
[/TR]
[TR]
[TD]Row 2:
[/TD]
[TD]Pizza
[/TD]
[/TR]
[TR]
[TD]Row 3:
[/TD]
[TD]Eva
[/TD]
[/TR]
[TR]
[TD]Row 4:
[/TD]
[TD]Ice Cream
[/TD]
[/TR]
[TR]
[TD]Row 5:
[/TD]
[TD]John
[/TD]
[/TR]
[TR]
[TD]Row 6:
[/TD]
[TD]Burgers
[/TD]
[/TR]
[TR]
[TD]Row 7:
[/TD]
[TD]Mandy
[/TD]
[/TR]
[TR]
[TD]Row 8:
[/TD]
[TD]Pizza
[/TD]
[/TR]
</TBODY>[/TABLE]


Since I don’t want the names, I simply deleted all the ODD formulas, giving me the result I was looking for:

[TABLE="class: grid, width: 500"]
<TBODY>[TR]
[TD]Row 1:
[/TD]
[TD]Pizza
[/TD]
[/TR]
[TR]
[TD]Row 2:
[/TD]
[TD]Ice Cream
[/TD]
[/TR]
[TR]
[TD]Row 3:
[/TD]
[TD]Burgers
[/TD]
[/TR]
[TR]
[TD]Row 4:
[/TD]
[TD]Pizza
[/TD]
[/TR]
</TBODY>[/TABLE]


Thanks for the help!!!!!!!

Great, but the suggestion I forwarded gives you all that without alteration anywhere.