Copy data in column based on data from another

Hi all!

I have a conundrum that I can't quite seem to square out and am hoping someone can help. Here it is:

I have a worksheet called "Location". Within this worksheet is data in column B starting on row 3. The user selects the data by placing a corresponding "X" in column A. I'd like to have a code/formula that says, If there is an "X" in column A, copy the data starting in row 31 without empty rows. So this:

___A___|_____B___|
1___X___|__Orange_|
2_______|__Apple__|
3___X___|__Pear___|

Would be copied as:

__|__A____|____B____|
1_|__X____|__Orange_|
2_|_______|__Apple__|
3_|__X____|__Pear___|
...|_______|_________|
31|_Orange|_________|
32|_Pear__|_________|

I've tried "IF" formulas, but they don't skip the blanks (or cause redundancies in the copied data), and I haven't been able to get a macro to do it either. I've tried to look up some examples, but they all deal with moving the data to a different sheet and the code does not work for copying to the same sheet.

Any suggestions?

Thanks!

Put this formula in C1:

=IF(A1="X",COUNTIF(A$1:A1,"X"),"")

and copy down. Then in A31 you can have this formula:

=IFERROR(INDEX(B:B,MATCH(ROWS($1:1),C:C,0)),"")

then copy this down.

Hope this helps.

Pete

That worked perfectly! Thanks a ton!