Create a conditional transposed list into new worksheet given certain criteria

[professorcrabb]

Hi there.

I have a worksheet with a vertical list of room names which I would like to transpose horizontally into another worksheet. The rooms should be transposed only if they should be included in the audit.

In the attached example I would like the room names located in worksheet 'S+ Rooms' (column A) to be transposed horizontally into worksheet 'HCOCC' (row 1, with the first room name at D1) if and only if there is a 'Y' in the cell adjacent to the room name in worksheet 'S+ Rooms' (column B).

I would also like advice on how to create a new vertical list in worksheet 'AUDITHC' of those room names in 'S+ Rooms' which have a 'Y' in the adjacent cell.

Thanks in advance for any pointers on how to solve this, I'm completely baffled.

(ideally not using VBA)

[ChemistB]

One way is with Array formulas. In HCOCC D4 copied to the right, enter as an ARRAY

=IFERROR(INDEX('S+ ROOMS'!$A$2:$A$420, SMALL(IF('S+ ROOMS'!$B$2:$B$420="Y", ROW($B$2:$B$420)-1),COLUMNS($A$1:A$1))),"")


...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.

Almost Identical formula for Part 2 of your question

=IFERROR(INDEX('S+ ROOMS'!$A$2:$A$420, SMALL(IF('S+ ROOMS'!$B$2:$B$420="Y", ROW($B$2:$B$420)-1), ROWS($A$1:$A1))),"")