need a lot of help doing a macro for excel

[otispunkmeyer]

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.

 

[otispunkmeyer]

i should point out that all the data is also availabe as an Access database if that helps

 

[KLin]

Originally posted by: otispunkmeyer
i should point out that all the data is also availabe as an Access database if that helps

This is so much easier in Access. Just do the following:

From query designer
1. Create new query
2. add the table in question
3. include the fields
4. Click the totals button(the Greek E)
5. Include the pass field, but change the total from group by to Count

Then you can copy/paste the results into excel if you want.

 

[Ken g6]

I'm really not sure how SUMPRODUCT solved your problem, but if your only problem in Excel is reoccurrances:

1. Take the list and put it in another sheet. Use Paste Special: Values if necessary.
2. Sort the list.
3. Add a formula =if(and($A1=$A2,$B1=$B2),"",A2) in a column to the right, e.g. cell D2. Drag it to the right, and then down, so that it mirrors the left columns.
4. Copy the new columns, and use Paste Special: Values to paste them in a new sheet.
5. Sort that sheet to eliminate blank spaces.

 

[PowerEngineer]

I'm not sure that you want to jump into macros for something like this. There are a lot of tricks you can do in the spreadsheet itself that might get you what you want.

One approach I like to use for similar situations is to build a new "flag" column that combines the entries in the two columns I'm interested in. I'll lay this out as best I can and I apologize in advance for any typos you may need to fix before the formulas work. This does add a lot of calculating overhead to the spreadsheet, but should be okay as long as the number of rows is more than 5000(?).

In your case, I gather you are looking for unique combinations of "Plate ID" and "Piece Number". Let's say these are columns A and B (and "Pass" is column C). Let's also assume that the first row of data in in row 3. In D3, put the formula:

=IF(len(A3)>0,A3&"$"$B3,"")

Copy that formula down through as many rows as you will ever have data (i.e. A,B,C entries in). If there's no data in column A, then the values in column D will be blank anyway. What this does is give you one column in which you can search for those unique combintaions. Now, in E3, put the formula:

=IF(len(A3)>0,IF(ISNA(MATCH(D3,D$2😀2,0)),D3,""),"")

Again, copy that formula down through the same number of rows as in column C. Again, there'll be blank values in E if there's no data. What this formula also does is look to see if the "flag" value in this row has already occurred in rows above it. If it hasn't, it repeats that "flag" value in column E; if it has, it leaves column E blank. This means you have a list of the unique "flags" in column E. To compress this down into a list without blanks thats more fiddling. In F3, put the formula:

=IF(len(D3)>0,MAX(E$2:E2)+1,"")

Again, copy the formula down... This numbers all the unique combinations. In H1, put the formula:

=MAX(F:F)

This puts the number of unique combinations in a predictable location. In G3, put the formula:

=1

and in G4:

=G3+1

Copy this formula (in G4) down for as many rows as you think will ever be unique combinations. The safe guess, of course, is to copy it down as far as all the other columns. Now, in H3 put the formula:

=IF(G3<=$H$1,INDEX(D😀,MATCH(G3,G:G,0),1),"")

Copy this formula down as far as you went in column G. This should give you the compressed list of "flag" values. I'm going to assume at this point that your "Plate ID" is always 5 characters long (if that's not right, then parsing the "flag" value requires a little more work but can be done). In I3, put the formula:

=IF(len(H3)>0,LEFT(H3,5),"")

Copy down... In J3:

=IF(len(H3)>0,MID(H3,7,10),"")

Copy down...

Finally, to add the number of passes, in K3:

=if(len(H3)>0,COUNTIF(D😀,H3),"")

Copy down...

You can "hide" columns D though H.

To work though a new list of data, clear columns A through C and copy/paste the new data in.

Good luck!



EDIT: LOL... The "smiley faces" are supposed to be a colon followed by a capital D, but of course that's the "smiley face" character string!

 

[Evadman]

Originally posted by: KLin

Originally posted by: otispunkmeyer
i should point out that all the data is also availabe as an Access database if that helps

This is so much easier in Access. Just do the following:

From query designer
1. Create new query
2. add the table in question
3. include the fields
4. Click the totals button(the Greek E)
5. Include the pass field, but change the total from group by to Count

Then you can copy/paste the results into excel if you want.

Listen to Klin. You are using Excel as a database, instead what Access was really designed for. Granted, this will function in excel, but it really should be in Access. Keep it allt here, and work with it there as Klin suggests.

the process Klin puts forth is 100% correct, and will run much faster in Access than Excel could ever hope for.