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), (E-Mail Removed)
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 <lar...@REMOOOVE.telia.com>
wrote:
> On Thu, 27 Mar 2008 03:13:14 -0700 (PDT), john.9.willi...@bt.com
> 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