Turning Two Columns of Data into an Array

[Dexsquab]

Or, more precisely, an array-like result.

Greetings.

I have two columns of data, one being a date, the other being a relate
(integer) code. I need to output the two columns into a grid.

I start with for example:
Date.......... Code
1/04/2005... 5
7/04/2005... 3
23/06/2005.. 1
28/09/2005.. 1
13/10/2005.. 2

and would need as output from the above as:
...............................month............................
....1....2....3....4....5....6....7....8....9....10...11...12
1...............................1...............1...................
2......................................................1.............
3....................1...............................................
4.....................................................................
5....................1................................................

(hope the formatting holds enough for that to make sense.

When I was given the spreadsheet, it determined the answer by using
specific column for each option. I'm about to amend it, and can do s
by giving each element in the grid a specific value (requiring only on
column to do all the calcs).

What I'd like to know is if there is any method I can use to directl
determine the value that should be placed in the above grid withou
requiring additional columns.

Basically, first box should be [count all elements with both code 1 i
month 1]. Next box would be [sount of code 1 in month 2], etc.

Any ideas?

Many thanks, either way

 

[Dexsquab]

From the replies to other questions posted here, it would seem th
answer is a pivot table.

Many thanks to you helpful people.

Cheers

 

[Dave Peterson]

How about this...

Add a couple of extra columns to your original data.

In C1, put: Month
in C2, put: =month(a2)
and drag down

In D1, put: Day
in D2, put: =day(a2)
and drag down.

Now select your range A1xx
data|pivottable
follow the wizard until you get to the dialog with the Layout button on it.
click that layout button.
drag the Month button to the column Field
drag the day button to the row field
drag the code field to the data field
if you want it to count the entries, double click on it and choose "Count"
If you want it to sum the entries, double click on it and choose "Sum"
If you want both, drag another code button to the data field

And finish up.

Or, more precisely, an array-like result.

Greetings.

I have two columns of data, one being a date, the other being a related
(integer) code. I need to output the two columns into a grid.

I start with for example:
Date.......... Code
1/04/2005... 5
7/04/2005... 3
23/06/2005.. 1
28/09/2005.. 1
13/10/2005.. 2

and would need as output from the above as:
..............................month............................
...1....2....3....4....5....6....7....8....9....10...11...12
1...............................1...............1...................
2......................................................1.............
3....................1...............................................
4.....................................................................
5....................1................................................

(hope the formatting holds enough for that to make sense.

When I was given the spreadsheet, it determined the answer by using a
specific column for each option. I'm about to amend it, and can do so
by giving each element in the grid a specific value (requiring only one
column to do all the calcs).

What I'd like to know is if there is any method I can use to directly
determine the value that should be placed in the above grid without
requiring additional columns.

Basically, first box should be [count all elements with both code 1 in
month 1]. Next box would be [sount of code 1 in month 2], etc.

Any ideas?

Many thanks, either way.

 

[Dave Peterson]

Ps. If you really care about the year and month, then I'd use this in the Month
column:

=text(a2,"yyyy-mm")

Or, more precisely, an array-like result.

Greetings.

I have two columns of data, one being a date, the other being a related
(integer) code. I need to output the two columns into a grid.

I start with for example:
Date.......... Code
1/04/2005... 5
7/04/2005... 3
23/06/2005.. 1
28/09/2005.. 1
13/10/2005.. 2

and would need as output from the above as:
..............................month............................
...1....2....3....4....5....6....7....8....9....10...11...12
1...............................1...............1...................
2......................................................1.............
3....................1...............................................
4.....................................................................
5....................1................................................

(hope the formatting holds enough for that to make sense.

When I was given the spreadsheet, it determined the answer by using a
specific column for each option. I'm about to amend it, and can do so
by giving each element in the grid a specific value (requiring only one
column to do all the calcs).

What I'd like to know is if there is any method I can use to directly
determine the value that should be placed in the above grid without
requiring additional columns.

Basically, first box should be [count all elements with both code 1 in
month 1]. Next box would be [sount of code 1 in month 2], etc.

Any ideas?

Many thanks, either way.

 

[Domenic]

Assumptions:

A1:B6 contains the source data

E11 contains the month number (1, 2, 3, etc.)

D26 contains the code

Formula:

E2, copied down and across:

=IF(SUMPRODUCT(--($A$2:$A$6<>""),--(MONTH($A$2:$A$6)=E$1),--($B$2:$B$6=$D
2)),1,"")

Hope this helps!

 

[Dexsquab]

Thanks again.

I will see how SumProduct behaves. Fingers crossed, that will sort i
(looks like it shall).

In the event that does not work, I shall have to prepare for a groun
assault... err... I mean, set up a pivot table.

Yes.

You people are worth more ducats

 

[Dexsquab]

Hmmm, ever so close...

By using the sumproduct function as you've described, I get a matri
that looks spot on. However... in that form, it cant total up multipl
entries of the same code for the same month. In other words, I end u
with a maximum of one value showing in each position. Kinda obviou
when I actually look at the function you've written.

What I need to do is count the number of times a specific code show
for each month. With up to 500 elements to be counted, you can se
that many of these values will exceed one.

I'm playing around with it now, seeing if I can get integer value
reported in the output matrix. Of course, if any of you exceedingl
helpful people can help, that would be awesome.

I may yet be saved having to launch a ground assault.

Many thanks (once again)

 

[Domenic]

In that case, try the following formula instead...

=SUMPRODUCT(--($A$2:$A$6<>""),--(MONTH($A$2:$A$6)=E$1),--($B$2:$B$6=$D2))

You'll notice that the formula will return zero when no instances occur.
If you want, you can custom format your cells to hide these zero
values...

Format > Cells > Number > Custom > Type: 0;-0;;@

Hope this helps!