Pull Chosen Data Only

[Eric G]

How do I pull only certain data from one section to another section?

In my spreadsheet for example, I have something like this here:

	A	B	C	D	E	F	G	H
1	ORG	FIRST	LAST	DATE		FIRST	LAST	DATE
2	2	Matthew	Johnson	5/2/2015
3	2	Mark	Anderson	2/7/2014
4	2	Luke	Jones	8/7/2014
5		Esau	Smith	1/1/2015
6		Cain	Williams	3/17/2016
7	2	John	Miller	3/16/2017

<tbody>
</tbody>

Of the rows in Column A denoted with the number 2, I need its corresponding data in Columns B, C and D to appear automatically or by VBA in Columns F, G and H. So that it would look like this here:

	A	B	C	D	E	F	G	H
1	ORG	FIRST	LAST	DATE		FIRST	LAST	DATE
2	2	Matthew	Johnson	5/2/2015		Matthew<strike></strike>	Johnson<strike></strike>	5/2/2015<strike></strike>
3	2	Mark	Anderson	2/7/2014		Mark<strike></strike>	Anderson<strike></strike>	2/7/2014<strike></strike>
4	2	Luke	Jones	8/7/2014		Luke<strike></strike>	Jones<strike></strike>	8/7/2014<strike></strike>
5		Esau	Smith	1/1/2015		John<strike></strike>	Miller	3/16/2017<strike></strike>
6		Cain	Williams	3/17/2016
7	2	John	Miller	3/16/2017

<tbody>
</tbody>

What are some ways I could accomplish this?

 

[Scott T]

Excel 2010
	A	B	C	D	E	F	G	H
1	ORG	FIRST	LAST	DATE		FIRST	LAST	DATE
2	2	Matthew	Johnson	5/2/2015		Matthew	Johnson	5/2/2015
3	2	Mark	Anderson	2/7/2014		Mark	Anderson	2/7/2014
4	2	Luke	Jones	8/7/2014		Luke	Jones	8/7/2014
5		Esau	Smith	1/1/2015		John	Miller	3/16/2017
6		Cain	Williams	3/17/2016
7	2	John	Miller	3/16/2017
Sheet4

