copy the frist value then the second etc

[josh-mediwatch]

so if i have

A B
H 1
M 2
H 3
L 4
M 5
L 6

and i want to a cell to look for the first H value and paste then in the cell after that the second H value and paste how do i do it?

[JBeaucaire]

Assuming:

1) Column A is the values to match
2) Column B is the values to return

3) D2 holds the value you want to match, "H" in this example
4) You want a formula in E2 you can copy to the right to get the remaining values that match E2,
and you can copy down to the next row to match the value in E3, etc...

Then this ARRAY formula should be put into cell E2:
=IF(COLUMNS($E:E)>COUNTIF($A:$A, $D2), "", INDEX($B$1:$B$100, SMALL(IF($A$1:$A$100=$D2, ROW($A$1:$A$100), ""), COLUMNS($E:E))))
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

Then copy E2 to the right about 6-10 cells to get the remaining matches. Then copy those cells down rows for other matches to values you enter in D3, D4, etc.

http://screencast.com/t/vvsUuLk76hIt

[Tony Valko]

Here's another one.

With your data in the range A2:B7...

D2 = H

Enter this array formula** in E2:

=IFERROR(INDEX(B:B,SMALL(IF(A$2:A$7=D$2,ROW(A$2:A$7)),ROWS(E$2:E2))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.

[Marcol]

This addition to Jerrys' workbook might be useful.

Assuming your data in Column A is text and there are no blanks, then Column D extracts the unique values from Column A.

[josh-mediwatch]

if i use =VLOOKUP("H",'Day 1'!A11:H29,8,FALSE) i then want it to move on to the next H value

[Tony Valko]

VLOOKUP can't do that.

[josh-mediwatch]

i have constructed a document to show my use;
the H, M, L results are randomized over the 20 days (ive only shown 2 days) i need to collate them all on the results page


run example.xlsx

[Tony Valko]

Just looking at your file ... I can't tell what you're wanting to do.

How about explaining what results you want and where you want them.

[josh-mediwatch]

sorry I am struggling to explain slightly

for instance Day1 H12 needs to go in Results B3
DAY1 H15 needs to go in Results C3

hope this is clearer

[Tony Valko]

OK, that helped.

One more question...

Will there always be 2 instances of "H" per "run" ? It looks like it in the sample file.

If so, then you can use a simple VLOOKUP to get the 1st instance and a less complicated formula than what we've suggested in our other replies to get the 2nd instance.

[josh-mediwatch]

yep always 2 H 2 M and 2 L

any help on this would be great i have 320 pieces of data i dont really want to use a copy function for them all

[Tony Valko]

Try these...

Entered in Results B3:

=VLOOKUP(LEFT(B2),'Day 1'!$A$12:$H$18,8,0)

Entered in Results C3:

=LOOKUP(2,1/('Day 1'!A12:A18=LEFT(C2)),'Day 1'!$H$12:$H$18)

Your data structure is not conducive to easily copying the formulas to the other tables!

With the current structure it would be possible to come up a single formula that could be copied however that formula would be very complicated and inefficient.