Creating a table

[murfitUK]

There's probably an easier way to do it but...

I have a series of numbers in column A (150 in all) and a series of letters
in column B (22 in all). In total there are 4800 rows.

What I am trying to do is create a table with the number down the left hand
side and the letters across the top. In each cell within the table I need
to count how many times the combination of number/letter appears. Eg:

A B C
201 0 2 2
202 3 0 1
203 3 6 4

I tried combining the number/letter into a single text field using the
CONCATENATE function then filtering and writing down the number but there
are just too many combinations!

I have managed to use the COUNTIF function for the very first entry (201/A)
but when I drag to extend the formula it changes the columns.

I have posted a screen to show what I mean if anyone would care to look:

http://homepage.ntlworld.com/murfituk/pics/example.JPG

As you can see there are 3 items with the "201" number and "A" letter.

When I extend the formula to the B, C, D & E cells it changes the column
from C3:C32 to D332, E3:E32 etc which isn't what I want.

Like I said, there is probably an easier way to do it but I am not an expert
with Excel. Any comments/suggestions gladly received.

Thanks for your time.

 

[Roger Govier]

Hi

One way, assuming your data is on Sheet1, and you set up your results table
on Sheet2 in A14, enter the following formula into cell B2 of Sheet2 and
copy across through cells C22, then copy B22 down through B3:B4

=SUMPRODUCT(--(Sheet1!$A$1+$A$4800=$A2),--(Sheet1!B$!:$B$4800=B$1))

Regards

Roger Govier

 

[Mladen_Dj]

Putting absolute reference in your formula could solve your problem. Instead
"C3:C32", use "$C$3:$C$32", and when you copy formula to another column,
reference $C$3:$C$32 will not change.

 

[murfitUK]

Thanks everyone. I have discovered Pivot Tables and found that it did
exactly what I wanted. An example here:

http://homepage.ntlworld.com/murfituk/pics/example2.JPG

The idea of absolute references will come in handy for something else I need
to do so thanks for replying.