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

[simaya]

Hi excel experts,hv a question hope someone can help me...
i need to rearrange different numbers from different rows into one row and in consecutive columns
current aspect of my table:
0 1 2 3 4 5 7 8 10
TSE1 TBE1 TNM1 TST1 TBE2 TSMF TTM1 TNMF TSE2
X

6 11 12 13 14 16 17 18 20 21 22 23 24 25 26 27 28 39 50 51 52 58
CS07 CB80 CS80 CT61(37) CL80 CS81 CS03 CS01 CS04 DT30 CS12 CB07 CC37 AB01 RB01 CC30 CC54 AL16 PS01 PS02 PS03 CC61
x

15 45 46 47 49 53 54 55 61 63 64 65 66 67 68 69 83 93 94
CS05 GP01 GP02 PP01 PS04 QP01 QS02 QS03 AS12 AC37 AP16 AP26 DT28 AL20 GP03 FSZ3 GP04 RP01 RP02
x

29 30 31 32 33 34 35 36 37 41 42 60 62 95 96 99
ER02 NM60 ED09 EP01 EN12 EM02 ER01 ENMX EM03 EM82 EM61 FEP9 EP02 EHT1 EBP1 EHM1
X X X
desired result:
2 6 30 31 32 61

is that possible ?
plz find attached an example file

[Richard Buttrey]

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.

[MarvinP]

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

[simaya]

Tks richard for ur reply,
i'm not gd with with macros,is it possible to hv the answer with formulas.
Regards

[XOR LX]

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

[Richard Buttrey]

@xor

Again...nice!

Been a good day for array formulae....

[simaya]

Tks again guys for ur help,the solution proposed by XOR was a gr8 help.Thank you XOR .
well, till next time...!
Hv a nice day

[Xx7]

Try this...