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

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

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

Hello,

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

Regards,
Bernd

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

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

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

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

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

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