Pulling Data from an Array
 

I have a production schedule that has dates accross the top and models
down the side. I want to be able to summarize the products that need to
be build each day instead of showing all the data.

Example Data Source

11/1/2005 11/2/2005 11/3/2005 11/4/2005 11/5/2005 11/6/2005
FF100 25 50 180 0 0 0
FF101 0 100 0 0 0 0
FF102 25 0 0 75 150 0
FF103 0 0 0 0 0 150
FF104 0 0 0 0 0 0
FF105 25 100 0 0 0 0
FF106 0 0 0 0 0 0
FF107 50 25 0 75 25 0
FF108 25 0 0 0 0 0

Expected Summary


FF100 11/1/2005 25
FF102 11/1/2005 25
FF105 11/1/2005 25
FF107 11/1/2005 50
FF108 11/1/2005 25
FF100 11/2/2005 50
FF101 11/2/2005 100
FF105 11/2/2005 100
FF107 11/2/2005 25
FF100 11/3/2005 180
FF102 11/4/2005 75
FF107 11/4/2005 75
FF102 11/5/2005 150
FF107 11/5/2005 25

I have tried various functions but can not get it to work. Anyone have
a suggestion? Thanks in advance!

Pulling Data from an Array
 

11/1/2002 11/2/2002 11/3/2002 11/4/2002 11/5/2002
11/6/2002
FF100 25 51 180 0 0
0
FF101 0 100 0 0 0
0
FF102 26 0 0 75
150 0
FF103 0 0 0 0
0 151
FF104 0 0 0 0
0 0
FF105 27 101 0 0
0 0
FF106 0 0 0 0
0 0
FF107 50 29 0 76
30 0
FF108 28 0 0 0
0 0

model date quantity
FF100 11/1/2002 25
FF101 11/1/2002 0
FF102 11/1/2002 26
FF103 11/1/2002 0
FF104 11/1/2002 0
FF105 11/1/2002 27
FF106 11/1/2002 0
FF107 11/1/2002 50
FF108 11/1/2002 28
FF100 11/2/2002 51
FF101 11/2/2002 100
FF102 11/2/2002 0
FF103 11/2/2002 0
FF104 11/2/2002 0
FF105 11/2/2002 101
FF106 11/2/2002 0
FF107 11/2/2002 29
FF108 11/2/2002 0
FF100 11/3/2002 180
..... ..... .....

Tools Options General R1C1 reference
Select the first array with all headers and
Insert Name Define array_gg
Select the 9 x 6 array of numbers and
Insert Name Define array_ff
Also define these names:
colm2 Refers To
=ROW(INDEX(C1,1):INDEX(C1,ROWS(array_ff)*COLUMNS(a rray_ff)))
colm3 Refers To
=FLOOR(colm2-1,ROWS(array_ff))/ROWS(array_ff)+1
rowm2 Refers To
=MOD(colm2-1,ROWS(array_ff))+1
Enter these array formulas (SCE) into the 9 x 6 rows deep
model, date and quantity columns:
=INDEX(array_gg,rowm2+1,1)
=INDEX(array_gg,1,colm3+1)
=INDEX(array_ff,rowm2,colm3)
Remove the rows with zero quantity with
Copy, Paste Value,
Advanced Filter,
Copy to another location, Criteria quantity <0
Change back to A1 reference if you prefer.