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 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.

[Dexsquab]

From the replies to other questions posted here, it would seem the 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 A1:Dxx
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.

Dexsquab wrote:
>
> 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.
>
> --
> Dexsquab
> ------------------------------------------------------------------------
> Dexsquab's Profile: http://www.excelforum.com/member.php...o&userid=30966
> View this thread: http://www.excelforum.com/showthread...hreadid=506786

--

Dave Peterson

[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")



Dexsquab wrote:
>
> 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.
>
> --
> Dexsquab
> ------------------------------------------------------------------------
> Dexsquab's Profile: http://www.excelforum.com/member.php...o&userid=30966
> View this thread: http://www.excelforum.com/showthread...hreadid=506786

--

Dave Peterson

[Domenic]

Assumptions:

A1:B6 contains the source data

E1:P1 contains the month number (1, 2, 3, etc.)

D2:D6 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!

In article <[email protected]>,
Dexsquab <[email protected]>
wrote:

> 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.

[Dexsquab]

Thanks again.

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

In the event that does not work, I shall have to prepare for a ground 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 matrix that looks spot on. However... in that form, it cant total up multiple entries of the same code for the same month. In other words, I end up with a maximum of one value showing in each position. Kinda obvious when I actually look at the function you've written.

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

I'm playing around with it now, seeing if I can get integer values reported in the output matrix. Of course, if any of you exceedingly 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!

In article <[email protected]>,
Dexsquab <[email protected]>
wrote:

> Hmmm, ever so close...
>
> By using the sumproduct function as you've described, I get a matrix
> that looks spot on. However... in that form, it cant total up multiple
> entries of the same code for the same month. In other words, I end up
> with a maximum of one value showing in each position. Kinda obvious
> when I actually look at the function you've written.
>
> What I need to do is count the number of times a specific code shows
> for each month. With up to 500 elements to be counted, you can see
> that many of these values will exceed one.
>
> I'm playing around with it now, seeing if I can get integer values
> reported in the output matrix. Of course, if any of you exceedingly
> helpful people can help, that would be awesome.
>
> I may yet be saved having to launch a ground assault.
>
> Many thanks (once again).