How To Extract Values Based On Criteria In Excel

[matthewbutterworth]

Hi, my brain has turned to blancmange attempting to solve this one on my own. Help is needed, please.

I'm trying to return the column values of rows that have a certain value. I've got close with this. https://www.extendoffice.com/documents/excel/3367-excel-extract-row-that-meet-criteria.html

This is an approximation of my main data sheet.

[TABLE="class: grid, width: 800, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD](The top row is used)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]SOURCE[/TD]
[TD][/TD]
[TD][/TD]
[TD]DATE[/TD]
[TD]MESSAGE[/TD]
[TD]URL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]twitter[/TD]
[TD][/TD]
[TD][/TD]
[TD]01/11/2017[/TD]
[TD]Message a[/TD]
[TD]www.a.com[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]facebook[/TD]
[TD][/TD]
[TD][/TD]
[TD]02/11/2017[/TD]
[TD]Message b[/TD]
[TD]www.b.com[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]linkedin[/TD]
[TD][/TD]
[TD][/TD]
[TD]03/11/2017[/TD]
[TD]Message c[/TD]
[TD]www.c.com[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]twitter[/TD]
[TD][/TD]
[TD][/TD]
[TD]04/11/2017[/TD]
[TD]Message d[/TD]
[TD]www.d.com[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]twitter[/TD]
[TD][/TD]
[TD][/TD]
[TD]05/11/2017[/TD]
[TD]Message e[/TD]
[TD]www.e.com[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


And this what I would like to return in a separate sheet. In the example below, this is the data that relates to twitter. I would like to hardcode IF=twitter in the formula, rather than referencing a cell. I'll then create separate sheets for linkedin and facebook.


[TABLE="class: grid, width: 800, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]01/11/2017[/TD]
[TD]Message a[/TD]
[TD]www.a.com[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]04/11/2017[/TD]
[TD]Message d[/TD]
[TD]www.d.com[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]05/11/2017[/TD]
[TD]Message e[/TD]
[TD]www.e.com[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Hopefully this explains it well enough for you to understand.

Thank you Forum! Matt.

 

[Momentman]

I would use a formula like this, entered with CTRL+SHIFT+ENTER

this can also be solved using "ADVANCED FILTER"

But here,,

Excel 2010
	A	B	C	D	E	F
1	(The top row is used)
2	SOURCE			DATE	MESSAGE	URL
3	twitter			01/11/2017	Message a	www.a.com
4	facebook			02/11/2017	Message b	www.b.com
5	linkedin			03/11/2017	Message c	www.c.com
6	twitter			04/11/2017	Message d	www.d.com
7	twitter			05/11/2017	Message e	www.e.com
8
9
10	01-Nov-17	Message a	www.a.com
11	04-Nov-17	Message d	www.d.com
12	05-Nov-17	Message e	www.e.com
Sheet4

Cell Formulas
Range	Formula
A10	{=IFERROR(INDEX(D$3:D$7,SMALL(IF($A$3:$A$7="Twitter",ROW($A$3:$A$7)-ROW($A$3)+1),ROWS($1:1))),"")}
B10	{=IFERROR(INDEX(E$3:E$7,SMALL(IF($A$3:$A$7="Twitter",ROW($A$3:$A$7)-ROW($A$3)+1),ROWS($1:1))),"")}
C10	{=IFERROR(INDEX(F$3:F$7,SMALL(IF($A$3:$A$7="Twitter",ROW($A$3:$A$7)-ROW($A$3)+1),ROWS($1:1))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[matthewbutterworth]

Thank you Momentman! Solved! So much appreciation for your time. Thank you, again.