Search column & list all occurances of value offset from string

I need to make a list of items that occur above the string "Room" and the data offset to the bottom and bottom right.

Here is the sample data:
1, 100A
2, Room, Rh
3, 123, 11
3, 200B
4, Room, Rh
5, 456, 24
6, 300C
7, Room, Rh
8, 789, 56
...

On another sheet this is what the output should look like:
1 100A 123 11
2 200B 456 24
3 300C 789 56
...

Here is a copied function that I've been trying to work with. "ROOM_AREAS" is the range in column A. I just can't seem to figure out how the ROW and SMALL functions are supposed to work here.

Please Login or Register  to view this content.

What this gives me is:
100A
200B
300C
...

I'm stuck. Please help!

asuming data starts in a1 try in D1 =OFFSET($A$1,ROW()*3-3,0); in E1 =OFFSET($A$1,ROW()*3-1,0);inF1 =OFFSET($A$1,ROW()*3-1,1)
dragged down

ok there are a few ways to do this one wayuse formulas as on attached sheet and drag down
then autofilter either column c or d on non blanks
select the complete rows copy paste to another sheet

Excellent, Thanks!
I think I can work with a macro to make the filter do what I want, but there is one minor annoyance left. I've attached a dumbed down version of what I'm working on. The "Summary" sheet is where I have the problem (Sorry, a little screwed up after removing some macros). As you can see on the "HeatLoss" sheet at the "200-Room" grouping there can be more than one line of data. I overcame this issue by putting the data used for the summary in columns Q, R, S & T. My problem is the user can't easily copy/paste the data groupings in this configuration. I'm trying to find a way to get rid of the Q,R,S and T columns on the "HeatLoss" sheet. Any ideas of suggestions?

cant see any q,r,s,t columns.
another thing you should avoid is all those merged cells!
if you say put
Floor/Roof Loss [Btu/hr]
all in c3 then select c and d format/ cells/ alignment/horizontal choose centre across selection from the dropdown instead
and for cells above and below each other put a bit in each cell and just remove the gridline between them (or just put in one cell and let it be bigger.)
but it might be an idea to post an original with before and after perhaps a vba solution would be better

Thanks! The un-merging of the cells was a great idea.
I've updated the formatting per your suggestions.
This latest attachment is fully functional for the most part.
I did have the previous version working with VB but the constant user problems with macro security is what prompted me to redo the sheet without macros.

If you check HeatLoss!R35 you'll see that this "hidden" value corresponds with Summary!B5. I would like to get rid of the "hidden" stuff on the HeatLoss sheet so the user can easily copy/paste the groupings. Any ideas?

By the way. I can't thank you enough for your help so far.

i think i may be losing something in the conversion to my version!
ther is no r35 on heatloss a row 35 yes a cell r35 no

still cant quite get what you mean? i thought you were copying from summary sheet?
what exactly is the range you want to copy and what bits shouldnt be in it?
give me an example of the finished paste on a spare sheet how it is now and what youd like it to look like.

try hiding q r s t
then use select visible cells only. 1, add the icon to a tool bar
right click tool bar/customise/choose edit/scroll down to bodom and youll see the select visible cells icon (looks like 4 boxes in a grid, drag to tool bar
(now thats in 2003 as you have 2007 youll prob have to do it some wierd and wonderful way involving the ribbon)
then before copying sheet click that icon and hidden cells are ignored
ok i found 2007 here
http://office.microsoft.com/en-us/ex...448971033.aspx