Creating A New Table

[carl]

My data table is like this:

BOX005 BOX009 BOX017 BOX050 BOX060
BOX005 30
BOX009
BOX017 430 81
BOX050 30 81 546 46
BOX060 46
BOX074 20
BOX226 15 5
BOX233
BOX325
BOX333 7 56

Is it possible, on another sheet to do the following:

In a cell, enter one of the values in row 1 above and then have a table fill
in like this (in this case BOX050 was entered):

BOX050 546
BOX017 81
BOX333 56
BOX060 46
BOX005 30
BOX074 20
BOX226 5
BOX009
BOX233
BOX325

So BOX050 was entered and the new table displayed the column with BOX050 in
row1 in a decending sort.

I hope I explained this clear enough.

Thank you in advance.

 

[Max]

One way ..

Assume source table is in a sheet named: X
within cols A to F

In another sheet: Y (say),
assume B1 will house the input for the box, eg: BOX050

Put in A2:
=IF($B$1="","",OFFSET(X!A:A,,MATCH($B$1,X!$1:$1,0)-1)-ROW()/10^10)
(Leave A1 blank)

Put in B2:
=IF($B$1="","",IF(ROW(A1)>COUNTA(X!$A:$A),"",INDEX(X!A:A,MATCH(LARGE($A:$A,ROW(A1)),$A:$A,0))))

Put in C2:
=IF($B$1="","",IF(ROW(A1)>COUNTA(X!$A:$A),"",INDEX(OFFSET(X!A:A,,MATCH($B$1,X!$1:$1,0)-1),MATCH(LARGE($A:$A,ROW(A1)),$A:$A,0))))

Select A2:C2, copy down to cover the max expected extent of the source table
in X, eg copy to C50. Hide away col A. Cols B and C will return the required
results, ie the full list of boxes & corresponding values for the box number
input in B1, sorted in descending order by the values.

For a neater look, suppress extraneous zeros from displaying in the sheet
via clicking: Tools > Options > View tab > Uncheck "Zero values" > OK

 

[Teethless mama]

Assuming your data from A1 to F11, and your criteria start from A15 and
"BOX050"

=INDEX($A$1:$F$11,MATCH("BOX050",$A$1:$F$1,"0),MATCH($A15,$A$1:$A$11,0))

 

[Max]

Here's a sample construct to illustrate:
http://www.savefile.com/files/326545
Auto-descending sort by selectable col header.xls

 

[Max]

Email received from OP:
------------ ------
From: " ... Carl"
To: "'(e-mail address removed)'"
Subject: Excel Discussion Page / http://savefile.com/projects/236895
Date: Mon, 11 Dec 2006 15:13:23 -0500
Hi MAX. Thanks for helping with my excel question.
The solution works well for me ...