Extract cells from one sheet to another based on specific criteria

[ShanaVT]

I have a large amount of unique values that are categorized under 3 names in one sheet. In separate sheets I want to be able to pull out the values by each name without skipping a row. See below. In the example below is what I have in Sheet 1 on the left - all unique values under 3 names. In Sheet 2 I just want to pull the values that are assigned to John. Any help is much appreciated! Thanks!


[TABLE="width: 286"]
<tbody>[TR]
[TD]Sheet 1[/TD]
[TD][/TD]
[TD][/TD]
[TD]Sheet 2 - John[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Paul[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD]Paul[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]Paul[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD]Paul[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD]Paul[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD="align: right"]19[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD="align: right"]11[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD="align: right"]12[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD="align: right"]13[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD="align: right"]14[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Paul[/TD]
[TD="align: right"]15[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD="align: right"]16[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD="align: right"]17[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD="align: right"]18[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD="align: right"]19[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Paul[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Scott T]

Book1
	A	B
1	name	value
2	John	1
3	Mary	2
4	Paul	3
5	Paul	4
6	Paul	5
7	Paul	6
8	Paul	7
9	Mary	8
10	Mary	9
11	John	10
12	John	11
13	John	12
14	John	13
15	John	14
16	Paul	15
17	John	16
18	Mary	17
19	Mary	18
20	John	19
21	Paul	20
Sheet1

Book1
	A	B
2	John	1
3		10
4		11
5		12
6		13
7		14
8		16
9		19
10
11
12
13
14
Sheet2

Cell Formulas
Range	Formula
B2	{=IF(ROWS(B$2:B2)>COUNTIF(Sheet1!$A$2:$A$21,Sheet2!$A$2),"",INDEX(Sheet1!$B$2:$B$21,SMALL(IF(Sheet2!$A$2=Sheet1!$A$2:$A$21,ROW(Sheet1!$A$2:$A$21)-ROW(Sheet1!$A$2)+1),ROWS(Sheet2!B$2:B2))))}
B3	{=IF(ROWS(B$2:B3)>COUNTIF(Sheet1!$A$2:$A$21,Sheet2!$A$2),"",INDEX(Sheet1!$B$2:$B$21,SMALL(IF(Sheet2!$A$2=Sheet1!$A$2:$A$21,ROW(Sheet1!$A$2:$A$21)-ROW(Sheet1!$A$2)+1),ROWS(Sheet2!B$2:B3))))}
B4	{=IF(ROWS(B$2:B4)>COUNTIF(Sheet1!$A$2:$A$21,Sheet2!$A$2),"",INDEX(Sheet1!$B$2:$B$21,SMALL(IF(Sheet2!$A$2=Sheet1!$A$2:$A$21,ROW(Sheet1!$A$2:$A$21)-ROW(Sheet1!$A$2)+1),ROWS(Sheet2!B$2:B4))))}
B5	{=IF(ROWS(B$2:B5)>COUNTIF(Sheet1!$A$2:$A$21,Sheet2!$A$2),"",INDEX(Sheet1!$B$2:$B$21,SMALL(IF(Sheet2!$A$2=Sheet1!$A$2:$A$21,ROW(Sheet1!$A$2:$A$21)-ROW(Sheet1!$A$2)+1),ROWS(Sheet2!B$2:B5))))}
B6	{=IF(ROWS(B$2:B6)>COUNTIF(Sheet1!$A$2:$A$21,Sheet2!$A$2),"",INDEX(Sheet1!$B$2:$B$21,SMALL(IF(Sheet2!$A$2=Sheet1!$A$2:$A$21,ROW(Sheet1!$A$2:$A$21)-ROW(Sheet1!$A$2)+1),ROWS(Sheet2!B$2:B6))))}
B7	{=IF(ROWS(B$2:B7)>COUNTIF(Sheet1!$A$2:$A$21,Sheet2!$A$2),"",INDEX(Sheet1!$B$2:$B$21,SMALL(IF(Sheet2!$A$2=Sheet1!$A$2:$A$21,ROW(Sheet1!$A$2:$A$21)-ROW(Sheet1!$A$2)+1),ROWS(Sheet2!B$2:B7))))}
B8	{=IF(ROWS(B$2:B8)>COUNTIF(Sheet1!$A$2:$A$21,Sheet2!$A$2),"",INDEX(Sheet1!$B$2:$B$21,SMALL(IF(Sheet2!$A$2=Sheet1!$A$2:$A$21,ROW(Sheet1!$A$2:$A$21)-ROW(Sheet1!$A$2)+1),ROWS(Sheet2!B$2:B8))))}
B9	{=IF(ROWS(B$2:B9)>COUNTIF(Sheet1!$A$2:$A$21,Sheet2!$A$2),"",INDEX(Sheet1!$B$2:$B$21,SMALL(IF(Sheet2!$A$2=Sheet1!$A$2:$A$21,ROW(Sheet1!$A$2:$A$21)-ROW(Sheet1!$A$2)+1),ROWS(Sheet2!B$2:B9))))}
B10	{=IF(ROWS(B$2:B10)>COUNTIF(Sheet1!$A$2:$A$21,Sheet2!$A$2),"",INDEX(Sheet1!$B$2:$B$21,SMALL(IF(Sheet2!$A$2=Sheet1!$A$2:$A$21,ROW(Sheet1!$A$2:$A$21)-ROW(Sheet1!$A$2)+1),ROWS(Sheet2!B$2:B10))))}
B11	{=IF(ROWS(B$2:B11)>COUNTIF(Sheet1!$A$2:$A$21,Sheet2!$A$2),"",INDEX(Sheet1!$B$2:$B$21,SMALL(IF(Sheet2!$A$2=Sheet1!$A$2:$A$21,ROW(Sheet1!$A$2:$A$21)-ROW(Sheet1!$A$2)+1),ROWS(Sheet2!B$2:B11))))}
B12	{=IF(ROWS(B$2:B12)>COUNTIF(Sheet1!$A$2:$A$21,Sheet2!$A$2),"",INDEX(Sheet1!$B$2:$B$21,SMALL(IF(Sheet2!$A$2=Sheet1!$A$2:$A$21,ROW(Sheet1!$A$2:$A$21)-ROW(Sheet1!$A$2)+1),ROWS(Sheet2!B$2:B12))))}
B13	{=IF(ROWS(B$2:B13)>COUNTIF(Sheet1!$A$2:$A$21,Sheet2!$A$2),"",INDEX(Sheet1!$B$2:$B$21,SMALL(IF(Sheet2!$A$2=Sheet1!$A$2:$A$21,ROW(Sheet1!$A$2:$A$21)-ROW(Sheet1!$A$2)+1),ROWS(Sheet2!B$2:B13))))}
B14	{=IF(ROWS(B$2:B14)>COUNTIF(Sheet1!$A$2:$A$21,Sheet2!$A$2),"",INDEX(Sheet1!$B$2:$B$21,SMALL(IF(Sheet2!$A$2=Sheet1!$A$2:$A$21,ROW(Sheet1!$A$2:$A$21)-ROW(Sheet1!$A$2)+1),ROWS(Sheet2!B$2:B14))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[My Aswer Is This]

Assuming the name "John" is in Sheet(2) Range("A2")
And the values like John are in sheet(1) column 'A"

Try this Vba solution:

Sub Test()
'Modified 3-21-18 2:00 PM EDT
Application.ScreenUpdating = False
Dim i As Long
Dim x As Long
x = 1
Sheets(1).Activate
Dim ans As String
ans = Sheets(2).Range("A2").Value
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    For i = 1 To Lastrow
        If Cells(i, 1).Value = ans Then
            x = x + 1
            Sheets(2).Cells(x, 2).Value = Cells(i, 2).Value
        End If
    Next
Application.ScreenUpdating = True
End Sub