Unique Counting With Multiple Criteria
 

I have the following repair records with headers in row 1, and data beginning
in row 2.
All data is contiguous down the columns with no spaces. I inserted the
lines between the different serial numbers for reading clarity.

[col A] [col B] [col C] [col D]
Start Date Ticket Number Serial Number Repeat Repair Count
12/29/2007 123 ABC111 0

08/28/2008 234 BCD222 0
08/28/2008 234 BCD222 0

12/29/2007 345 CDE333 0
08/26/2008 456 CDE333 1
12/17/2008 567 CDE333 2

11/12/2008 678 DEF444 0

06/14/2008 789 EFG555 0
12/16/2008 890 EFG555 1


I need to produce a report of repeat repair activity after the initial
repair. After sorting the data by serial number, by ticket number, by start
date, I have to look at the serial number it matches, then check the tcket
number to see if it doesn't match.

Using the data above, my report would read as follows:

Three serial numbers ABC111, BCD222, DEF444 had no repeat repair activity
One serial number EFG555, had 1 repeat repair
One serial number CDE333 had 2 repeat repairs

I'm stumped....can anyone help with a way to fill in the counts in col D?

Thank you.....

Unique Counting With Multiple Criteria
 

Enter this in D2 and copy down...
=SUMPRODUCT(--($B$2:B2<B2),--($C$2:C2=C2))

it will give 0 for blank rows...

"Morton Detwyler" wrote:

I have the following repair records with headers in row 1, and data beginning
in row 2.
All data is contiguous down the columns with no spaces. I inserted the
lines between the different serial numbers for reading clarity.

[col A] [col B] [col C] [col D]
Start Date Ticket Number Serial Number Repeat Repair Count
12/29/2007 123 ABC111 0

08/28/2008 234 BCD222 0
08/28/2008 234 BCD222 0

12/29/2007 345 CDE333 0
08/26/2008 456 CDE333 1
12/17/2008 567 CDE333 2

11/12/2008 678 DEF444 0

06/14/2008 789 EFG555 0
12/16/2008 890 EFG555 1


I need to produce a report of repeat repair activity after the initial
repair. After sorting the data by serial number, by ticket number, by start
date, I have to look at the serial number it matches, then check the tcket
number to see if it doesn't match.

Using the data above, my report would read as follows:

Three serial numbers ABC111, BCD222, DEF444 had no repeat repair activity
One serial number EFG555, had 1 repeat repair
One serial number CDE333 had 2 repeat repairs

I'm stumped....can anyone help with a way to fill in the counts in col D?

Thank you.....

Unique Counting With Multiple Criteria
 

This subroutine does what you want

Option Explicit
Sub repairs()
Dim myanswer, oldticket, oldserial, j, myrepairs, mylast
myanswer = MsgBox("please confirm data is sorted", vbYesNo)
If myanswer = vbNo Then Exit Sub
mylast = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Cells(2, "D") = 0
oldticket = Cells(2, "B")
oldserial = Cells(2, "C")
For j = 3 To mylast
If Cells(j, "B") = oldticket And Cells(j, "C") = oldserial Then
myrepairs = 0
Cells(j, "D") = myrepairs
ElseIf Cells(j, "B") < oldticket And Cells(j, "C") = oldserial Then
myrepairs = myrepairs + 1
Cells(j, "D") = myrepairs
oldserial = Cells(j, "C")
Else
myrepairs = 0
Cells(j, "D") = myrepairs
oldticket = Cells(j, "B")
oldserial = Cells(j, "C")
End If
Next j
End Sub

New to VBA: See David McRitchie's site on "getting started" with VBA

http://www.mvps.org/dmcritchie/excel/getstarted.htm

Debra Dalgleish's "Adding Code to a Workbook"

http://www.contextures.com:80/xlvba01.html

best wishes

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Morton Detwyler" wrote in
message ...
I have the following repair records with headers in row 1, and data
beginning
in row 2.
All data is contiguous down the columns with no spaces. I inserted the
lines between the different serial numbers for reading clarity.

[col A] [col B] [col C] [col D]
Start Date Ticket Number Serial Number Repeat Repair Count
12/29/2007 123 ABC111 0

08/28/2008 234 BCD222 0
08/28/2008 234 BCD222 0

12/29/2007 345 CDE333 0
08/26/2008 456 CDE333 1
12/17/2008 567 CDE333 2

