Converting a table into a dynamic list

[nickw90]

Hi all,

Not sure if this has been posted or answered already, all searches I tried had results that didn't quite match what I was after.

I'm trying to generate a list that will streamline ordering of timber lengths - currently I type out a list in an email of each quantity and length of each material that is required. This is time consuming and considering the repetitive nature of the process, I am sure there is a way to speed this up. I would do this up to 10 times per day.

I have a grid that represents timber materials down the columns and material lengths across the rows. Quantities of each material/length are added within the cells.
I want to be able to display a list of the results in descending order of the selected materials and lengths as shown:
grid.JPG
list.JPG

LVL Order-public.xlsx

(forgive the formatting)

Cheers

*Edited to update result pic*

[josephteh]

Type in the material in AF11,
D6=SUMIFS($AG$10:$AG$13,$AF$10:$AF$13,$C6,$AH$10:$AH$13,D$5), or =SUMPRODUCT(($AG$10:$AG$13)*($AF$10:$AF$13=$C6)*($AH$10:$AH$13=D$5))
copy across and down

[TMS]

@josephteh: I think the OP wants to create the list from the table entries, not the table entries from the list.

[nickw90]

that's correct, the table will be cleared once I move to the next order so I want to be able to generate a list or report based on the data in the table at any given time

[wk9128]

Cell D6 formula , Drag down and across

Please Login or Register  to view this content.

Cell AF10 formula , Drag down

Please Login or Register  to view this content.

Cell AH10 formula , Drag down

Please Login or Register  to view this content.

Cell AG10 formula , Drag down

Please Login or Register  to view this content.

[nickw90]

still not quite what i'm after - the values in the main (larger table) will change with each order i need to make so i need the list on the right hand side to update automatically based on the values (quantities/lengths) of materials selected in the main table

[Alf]

Perhaps a macro based solution like this could be of help.

To test run macro GetData. Macro clears data result starting in B20, it the loops through all rows from 3 to 16 (stop value of 16 set by macro). If sum of row from C to Z is equal to 0 the row gets hidden.
Both C and Z is hardcoded in macro. Not the best of solutions.

Then macro checks sum of columns from C to Z and hiding columns that sums to 0. End point for macro i.e. Z column is set by macro. The condensed result is the copied to B20.

Alf

[josephteh]

@josephteh: I think the OP wants to create the list from the table entries, not the table entries from the list.

Oh.. ok.. thanks, Trevor!

[Czeslaw]

Power Query

Please Login or Register  to view this content.

[ardross]

AF10:
=IFERROR(INDEX(C:C,AGGREGATE(15,6,ROW($D$6:$AB$19)/($D$6:$AB$19<>""),ROWS(AF$10:AF10))),"")

AG10:
=IFERROR(INDEX($1:$1048576,AGGREGATE(15,6,ROW($D$6:$AB$19)/($D$6:$AB$19<>""),ROWS(AG$10:AG10)),10^6*MOD(AGGREGATE(15,6,ROW($D$6:$AB$19)+(COLUMN($D$6:$AB$19)*10^-6)/($D$6:$AB$19<>""),ROWS(AG$10:AG10)),1)),"")

AH10:
=IFERROR(INDEX($5:$5,MOD(AGGREGATE(15,6,ROW($D$6:$AC$19)*10^6+COLUMN($D$5:$AB$5)/($D$6:$AC$19>0),ROWS(AH$10:AH10)),10^6)),"")

Don't go out of your way to intrroduce blank cells in your data (as in your expected results). This might look pretty, but will make formulas more complicated than needed!!

[nickw90]

AF10:
=IFERROR(INDEX(C:C,AGGREGATE(15,6,ROW($D$6:$AB$19)/($D$6:$AB$19<>""),ROWS(AF$10:AF10))),"")

AG10:
=IFERROR(INDEX($1:$1048576,AGGREGATE(15,6,ROW($D$6:$AB$19)/($D$6:$AB$19<>""),ROWS(AG$10:AG10)),10^6*MOD(AGGREGATE(15,6,ROW($D$6:$AB$19)+(COLUMN($D$6:$AB$19)*10^-6)/($D$6:$AB$19<>""),ROWS(AG$10:AG10)),1)),"")

AH10:
=IFERROR(INDEX($5:$5,MOD(AGGREGATE(15,6,ROW($D$6:$AC$19)*10^6+COLUMN($D$5:$AB$5)/($D$6:$AC$19>0),ROWS(AH$10:AH10)),10^6)),"")

Don't go out of your way to intrroduce blank cells in your data (as in your expected results). This might look pretty, but will make formulas more complicated than needed!!

absolutely nailed it mate! Perfect, exactly what i was after
thanks for the help

[wk9128]

Cell AF10 formula , Drag down

Please Login or Register  to view this content.

Cell AG10 formula , Drag down

Please Login or Register  to view this content.

OR

Please Login or Register  to view this content.

Cell AH10 formula , Drag down

Please Login or Register  to view this content.

OR

Please Login or Register  to view this content.