Counting data

[john.9.williams]

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

 

[Lars-Åke Aspelin]

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

 

[john.9.williams]

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