11/12/2008 678 DEF444 0

06/14/2008 789 EFG555 0
12/16/2008 890 EFG555 1


I need to produce a report of repeat repair activity after the initial
repair. After sorting the data by serial number, by ticket number, by
start
date, I have to look at the serial number it matches, then check the tcket
number to see if it doesn't match.

Using the data above, my report would read as follows:

Three serial numbers ABC111, BCD222, DEF444 had no repeat repair activity
One serial number EFG555, had 1 repeat repair
One serial number CDE333 had 2 repeat repairs

I'm stumped....can anyone help with a way to fill in the counts in col D?

Thank you.....

Unique Counting With Multiple Criteria
 

Hello,

I suggest to enter into
D2:
=IF(C2<C1,0,(B2<B1)+D1)
and copy down.

Regards,
Bernd

Unique Counting With Multiple Criteria
 

This works perfectly....thank you so much!

"Sheeloo" wrote:

Enter this in D2 and copy down...
=SUMPRODUCT(--($B$2:B2<B2),--($C$2:C2=C2))

it will give 0 for blank rows...

"Morton Detwyler" wrote:

I have the following repair records with headers in row 1, and data beginning
in row 2.
All data is contiguous down the columns with no spaces. I inserted the
lines between the different serial numbers for reading clarity.

[col A] [col B] [col C] [col D]
Start Date Ticket Number Serial Number Repeat Repair Count
12/29/2007 123 ABC111 0

08/28/2008 234 BCD222 0
08/28/2008 234 BCD222 0

12/29/2007 345 CDE333 0
08/26/2008 456 CDE333 1
12/17/2008 567 CDE333 2

11/12/2008 678 DEF444 0

06/14/2008 789 EFG555 0
12/16/2008 890 EFG555 1


I need to produce a report of repeat repair activity after the initial
repair. After sorting the data by serial number, by ticket number, by start
date, I have to look at the serial number it matches, then check the tcket
number to see if it doesn't match.

Using the data above, my report would read as follows:

Three serial numbers ABC111, BCD222, DEF444 had no repeat repair activity
One serial number EFG555, had 1 repeat repair
One serial number CDE333 had 2 repeat repairs

I'm stumped....can anyone help with a way to fill in the counts in col D?

Thank you.....

Unique Counting With Multiple Criteria
 

Very nice work...I could have never done this.....thank you so much!

"Bernard Liengme" wrote:

This subroutine does what you want

Option Explicit
Sub repairs()
Dim myanswer, oldticket, oldserial, j, myrepairs, mylast
myanswer = MsgBox("please confirm data is sorted", vbYesNo)
If myanswer = vbNo Then Exit Sub
mylast = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Cells(2, "D") = 0
oldticket = Cells(2, "B")
oldserial = Cells(2, "C")
For j = 3 To mylast
If Cells(j, "B") = oldticket And Cells(j, "C") = oldserial Then
myrepairs = 0
Cells(j, "D") = myrepairs
ElseIf Cells(j, "B") < oldticket And Cells(j, "C") = oldserial Then
myrepairs = myrepairs + 1
Cells(j, "D") = myrepairs
oldserial = Cells(j, "C")
Else
myrepairs = 0
Cells(j, "D") = myrepairs
oldticket = Cells(j, "B")
oldserial = Cells(j, "C")
End If
Next j
End Sub

New to VBA: See David McRitchie's site on "getting started" with VBA

http://www.mvps.org/dmcritchie/excel/getstarted.htm

Debra Dalgleish's "Adding Code to a Workbook"

http://www.contextures.com:80/xlvba01.html

best wishes

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Morton Detwyler" wrote in
message ...
I have the following repair records with headers in row 1, and data
beginning
in row 2.
All data is contiguous down the columns with no spaces. I inserted the
lines between the different serial numbers for reading clarity.

[col A] [col B] [col C] [col D]
Start Date Ticket Number Serial Number Repeat Repair Count
12/29/2007 123 ABC111 0

08/28/2008 234 BCD222 0
08/28/2008 234 BCD222 0

12/29/2007 345 CDE333 0
08/26/2008 456 CDE333 1
12/17/2008 567 CDE333 2

11/12/2008 678 DEF444 0

06/14/2008 789 EFG555 0
12/16/2008 890 EFG555 1


I need to produce a report of repeat repair activity after the initial
repair. After sorting the data by serial number, by ticket number, by
start
date, I have to look at the serial number it matches, then check the tcket
number to see if it doesn't match.

