Compare two col to same two on next row

[morry]

There are numbers in col B and words in col G.
I need to compare Col B and Col G (together) to the same two columns o
the next row for all rows. I need to count every time there is
repeat but i don't want to count the first occurance just from th
second on. Each time the word in column G changes I need to start
new count. I would like to record the totals for each new word in co
G, in a new cell. For example for the first word I record the tota
number of repeats in cell H1, for the second word I record the numbe
of repeats in H2 and so on. Is this possible. If I have to d
something else with the totals that is ok. I have attached a smal
example of a spread sheet so you can visualize this.


Thank you for your help

Morr

Attachment filename: sample.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=58370

 

[Frank Kabel]

Hi
try
=SUMPRODUCT(--(B1:B99=B2:B100),--(G1:G99=G2:G100))

 

[mudraker]

Morry

I have not looked at your attached sample sheet as I do not open unkow
files.


If I understand your problem correctly this should do what you want


Sub CountDup()
Dim iCnt As Integer
Dim lRow As Long
Dim sWord As String

For lRow = 1 To Cells.Find(what:="*", _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row Step 1

If Cells(lRow, "g").Value = sWord Then
If Cells(lRow, "b").Value = _
Cells(lRow + 1, "b").Value Then
iCnt = iCnt + 1
End If

Else
Cells(Range("h" & Rows.Count).End(xlUp).Row + 1, _
"h").Value = sWord & " " & iCnt - 1

iCnt = 0
sWord$ = Cells(lRow, "g")
End If
Nex

 

[morry]

Thank you Frank for the feedback but that wasn't exactly what I wa
looking for but thanks anyway.

Mudraker, what you gave me is really close but it still isn't exactl
what I need. I tried to modify your code to make it work but I stil
could not return what I need exactly.

I understand why you don't want to open unknown files, I don't either.
I will list below a small sample of my spreadsheet.

Col B Col G

826 TaskA
826 TaskA 1
867 TaskA
895 TaskA
967 TaskA
967 TaskA 1
969 TaskA
969 TaskA 1
1010 TaskA
1030 TaskA
1031 TaskA
1049 TaskC
1049 TaskB
1050 TaskB
1052 TaskB
1052 TaskB 2
1052 TaskB 2
1059 TaskB
1062 TaskC
1062 TaskB
1062 TaskB 2
1062 TaskA
1063 TaskC
1063 TaskC 3
1063 TaskB
1063 TaskB 2

Here is an example
Task A = 3
Task B = 4
Task C = 1

We don't count the first one but if the next one matches then we coun
it and then it gives one total for each individual task.

Can you help me figure this out?

Thank you very much

Morr

 

[mudraker]

Morry

Hopefully I understand your problem correctly

This version puts the text of column G into column H

If I have done it correctly it would then only require counting eac
time each item is listed.

I did not want to add any more code in case I still have not this firs
part right


Sub CountDup2()
Dim lRow As Long

For lRow = 1 To Cells.Find(what:="*", _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row Step 1

If Cells(lRow, "b").Value = _
Cells(lRow + 1, "b").Value Then
If Cells(lRow, "g").Value = _
Cells(lRow + 1, "g").Value Then
Range("h" & lRow + 1).Value = Cells(lRow, "g").Value
End If
End If
Next
End Su