Cell Formulas
Range	Formula
F2	{=IF(ROWS(F$2:F2)>COUNTIF($A$2:$B$7,2),"",INDEX(B$2:B$7,SMALL(IF($A$2:$A$7=2,ROW(A$2:A$7)-ROW(A$2)+1),ROWS(F$2:F2))))}
F3	{=IF(ROWS(F$2:F3)>COUNTIF($A$2:$B$7,2),"",INDEX(B$2:B$7,SMALL(IF($A$2:$A$7=2,ROW(A$2:A$7)-ROW(A$2)+1),ROWS(F$2:F3))))}
F4	{=IF(ROWS(F$2:F4)>COUNTIF($A$2:$B$7,2),"",INDEX(B$2:B$7,SMALL(IF($A$2:$A$7=2,ROW(A$2:A$7)-ROW(A$2)+1),ROWS(F$2:F4))))}
F5	{=IF(ROWS(F$2:F5)>COUNTIF($A$2:$B$7,2),"",INDEX(B$2:B$7,SMALL(IF($A$2:$A$7=2,ROW(A$2:A$7)-ROW(A$2)+1),ROWS(F$2:F5))))}
F6	{=IF(ROWS(F$2:F6)>COUNTIF($A$2:$B$7,2),"",INDEX(B$2:B$7,SMALL(IF($A$2:$A$7=2,ROW(A$2:A$7)-ROW(A$2)+1),ROWS(F$2:F6))))}
F7	{=IF(ROWS(F$2:F7)>COUNTIF($A$2:$B$7,2),"",INDEX(B$2:B$7,SMALL(IF($A$2:$A$7=2,ROW(A$2:A$7)-ROW(A$2)+1),ROWS(F$2:F7))))}
G2	{=IF(ROWS(G$2:G2)>COUNTIF($A$2:$B$7,2),"",INDEX(C$2:C$7,SMALL(IF($A$2:$A$7=2,ROW(B$2:B$7)-ROW(B$2)+1),ROWS(G$2:G2))))}
G3	{=IF(ROWS(G$2:G3)>COUNTIF($A$2:$B$7,2),"",INDEX(C$2:C$7,SMALL(IF($A$2:$A$7=2,ROW(B$2:B$7)-ROW(B$2)+1),ROWS(G$2:G3))))}
G4	{=IF(ROWS(G$2:G4)>COUNTIF($A$2:$B$7,2),"",INDEX(C$2:C$7,SMALL(IF($A$2:$A$7=2,ROW(B$2:B$7)-ROW(B$2)+1),ROWS(G$2:G4))))}
G5	{=IF(ROWS(G$2:G5)>COUNTIF($A$2:$B$7,2),"",INDEX(C$2:C$7,SMALL(IF($A$2:$A$7=2,ROW(B$2:B$7)-ROW(B$2)+1),ROWS(G$2:G5))))}
G6	{=IF(ROWS(G$2:G6)>COUNTIF($A$2:$B$7,2),"",INDEX(C$2:C$7,SMALL(IF($A$2:$A$7=2,ROW(B$2:B$7)-ROW(B$2)+1),ROWS(G$2:G6))))}
G7	{=IF(ROWS(G$2:G7)>COUNTIF($A$2:$B$7,2),"",INDEX(C$2:C$7,SMALL(IF($A$2:$A$7=2,ROW(B$2:B$7)-ROW(B$2)+1),ROWS(G$2:G7))))}
H2	{=IF(ROWS(H$2:H2)>COUNTIF($A$2:$B$7,2),"",INDEX(D$2:D$7,SMALL(IF($A$2:$A$7=2,ROW(C$2:C$7)-ROW(C$2)+1),ROWS(H$2:H2))))}
H3	{=IF(ROWS(H$2:H3)>COUNTIF($A$2:$B$7,2),"",INDEX(D$2:D$7,SMALL(IF($A$2:$A$7=2,ROW(C$2:C$7)-ROW(C$2)+1),ROWS(H$2:H3))))}
H4	{=IF(ROWS(H$2:H4)>COUNTIF($A$2:$B$7,2),"",INDEX(D$2:D$7,SMALL(IF($A$2:$A$7=2,ROW(C$2:C$7)-ROW(C$2)+1),ROWS(H$2:H4))))}
H5	{=IF(ROWS(H$2:H5)>COUNTIF($A$2:$B$7,2),"",INDEX(D$2:D$7,SMALL(IF($A$2:$A$7=2,ROW(C$2:C$7)-ROW(C$2)+1),ROWS(H$2:H5))))}
H6	{=IF(ROWS(H$2:H6)>COUNTIF($A$2:$B$7,2),"",INDEX(D$2:D$7,SMALL(IF($A$2:$A$7=2,ROW(C$2:C$7)-ROW(C$2)+1),ROWS(H$2:H6))))}
H7	{=IF(ROWS(H$2:H7)>COUNTIF($A$2:$B$7,2),"",INDEX(D$2:D$7,SMALL(IF($A$2:$A$7=2,ROW(C$2:C$7)-ROW(C$2)+1),ROWS(H$2:H7))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Eric G]

Just too cool. Works as advertised. Thanks, Scott.

 

[Eric G]

This is in regard to Scott's solution above. When the source cell is empty. How do I prevent the data from being pulled over as "0," when it's in the general format, or as "1/01/1900," when it's in the date format?

For example,

	A	B	C	D	E	F	G	H
1	ORG	FIRST	LAST	DATE		FIRST	LAST	DATE
2	2	Matthew	Johnson			Matthew	Johnson	1/01/1900
3	2	Mark		2/7/2014		Mark	0	2/7/2014
4		Esau	Smith	1/1/2015		John	Miller	3/16/2017
5	2	John	Miller	3/16/2017

<tbody>
</tbody>

If the source cell is empty, the destination cell needs to be empty too. Is there a solution?

 

[Scott T]

There is probable a better way but I came up with

Excel 2010
	A	B	C	D	E	F	G	H
1	ORG	FIRST	LAST	DATE		FIRST	LAST	DATE
2	2	Matthew	Johnson			Matthew	Johnson
3	2	Mark		2/7/2014		Mark		2/7/2014
4	2	Luke	Jones	8/7/2014		Luke	Jones	8/7/2014
5		Esau	Smith	1/1/2015		John	Miller	3/16/2017
6		Cain	Williams	3/17/2016
7	2	John	Miller	3/16/2017
8
9
10
Sheet1

Cell Formulas
Range	Formula
F2	{=IF(ROWS(F$2:F2)>COUNTIF($A$2:$B$7,2),"",IF(INDEX(B$2:B$7,SMALL(IF($A$2:$A$7=2,ROW(A$2:A$7)-ROW(A$2)+1),ROWS(F$2:F2)))=0,"",INDEX(B$2:B$7,SMALL(IF($A$2:$A$7=2,ROW(A$2:A$7)-ROW(A$2)+1),ROWS(F$2:F2)))))}
F3	{=IF(ROWS(F$2:F3)>COUNTIF($A$2:$B$7,2),"",IF(INDEX(B$2:B$7,SMALL(IF($A$2:$A$7=2,ROW(A$2:A$7)-ROW(A$2)+1),ROWS(F$2:F3)))=0,"",INDEX(B$2:B$7,SMALL(IF($A$2:$A$7=2,ROW(A$2:A$7)-ROW(A$2)+1),ROWS(F$2:F3)))))}
F4	{=IF(ROWS(F$2:F4)>COUNTIF($A$2:$B$7,2),"",IF(INDEX(B$2:B$7,SMALL(IF($A$2:$A$7=2,ROW(A$2:A$7)-ROW(A$2)+1),ROWS(F$2:F4)))=0,"",INDEX(B$2:B$7,SMALL(IF($A$2:$A$7=2,ROW(A$2:A$7)-ROW(A$2)+1),ROWS(F$2:F4)))))}
F5	{=IF(ROWS(F$2:F5)>COUNTIF($A$2:$B$7,2),"",IF(INDEX(B$2:B$7,SMALL(IF($A$2:$A$7=2,ROW(A$2:A$7)-ROW(A$2)+1),ROWS(F$2:F5)))=0,"",INDEX(B$2:B$7,SMALL(IF($A$2:$A$7=2,ROW(A$2:A$7)-ROW(A$2)+1),ROWS(F$2:F5)))))}
F6	{=IF(ROWS(F$2:F6)>COUNTIF($A$2:$B$7,2),"",IF(INDEX(B$2:B$7,SMALL(IF($A$2:$A$7=2,ROW(A$2:A$7)-ROW(A$2)+1),ROWS(F$2:F6)))=0,"",INDEX(B$2:B$7,SMALL(IF($A$2:$A$7=2,ROW(A$2:A$7)-ROW(A$2)+1),ROWS(F$2:F6)))))}
F7	{=IF(ROWS(F$2:F7)>COUNTIF($A$2:$B$7,2),"",IF(INDEX(B$2:B$7,SMALL(IF($A$2:$A$7=2,ROW(A$2:A$7)-ROW(A$2)+1),ROWS(F$2:F7)))=0,"",INDEX(B$2:B$7,SMALL(IF($A$2:$A$7=2,ROW(A$2:A$7)-ROW(A$2)+1),ROWS(F$2:F7)))))}
F8	{=IF(ROWS(F$2:F8)>COUNTIF($A$2:$B$7,2),"",IF(INDEX(B$2:B$7,SMALL(IF($A$2:$A$7=2,ROW(A$2:A$7)-ROW(A$2)+1),ROWS(F$2:F8)))=0,"",INDEX(B$2:B$7,SMALL(IF($A$2:$A$7=2,ROW(A$2:A$7)-ROW(A$2)+1),ROWS(F$2:F8)))))}
F9	{=IF(ROWS(F$2:F9)>COUNTIF($A$2:$B$7,2),"",IF(INDEX(B$2:B$7,SMALL(IF($A$2:$A$7=2,ROW(A$2:A$7)-ROW(A$2)+1),ROWS(F$2:F9)))=0,"",INDEX(B$2:B$7,SMALL(IF($A$2:$A$7=2,ROW(A$2:A$7)-ROW(A$2)+1),ROWS(F$2:F9)))))}
F10	{=IF(ROWS(F$2:F10)>COUNTIF($A$2:$B$7,2),"",IF(INDEX(B$2:B$7,SMALL(IF($A$2:$A$7=2,ROW(A$2:A$7)-ROW(A$2)+1),ROWS(F$2:F10)))=0,"",INDEX(B$2:B$7,SMALL(IF($A$2:$A$7=2,ROW(A$2:A$7)-ROW(A$2)+1),ROWS(F$2:F10)))))}
G2	{=IF(ROWS(G$2:G2)>COUNTIF($A$2:$B$7,2),"",IF(INDEX(C$2:C$7,SMALL(IF($A$2:$A$7=2,ROW(B$2:B$7)-ROW(B$2)+1),ROWS(G$2:G2)))=0,"",INDEX(C$2:C$7,SMALL(IF($A$2:$A$7=2,ROW(B$2:B$7)-ROW(B$2)+1),ROWS(G$2:G2)))))}
G3	{=IF(ROWS(G$2:G3)>COUNTIF($A$2:$B$7,2),"",IF(INDEX(C$2:C$7,SMALL(IF($A$2:$A$7=2,ROW(B$2:B$7)-ROW(B$2)+1),ROWS(G$2:G3)))=0,"",INDEX(C$2:C$7,SMALL(IF($A$2:$A$7=2,ROW(B$2:B$7)-ROW(B$2)+1),ROWS(G$2:G3)))))}
G4	{=IF(ROWS(G$2:G4)>COUNTIF($A$2:$B$7,2),"",IF(INDEX(C$2:C$7,SMALL(IF($A$2:$A$7=2,ROW(B$2:B$7)-ROW(B$2)+1),ROWS(G$2:G4)))=0,"",INDEX(C$2:C$7,SMALL(IF($A$2:$A$7=2,ROW(B$2:B$7)-ROW(B$2)+1),ROWS(G$2:G4)))))}
G5	{=IF(ROWS(G$2:G5)>COUNTIF($A$2:$B$7,2),"",IF(INDEX(C$2:C$7,SMALL(IF($A$2:$A$7=2,ROW(B$2:B$7)-ROW(B$2)+1),ROWS(G$2:G5)))=0,"",INDEX(C$2:C$7,SMALL(IF($A$2:$A$7=2,ROW(B$2:B$7)-ROW(B$2)+1),ROWS(G$2:G5)))))}
G6	{=IF(ROWS(G$2:G6)>COUNTIF($A$2:$B$7,2),"",IF(INDEX(C$2:C$7,SMALL(IF($A$2:$A$7=2,ROW(B$2:B$7)-ROW(B$2)+1),ROWS(G$2:G6)))=0,"",INDEX(C$2:C$7,SMALL(IF($A$2:$A$7=2,ROW(B$2:B$7)-ROW(B$2)+1),ROWS(G$2:G6)))))}
G7	{=IF(ROWS(G$2:G7)>COUNTIF($A$2:$B$7,2),"",IF(INDEX(C$2:C$7,SMALL(IF($A$2:$A$7=2,ROW(B$2:B$7)-ROW(B$2)+1),ROWS(G$2:G7)))=0,"",INDEX(C$2:C$7,SMALL(IF($A$2:$A$7=2,ROW(B$2:B$7)-ROW(B$2)+1),ROWS(G$2:G7)))))}
G8	{=IF(ROWS(G$2:G8)>COUNTIF($A$2:$B$7,2),"",IF(INDEX(C$2:C$7,SMALL(IF($A$2:$A$7=2,ROW(B$2:B$7)-ROW(B$2)+1),ROWS(G$2:G8)))=0,"",INDEX(C$2:C$7,SMALL(IF($A$2:$A$7=2,ROW(B$2:B$7)-ROW(B$2)+1),ROWS(G$2:G8)))))}
G9	{=IF(ROWS(G$2:G9)>COUNTIF($A$2:$B$7,2),"",IF(INDEX(C$2:C$7,SMALL(IF($A$2:$A$7=2,ROW(B$2:B$7)-ROW(B$2)+1),ROWS(G$2:G9)))=0,"",INDEX(C$2:C$7,SMALL(IF($A$2:$A$7=2,ROW(B$2:B$7)-ROW(B$2)+1),ROWS(G$2:G9)))))}
G10	{=IF(ROWS(G$2:G10)>COUNTIF($A$2:$B$7,2),"",IF(INDEX(C$2:C$7,SMALL(IF($A$2:$A$7=2,ROW(B$2:B$7)-ROW(B$2)+1),ROWS(G$2:G10)))=0,"",INDEX(C$2:C$7,SMALL(IF($A$2:$A$7=2,ROW(B$2:B$7)-ROW(B$2)+1),ROWS(G$2:G10)))))}
H2	{=IF(ROWS(H$2:H2)>COUNTIF($A$2:$B$7,2),"",IF(INDEX(D$2:D$7,SMALL(IF($A$2:$A$7=2,ROW(C$2:C$7)-ROW(C$2)+1),ROWS(H$2:H2)))=0,"",INDEX(D$2:D$7,SMALL(IF($A$2:$A$7=2,ROW(C$2:C$7)-ROW(C$2)+1),ROWS(H$2:H2)))))}
H3	{=IF(ROWS(H$2:H3)>COUNTIF($A$2:$B$7,2),"",IF(INDEX(D$2:D$7,SMALL(IF($A$2:$A$7=2,ROW(C$2:C$7)-ROW(C$2)+1),ROWS(H$2:H3)))=0,"",INDEX(D$2:D$7,SMALL(IF($A$2:$A$7=2,ROW(C$2:C$7)-ROW(C$2)+1),ROWS(H$2:H3)))))}
H4	{=IF(ROWS(H$2:H4)>COUNTIF($A$2:$B$7,2),"",IF(INDEX(D$2:D$7,SMALL(IF($A$2:$A$7=2,ROW(C$2:C$7)-ROW(C$2)+1),ROWS(H$2:H4)))=0,"",INDEX(D$2:D$7,SMALL(IF($A$2:$A$7=2,ROW(C$2:C$7)-ROW(C$2)+1),ROWS(H$2:H4)))))}
H5	{=IF(ROWS(H$2:H5)>COUNTIF($A$2:$B$7,2),"",IF(INDEX(D$2:D$7,SMALL(IF($A$2:$A$7=2,ROW(C$2:C$7)-ROW(C$2)+1),ROWS(H$2:H5)))=0,"",INDEX(D$2:D$7,SMALL(IF($A$2:$A$7=2,ROW(C$2:C$7)-ROW(C$2)+1),ROWS(H$2:H5)))))}
H6	{=IF(ROWS(H$2:H6)>COUNTIF($A$2:$B$7,2),"",IF(INDEX(D$2:D$7,SMALL(IF($A$2:$A$7=2,ROW(C$2:C$7)-ROW(C$2)+1),ROWS(H$2:H6)))=0,"",INDEX(D$2:D$7,SMALL(IF($A$2:$A$7=2,ROW(C$2:C$7)-ROW(C$2)+1),ROWS(H$2:H6)))))}
H7	{=IF(ROWS(H$2:H7)>COUNTIF($A$2:$B$7,2),"",IF(INDEX(D$2:D$7,SMALL(IF($A$2:$A$7=2,ROW(C$2:C$7)-ROW(C$2)+1),ROWS(H$2:H7)))=0,"",INDEX(D$2:D$7,SMALL(IF($A$2:$A$7=2,ROW(C$2:C$7)-ROW(C$2)+1),ROWS(H$2:H7)))))}
H8	{=IF(ROWS(H$2:H8)>COUNTIF($A$2:$B$7,2),"",IF(INDEX(D$2:D$7,SMALL(IF($A$2:$A$7=2,ROW(C$2:C$7)-ROW(C$2)+1),ROWS(H$2:H8)))=0,"",INDEX(D$2:D$7,SMALL(IF($A$2:$A$7=2,ROW(C$2:C$7)-ROW(C$2)+1),ROWS(H$2:H8)))))}
H9	{=IF(ROWS(H$2:H9)>COUNTIF($A$2:$B$7,2),"",IF(INDEX(D$2:D$7,SMALL(IF($A$2:$A$7=2,ROW(C$2:C$7)-ROW(C$2)+1),ROWS(H$2:H9)))=0,"",INDEX(D$2:D$7,SMALL(IF($A$2:$A$7=2,ROW(C$2:C$7)-ROW(C$2)+1),ROWS(H$2:H9)))))}
H10	{=IF(ROWS(H$2:H10)>COUNTIF($A$2:$B$7,2),"",IF(INDEX(D$2:D$7,SMALL(IF($A$2:$A$7=2,ROW(C$2:C$7)-ROW(C$2)+1),ROWS(H$2:H10)))=0,"",INDEX(D$2:D$7,SMALL(IF($A$2:$A$7=2,ROW(C$2:C$7)-ROW(C$2)+1),ROWS(H$2:H10)))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Eric G]

there is probable a better way but i came up with

Maybe so, but I had a feeling the solution lied in using an if statement and a double pair of double quotes, as you had done. Just I was at a loss on how to construct it. You have my gratitude - it works; otherwise, I was left with using conditional formatting to camouflage the not so empty text. An idea which falls apart the moment someone copies and special pastes the data as text elsewhere.

I have one more question. Is there a way to "INDEX-MATCH" this formula? My actual data involves many more columns, and the columns I need to pull over aren't necessarily next to each other. I use INDEX-MATCH a lot to pull over what I need and leave behind the rest. Since this formula uses INDEX, I have to imagine it is possible to include the MATCH bit.

In this case, I did try to write the MATCH portion for the column selection into the formula, but failed. Which leaves me with having to get around this problem by manually setting the columns, except it is a tedious affair. Would you know how to automate the column selection based on the destination column header?

If not, no worries, you've resolved my largest obstacle, just this last slice of advice would be the icing on the cake. Thanks, again.

 

[Scott T]

MATCH will always return the first match.

So if you use this formula, it will always return Matthew as that is the row of the first 2 it finds.

=INDEX($B$2:$B$7,MATCH(2,A2:A7,0))

You would have to change the range so it would no longer look at the first match. I am not sure how robust this formula so there could be problems with using this formula.

=IF(ROWS(J$2:J2)>COUNTIF($A$2:$A$9,2),"",IF(INDEX(B2:B$9,MATCH(2,$A2:$A$9,0))=0,"",INDEX(B2:B$9,MATCH(2,$A2:$A$9,0))))

 

[Eric G]

Thanks, Scott. I appreciate the attempt. I'll probably stick with what you gave me from before. I don't anticipate too much future fiddling. I just like ease and flexibility. Nonetheless, without a doubt, the previous solution meets my needs to keep the bossmen happy.