Hi,

Another approach using formulas:

Let's us suppose that your matrix is in columns A thru E (for your sample
data, it is A1:E6). Create 4 new columns (say, G, H, I, and J) with the
following formulas in Row 1 (i.e., G1, ...J1); of course, modify the column A
range in the formulas (here showing as "$A$2:$A$6") to suit to your data.

=IF(ROW()4*COUNTA($A$2:$A$6),"delete",OFFSET($A$2 ,MOD(ROW()-1,COUNTA($A$2:$A$6)),0))

=OFFSET($B$1,0,QUOTIENT(ROW()-1,COUNTA($A$2:$A$6)))

=OFFSET($B$2,MOD(ROW()-1,COUNTA($A$2:$A$6)),QUOTIENT(ROW()-1,COUNTA($A$2:$A$6)))

=ROW()+IF(OFFSET($B$2,MOD(ROW()-1,COUNTA($A$2:$A$6)),QUOTIENT(ROW()-1,COUNTA($A$2:$A$6)))=0,100000,0)

Autofill the formulas down the rows generously. Delete the rows at the
bottom where column G shows "delete".

These columns are still linked to the original matrix. To make them
independent, select and copy the entire area of the new columns, "Edit" --
"Paste Special" --"Values" -- "OK".

Sort the new columns by Column J ascending, and delete the rows at the
bottom where Column J shows numbers greater than 100000).

Delete the last column (Column J)

Regards,
B. R. Ramachandran


"RD Wirr" wrote:

Just to illustrate what I am talking about here, data looks like this:

Upper Lower Inner Outer
TD 01360 1 1 0 1
TD 01373 2 2 0 1
TD 01361 0 2 0 1
TD 01364 0 2 0 1
TD 01378 3 0 1 0

and I am trying to work out a formula that will transpose it into a filtered
(0) column that looks like this:

TD 01360 Upper 1
TD 01373 Upper 2
TD 01378 Upper 3
TD 01360 Lower 1
TD 01373 Lower 2
TD 01361 Lower 2
TD 01364 Lower 2
TD 01378 Inner 1
TD 01360 Outer 1
TD 01373 Outer 1
TD 01361 Outer 1
TD 01364 Outer 1

Thanks in advance,
RDW