Pull Chosen Data Only

Eric G

New Member
Joined
Dec 21, 2017
Messages
47
How do I pull only certain data from one section to another section?

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

[TABLE="class: grid, width: 777"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ORG[/TD]
[TD]FIRST[/TD]
[TD]LAST[/TD]
[TD]DATE[/TD]
[TD][/TD]
[TD]FIRST[/TD]
[TD]LAST[/TD]
[TD]DATE[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2[/TD]
[TD]Matthew[/TD]
[TD]Johnson[/TD]
[TD]5/2/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2[/TD]
[TD]Mark[/TD]
[TD]Anderson[/TD]
[TD]2/7/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2[/TD]
[TD]Luke[/TD]
[TD]Jones[/TD]
[TD]8/7/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]Esau[/TD]
[TD]Smith[/TD]
[TD]1/1/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]Cain[/TD]
[TD]Williams[/TD]
[TD]3/17/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]2[/TD]
[TD]John[/TD]
[TD]Miller[/TD]
[TD]3/16/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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:

[TABLE="class: grid, width: 777"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ORG[/TD]
[TD]FIRST[/TD]
[TD]LAST[/TD]
[TD]DATE[/TD]
[TD][/TD]
[TD]FIRST[/TD]
[TD]LAST[/TD]
[TD]DATE[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2[/TD]
[TD]Matthew[/TD]
[TD]Johnson[/TD]
[TD]5/2/2015[/TD]
[TD][/TD]
[TD]Matthew<strike></strike>[/TD]
[TD]Johnson<strike></strike>[/TD]
[TD]5/2/2015<strike></strike>[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2[/TD]
[TD]Mark[/TD]
[TD]Anderson[/TD]
[TD]2/7/2014[/TD]
[TD][/TD]
[TD]Mark<strike></strike>[/TD]
[TD]Anderson<strike></strike>[/TD]
[TD]2/7/2014<strike></strike>[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2[/TD]
[TD]Luke[/TD]
[TD]Jones[/TD]
[TD]8/7/2014[/TD]
[TD][/TD]
[TD]Luke<strike></strike>[/TD]
[TD]Jones<strike></strike>[/TD]
[TD]8/7/2014<strike></strike>[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]Esau[/TD]
[TD]Smith[/TD]
[TD]1/1/2015[/TD]
[TD][/TD]
[TD]John<strike></strike>[/TD]
[TD]Miller[/TD]
[TD]3/16/2017<strike></strike>[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]Cain[/TD]
[TD]Williams[/TD]
[TD]3/17/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]2[/TD]
[TD]John[/TD]
[TD]Miller[/TD]
[TD]3/16/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

What are some ways I could accomplish this?
 
Last edited:

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Excel 2010
ABCDEFGH
1ORGFIRSTLASTDATEFIRSTLASTDATE
22MatthewJohnson5/2/2015MatthewJohnson5/2/2015
32MarkAnderson2/7/2014MarkAnderson2/7/2014
42LukeJones8/7/2014LukeJones8/7/2014
5EsauSmith1/1/2015JohnMiller3/16/2017
6CainWilliams3/17/2016   
72JohnMiller3/16/2017   
Sheet4
Cell Formulas
RangeFormula
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.
 
Upvote 0
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,

[TABLE="class: grid, width: 777, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]ORG[/TD]
[TD]FIRST[/TD]
[TD]LAST[/TD]
[TD]DATE[/TD]
[TD][/TD]
[TD]FIRST[/TD]
[TD]LAST[/TD]
[TD]DATE[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2[/TD]
[TD]Matthew[/TD]
[TD]Johnson[/TD]
[TD][/TD]
[TD][/TD]
[TD]Matthew[/TD]
[TD]Johnson
[/TD]
[TD]1/01/1900[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2[/TD]
[TD]Mark[/TD]
[TD][/TD]
[TD]2/7/2014[/TD]
[TD][/TD]
[TD]Mark[/TD]
[TD]0[/TD]
[TD]2/7/2014[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]Esau[/TD]
[TD]Smith[/TD]
[TD]1/1/2015[/TD]
[TD][/TD]
[TD]John[/TD]
[TD]Miller[/TD]
[TD]3/16/2017[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]2[/TD]
[TD]John[/TD]
[TD]Miller[/TD]
[TD]3/16/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

If the source cell is empty, the destination cell needs to be empty too. Is there a solution?
 
Upvote 0
There is probable a better way but I came up with


Excel 2010
ABCDEFGH
1ORGFIRSTLASTDATEFIRSTLASTDATE
22MatthewJohnsonMatthewJohnson 
32Mark2/7/2014Mark 2/7/2014
42LukeJones8/7/2014LukeJones8/7/2014
5EsauSmith1/1/2015JohnMiller3/16/2017
6CainWilliams3/17/2016   
72JohnMiller3/16/2017   
8   
9   
10   
Sheet1
Cell Formulas
RangeFormula
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.
 
Upvote 0
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.
 
Upvote 0
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.
Code:
=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.
Code:
=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))))
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top