Unique Counting With Multiple Criteria

[Morton Detwyler]

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.....

 

[Sheeloo]

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

it will give 0 for blank rows...

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.....

 

[Bernard Liengme]

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

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.....

 

[Bernd P]

Hello,

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

Regards,
Bernd

 

[Morton Detwyler]

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

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

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.....

 

[Morton Detwyler]

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

 

[Morton Detwyler]

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!

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

it will give 0 for blank rows...

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.....

 

[Michael.Tarnowski]

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!

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

it will give 0 for blank rows...

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

 

[Bernd P]

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 here:
http://www.sulprobil.com/html/pfreq.html

Regards,
Bernd