Present data from a binary table

[Solum]

Hi guys!

Im currently working on my master thesis and i hope you can help me on a excel issue.
We are doing an optimization model for a brewery where the output we get is a table with binary values (representing wether to produce a chosen product in a chosen week).
Weeks is in the columns, the products in the rows.
(See the gdoc spreadsheet, INPUT)

What i want to be able to do is to show the products the model chooses to produce presented under the weeks chronologically, like i have written in the gdoc spreadsheet under TO SHOW.
Im not sure what kind of coding is needed for this, but i think you might.

Google docs spreadsheet:
http://spreadsheets.google.com/ccc?k...pTXFjZmc&hl=en

Thanks in advance,
Alexander

[martindwilson]

why not attach an excel workbook ? instead of a link to google docs?

[Solum]

Oh, i thought it might be easier to access it.
I`ll attach the same type of setup here as an excel-file.

Hope somebody out there is able to help.

Alexander

Example.xls

[tony h]

As you are on your master's thesis I will give you a few pointers.

(possible) the easiest way is to use VBA.

When you change the value of a cell the event

Please Login or Register  to view this content.

is fired.
This code exists in the worksheet module NOT in a separate code module.

You can then use the range Target to determine whether it is an appropriate cell that has changed (and do some validation)

Then from the target.row and target.column and value of target you can identify whether a particular combination should appear in your list.

If you have all combination of week and product then it is easy to calculate which line is for a week/product and then hide/unhide the row.


----
you could also use (hidden) rows and columns to hold values for the weeks and productsand do lookups. rather than fixed value calculations.


hope this helps

[Andy Pope]

Depends how rigid your output layout is.

This array formula, commit with CTRL+SHIFT+ENTER, will return the products in order. But you will need a formula for all possible products.

=INDEX($A$1:$A$8,SMALL(IF($B$3:$B$7=1,ROW($B$3:$B$7),ROW($B$8)),ROW(A3:A7)-ROW($A$3)+1))

See attached.

You can use a custom number format to hide the zeros.