I have got a bit of a problem i cannot seem to work out. I have got 3
coloums of data, column A has town names, say towna townb townc etc,
the same town name will be repeated within the column several times or
sometimes only once. In coloum 3 is various data say data1, data2,
data3, data4 etc. I need to summarise the information i.e I need to
now how many times data1 was in the same row as towna, townb, townc.
How may times data2 was in towna,townb,townc until I have counted
every different piece of data in column 3 and assigned that number to
every different town. Hope this makes sense, I have been trying to do
it with a loop but i am getting know where fast. Any help much
appreaciated

Johny

On Thu, 27 Mar 2008 03:13:14 -0700 (PDT),
wrote:

I have got a bit of a problem i cannot seem to work out. I have got 3
coloums of data, column A has town names, say towna townb townc etc,
the same town name will be repeated within the column several times or
sometimes only once. In coloum 3 is various data say data1, data2,
data3, data4 etc. I need to summarise the information i.e I need to
now how many times data1 was in the same row as towna, townb, townc.
How may times data2 was in towna,townb,townc until I have counted
every different piece of data in column 3 and assigned that number to
every different town. Hope this makes sense, I have been trying to do
it with a loop but i am getting know where fast. Any help much
appreaciated

Johny


If this is what you have in columns A and B

ta d1
tb d1
ta d2
ta d2
tb d3
tc d1
ta d2
tc d1

And this is what you want in say columns C to F

d1 d2 d3
ta 1 3 0
tb 1 0 1
tc 2 0 0

Then you could use the following formula in cell D2 and copy it down
and to the right:

=SUMPRODUCT(($A$1:$A$8=$C2)*($B$1:$B$8=D$1))

This solution assumes that you know all t's and d's in advance.

Hope this helps / Lars-Åke

On Mar 27, 10:29*am, Lars-Åke Aspelin
wrote:
On Thu, 27 Mar 2008 03:13:14 -0700 (PDT),
wrote:

I have got a bit of a problem i cannot seem to work out. I have got 3
coloums of data, column A has town names, say towna townb townc etc,
the same town name will be repeated within the column several times or
sometimes only once. *In coloum 3 is various data say data1, data2,
data3, data4 etc. *I need to summarise the information i.e I need to
now how many times data1 was in the same row as towna, townb, townc.
How may times data2 was in towna,townb,townc until I have counted
every different piece of data in column 3 and assigned that number to
every different town. *Hope this makes sense, I have been trying to do
it with a loop but i am getting know where fast. *Any help much
appreaciated

Johny

If this is what you have in columns A and B

ta * * *d1
tb * * *d1
ta * * *d2
ta * * *d2
tb * * *d3
tc * * *d1
ta * * *d2
tc * * *d1

And this is what you want in say columns C to F

* * * * d1 * * *d2 * * *d3
ta * * *1 * * * 3 * * * 0
tb * * *1 * * * 0 * * * 1
tc * * *2 * * * 0 * * * 0

Then you could use the following formula in cell D2 and copy it down
and to the right:

=SUMPRODUCT(($A$1:$A$8=$C2)*($B$1:$B$8=D$1))

This solution assumes that you know all t's and d's in advance.

Hope this helps */ Lars-Åke

I will always know what the d are in advance but not the T's is there
a way i can populate column c with all the different T;s first