counting data

**otispunkmeyer** · 06-06-2007, 06:18 AM

hi, im new here, i know very little about excel beyond simple features and dont really know how to prgram VB (though i can do it by example)

im not really sure how to explain what i would like to do but here goes.

*i have a huge file containing data relating to levelled steel plate

*there are two columns i am focusing on. plate ID number and Piece number (there can be many pieces all cut from the same plate)

*every time a piece passes through the TAB leveller, it gets recorded. lets say it goes through 5 times. it will look like this

Plate ID Piece No Pass
jw001 1 forward
jw001 1 reverse
jw001 1 forward
jw001 1 reverse
jw001 1 forward

so thats 5 times. Now, there are many different plates, with many different piece numbers all in the same table. sometimes they are mixed up abit (ie not in a nice sequence like this)

*what i want to do, is have a macro that scans the plate ID column, and the Piece number looking for unique combinations. ie

Plate ID Piece No Pass
jw001 1 forward
jw001 1 reverse
jw001 1 forward
jw001 1 reverse
jw001 1 forward
jw001 2 forward
tz445 1 forward
tz445 1 reverse
jg556 4 forward

out of all them there are just 4 unique plates/pieces

basically how do i get the macro to just take each unique ID and piece combination, list them all in a seperate column then have it count the number of occurances for each? e.g. the result would look like this

Plate ID Piece No Number of Passes
jw001 1 5
jw001 2 1
tz445 1 2
jg556 4 1

i hope that makes sense. i have already kind of done this but using

=SUMPRODUCT(--(PlateID range=a plate id),--(Piece no range=a piece no))

i dragged it all the way down to the bottom, then went through and just deleted reocurrences because it would put 5 lots of 5 next to a plate that passed through the machine 5 times.....it took ages and i have 3 more files to do.

**sweep** · 06-06-2007, 06:50 AM

Hi,

You can do this with a pivot table if you don't want to resort to VBA

Please see the attachment

**otispunkmeyer** · 06-06-2007, 07:19 AM

i shall try that thanks!

i looks like it'll be a little too crowded but i bet you can pare it down right?

**sweep** · 06-06-2007, 07:26 AM

You can either format it to get exactly what you want, or draw up another table using the pivot as your source - I'm no expert, but it beats trying to program what you wanted from VBA, and is easier than array formulas and the like.

Enjoy,

Dave