converting database of transactions to a time series

**gren** · 03-08-2012, 05:52 PM

I have a database of items whether they went "in stock" or "out of stock" on a specific date. I'm trying to convert that to a table that shows for EVERY date is an item was "in stock" or "out of stock". I'm thinking my starting point is a pivot table that gets a list of horizontal list of every item in the database. But I can't figure out a formula that says for any given date, was the item in stock or not.

I've attached a simplified version of my problem, and I'd love to hear any ideas.

Thanks.

**rylo** · 03-08-2012, 09:33 PM

Hi

Try this

B5:

Please Login or Register  to view this content.

Copy across / down as required.

HTH

rylo

**gren** · 03-08-2012, 10:41 PM

That works incredibly well. I've got to spend some time figuring out how it works... Does the --(database!$B$2:$B$8=Table!B$4) creates of vector of 0s for false and 1s for true? And the subtraction turns "overlaps" into 0s? Thanks so much.