Rearrange different numbers from different rows in one row and in consecutive columns

Hi,

One way - change the C5:X23 range as necessary or better still set it with a variable that detects the data range

Please Login or Register  to view this content.

@ Richard,

I was going to write some VBA for this problem but decided to see if I could do it using Array formulas. I got so close using Min and Max but am having problems solving it with Small( If(arrays), Columns()-2). What am I missing?? I can use the Array formulas with Min and Max but I'm missing the Small conversion. See the attached and I've got the green cells figured. Now how about using Small for the yellow cells.

@MarvinP

Apologies for butting in, since I see that your question was aimed at Richard, but I think you can use this array formula**:

=SMALL(IF($C$3:$X$23="X",$C$1:$X$21),COLUMNS($A:A))

and drag to the right.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

@xor

Again...nice!

Been a good day for array formulae....