Using the data above, my report would read as follows:

Three serial numbers ABC111, BCD222, DEF444 had no repeat repair activity
One serial number EFG555, had 1 repeat repair
One serial number CDE333 had 2 repeat repairs

I'm stumped....can anyone help with a way to fill in the counts in col D?

Thank you.....

Unique Counting With Multiple Criteria
 

Exactly what I was looking for...thank you so much!

"Bernd P" wrote:

Hello,

I suggest to enter into
D2:
=IF(C2<C1,0,(B2<B1)+D1)
and copy down.

Regards,
Bernd

Unique Counting With Multiple Criteria
 

Hi Sheeloo,
Your formula worked perfectly, but I was curious as to the purpose of the
double minus signs (--)? Thanks much for your time and assistance!

"Sheeloo" wrote:

Enter this in D2 and copy down...
=SUMPRODUCT(--($B$2:B2<B2),--($C$2:C2=C2))

it will give 0 for blank rows...

"Morton Detwyler" wrote:

I have the following repair records with headers in row 1, and data beginning
in row 2.
All data is contiguous down the columns with no spaces. I inserted the
lines between the different serial numbers for reading clarity.

[col A] [col B] [col C] [col D]
Start Date Ticket Number Serial Number Repeat Repair Count
12/29/2007 123 ABC111 0

08/28/2008 234 BCD222 0
08/28/2008 234 BCD222 0

12/29/2007 345 CDE333 0
08/26/2008 456 CDE333 1
12/17/2008 567 CDE333 2

11/12/2008 678 DEF444 0

06/14/2008 789 EFG555 0
12/16/2008 890 EFG555 1


I need to produce a report of repeat repair activity after the initial
repair. After sorting the data by serial number, by ticket number, by start
date, I have to look at the serial number it matches, then check the tcket
number to see if it doesn't match.

Using the data above, my report would read as follows:

Three serial numbers ABC111, BCD222, DEF444 had no repeat repair activity
One serial number EFG555, had 1 repeat repair
One serial number CDE333 had 2 repeat repairs

I'm stumped....can anyone help with a way to fill in the counts in col D?

Thank you.....

Unique Counting With Multiple Criteria
 

On Mar 15, 12:21 am, Morton Detwyler
wrote:
Hi Sheeloo,
Your formula worked perfectly, but I was curious as to the purpose of the
double minus signs (--)? Thanks much for your time and assistance!

"Sheeloo" wrote:
Enter this in D2 and copy down...
=SUMPRODUCT(--($B$2:B2<B2),--($C$2:C2=C2))

it will give 0 for blank rows...

"Morton Detwyler" wrote:

I have the following repair records with headers in row 1, and data beginning
in row 2.
All data is contiguous down the columns with no spaces. I inserted the
lines between the different serial numbers for reading clarity.

[col A] [col B] [col C] [col D]
Start Date Ticket Number Serial Number Repeat Repair Count
12/29/2007 123 ABC111 0

08/28/2008 234 BCD222 0
08/28/2008 234 BCD222 0

12/29/2007 345 CDE333 0
08/26/2008 456 CDE333 1
12/17/2008 567 CDE333 2

11/12/2008 678 DEF444 0

06/14/2008 789 EFG555 0
12/16/2008 890 EFG555 1

I need to produce a report of repeat repair activity after the initial
repair. After sorting the data by serial number, by ticket number, by start
date, I have to look at the serial number it matches, then check the tcket
number to see if it doesn't match.

Using the data above, my report would read as follows:

Three serial numbers ABC111, BCD222, DEF444 had no repeat repair activity
One serial number EFG555, had 1 repeat repair
One serial number CDE333 had 2 repeat repairs

I'm stumped....can anyone help with a way to fill in the counts in col D?

Thank you.....

Hi Morton,
the double minus is used to change logical True, False into 1 and 0.
Multiplying with 1 (*1) or adding 0 (+0) are sometimes used as well,
but mostly used double minus - some experts say, this is faster.
Cheers Michael

Unique Counting With Multiple Criteria
 

Hello again,

If you want a simple report which is "auto-updating", select a
sufficiently long area with 2 columns and array-enter:
=Pfreq(Pfreq(C1:C999,B1:B999))

My UDF Pfreq you will find he
http://www.sulprobil.com/html/pfreq.html

Regards,
Bernd