Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort a Table into one long list
I have a table with row and column headings. Basically it's a grid of prices
for styles and materials. I'd like to sort those prices and have the row, column headings displayed next to the list - sort of like this cherry ash oak plain 12.50 10 11.75 fancy 19.50 12 15 sorting into this 10 plain, ash 11.75 plain oak 12 fancy ash 12.50 plain cherry 15 fancy oak 19.50 fancy cherry Is it possible? What's the right tool? Is Excel the right place? THanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort a Table into one long list
A looping macro that looked at each number and used the left column and the
top row to add to it. -- Don Guillett Microsoft MVP Excel SalesAid Software "Stoke" wrote in message ... I have a table with row and column headings. Basically it's a grid of prices for styles and materials. I'd like to sort those prices and have the row, column headings displayed next to the list - sort of like this cherry ash oak plain 12.50 10 11.75 fancy 19.50 12 15 sorting into this 10 plain, ash 11.75 plain oak 12 fancy ash 12.50 plain cherry 15 fancy oak 19.50 fancy cherry Is it possible? What's the right tool? Is Excel the right place? THanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort a Table into one long list
One way to transform it with ease into the desired columnar listings
Assume your source table as posted is in Sheet1's A1:D3 where B1:D1 contains the 3 "wood" types: cherry, ash, oak and in A2 down a plain, fancy, etc In another sheet, place: In A2: =OFFSET(Sheet1!$B$2,INT((ROWS($1:1)-1)/3),MOD(ROWS($1:1)-1,3)) In B2: =OFFSET(Sheet1!$A$2,INT((ROWS($1:1)-1)/3),) In C2: =INDEX(Sheet1!$B$1:$D$1,MOD(ROWS($1:1)-1,3)+1) Select A2:C2, copy down as far as required to exhaust the source data Adapt the "3" and the INDEX ref range: Sheet1!$B$1:$D$1 within the expressions to suit the no. of source cols for "wood" types -- Max Singapore http://savefile.com/projects/236895 Downloads:20,500 Files:365 Subscribers:65 xdemechanik --- "Stoke" wrote: I have a table with row and column headings. Basically it's a grid of prices for styles and materials. I'd like to sort those prices and have the row, column headings displayed next to the list - sort of like this cherry ash oak plain 12.50 10 11.75 fancy 19.50 12 15 sorting into this 10 plain, ash 11.75 plain oak 12 fancy ash 12.50 plain cherry 15 fancy oak 19.50 fancy cherry Is it possible? What's the right tool? Is Excel the right place? THanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort a Table into one long list
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort a Table into one long list
Thank you so much - it is really amazing to me to be taught something by
someone across the world - and in minutes after I ask the question! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort a Table into one long list
Welcome, glad it got you going.
-- Max Singapore http://savefile.com/projects/236895 Downloads:20,500 Files:365 Subscribers:65 xdemechanik --- "Stoke" wrote in message ... Thank you so much - it is really amazing to me to be taught something by someone across the world - and in minutes after I ask the question! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I sort a pivot table page list? | Excel Discussion (Misc queries) | |||
pull data from a table and sort into list | New Users to Excel | |||
Summarised list from long list | Excel Discussion (Misc queries) | |||
Pivot Table Sort & Top List | Excel Discussion (Misc queries) | |||
Setting up a random list from long list of names ? | Excel Discussion (Misc queries) |