Count number of like occurences across all worksheet within workbo

[Versace77]

Hello,

I'm in search of a way to count how many times a certain line of data
appears across many worksheets to summarize on a new worksheet within the
same workbook.

Example. My workbook may contain 60 worksheets from July 15- Sept 15, 1 for
each day. Each sheet has many lines of data spanning from Row 1; Column A-T,
Row 2; Column A-T and so forth.

There are times where the same row of data will appear for 'x' number of
days in a row. I'd like to get an analysis across all 60 sheets counting
the number of times the same row of data appears across these 60 worksheets.

If this reads confusing, let me know and I'll try to clarify better.

Thank you in advance.

Mike

 

[Héctor Miguel]

hi, Mike !

columns A-T, rows 1, 2 and so forth, for ~60 worksheets are too many rows/columns for data comparisson
also, it's not clear if you need to compare the data in whole columns for n_rows within ~60 worksheets -?-

is there any chance that in a few columns (the less possible) to "build" a unique set/chain of data to compare ?

regards,
hector.

__ OP __

 

[GTVT06]

Hello Mike,
You can try to use this code that I put together for you. I did it on
the fly, so it's not the cleanest code, but it does the trick! right
now I have it debug.print the sheet name and values of the duplicate
entries in the immediate window. Let me know if you have questions.

Sub test()
Dim cell, cell2 As Range
Dim Dupe, Dupe2 As String
Dim LRow, LRow2, i, i2 As Long
Dim n As Single
For n = 1 To Sheets.Count
Worksheets(n).Activate

LRow = Worksheets(n).Range("A65536").End(xlUp).Row
For Each cell In Worksheets(n).Range("A1:A" & LRow)
i = cell.Row
Dupe = Worksheets(n).Range("A" & i).Value &
Worksheets(n).Range("B" & i).Value & Worksheets(n).Range("C" &
i).Value _
& Worksheets(n).Range("D" & i).Value & Worksheets(n).Range("E" &
i).Value & Worksheets(n).Range("F" & i).Value _
& Worksheets(n).Range("G" & i).Value & Worksheets(n).Range("H" &
i).Value & Worksheets(n).Range("I" & i).Value _
& Worksheets(n).Range("J" & i).Value & Worksheets(n).Range("K"
& i).Value & Worksheets(n).Range("L" & i).Value _
& Worksheets(n).Range("M" & i).Value & Worksheets(n).Range("N"
& i).Value & Worksheets(n).Range("O" & i).Value _
& Worksheets(n).Range("P" & i).Value &
Worksheets(n).Range("Q" & i).Value & Worksheets(n).Range("R" &
i).Value _
& Worksheets(n).Range("S" & i).Value &
Worksheets(n).Range("T" & i).Value
Dim n2 As Single
For n2 = 1 To Sheets.Count
If Worksheets(n2).Name = Worksheets(n).Name Then GoTo nx
LRow2 = Worksheets(n2).Range("A65536").End(xlUp).Row
For Each cell2 In Worksheets(n2).Range("A1:A" & LRow2)
i2 = cell2.Row
Dupe2 = Worksheets(n2).Range("A" & i2).Value &
Worksheets(n2).Range("B" & i2).Value & Worksheets(n2).Range("C" &
i2).Value _
& Worksheets(n2).Range("D" & i2).Value & Worksheets(n2).Range("E"
& i2).Value & Worksheets(n2).Range("F" & i2).Value _
& Worksheets(n2).Range("G" & i2).Value &
Worksheets(n2).Range("H" & i2).Value & Worksheets(n2).Range("I" &
i2).Value _
& Worksheets(n2).Range("J" & i2).Value &
Worksheets(n2).Range("K" & i2).Value & Worksheets(n2).Range("L" &
i2).Value _
& Worksheets(n2).Range("M" & i2).Value &
Worksheets(n2).Range("N" & i2).Value & Worksheets(n2).Range("O" &
i2).Value _
& Worksheets(n2).Range("P" & i2).Value &
Worksheets(n2).Range("Q" & i2).Value & Worksheets(n2).Range("R" &
i2).Value _
& Worksheets(n2).Range("S" & i2).Value &
Worksheets(n2).Range("T" & i2).Value

If Dupe <> "" And Dupe = Dupe2 Then Debug.Print Worksheets(n).Name & "
- " & Dupe & " & " & Worksheets(n2).Name & " - " & Dupe2

Next cell2
nx:
Next n2
Next cell
Next n
End Sub

 

[Versace77]

Hector, thanks for the reply, i think GVT is on to something, i have a few
more q's to ask him.

 

[Versace77]

GVT,

Thanks for the effort you are making on this. I do have some questions and
clarifications.

1: I noticed in your programming you LRow and LRow2. Do i need an LRow for
every row in the worksheets? Each worksheet has a different number of rows.

2: Can you further elaborate/explain Debug.Print Worksheets(n).Name & "
- " & Dupe & " & " & Worksheets(n2).Name & " - " & Dupe2 for me? I'm not
very familiiar and and receiving run time error 13 or 438 when i try to
manipulate.

Once i see what this will produce i will be better able to see if it meets
what i'm looking for.

Thank you.

 

[Versace77]

GTVT06,

Good evening.

Thought this bit of specific info would be helpful.

Once the macro analyzes the data across all 60 worksheets and rows i'd like
the summary sheet of data to display either each line (row) with a number at
the end or beginning of the row showing how many times that 1 row appeared in
the 60 worksheets.

Hope that helps.

Thanks again, GTV.

 

[GTVT06]

Hello Versace,
See answers below:

1: I noticed in your programming you LRow and LRow2.  Do i need an LRowfor
every row in the worksheets?  Each worksheet has a different number of rows.

Nope. LRow and LRow2 is actually figuring out what the LastRow is on
each sheet since the last row will vary from sheet to sheet.

2: Can you further elaborate/explain Debug.Print Worksheets(n).Name & "
- " & Dupe & " & " & Worksheets(n2).Name & " - " & Dupe2 for me?

That is concatenating the worksheet name and duplicate values of 1
sheet with "&" and the sheet name and duplicate value of the second
sheet.
The result would look like this:
"Sheet1 - 123 & Sheet3 - 123"
if you would like to count duplicate entries rather than show them, I
can edit the code, however there will be entries counted more than
once since the code if selecting each sheet and then searching all of
the other sheets for duplicates, I can edit the code to cut back on
double entries but it would take quite a bit of more code to elimate
reporting duplicates twice.

 

[GTVT06]

GTVT06,

Good evening.

Thought this bit of specific info would be helpful.

Once the macro analyzes the data across all 60 worksheets and rows i'd like
the summary sheet of data to display either each line (row) with a numberat
the end or beginning of the row showing how many times that 1 row appeared in
the 60 worksheets.

Hope that helps.

Thanks again, GTV.









- Show quoted text -

Oh Cool!!! This will make doing away with duplicate entries easier! I
didn't know there was a summary sheet you wanted to report this on. I
can revise my code to do this for you. I probably wont get a chance to
actually work on it until a little later tonight though.

 

[Versace77]

Sweet deal GTV! Yes, I apologize for not making that more clearer earlier.
A summary sheet is ideal so i can look at that one sheet for all my answers.
No worries on the hastiness, i'm patient. Thanks!

 

[GTVT06]

Ok, heres the revised code. Inside the commented box change "Summary"
to whatever the actual summary sheet name is, change "U" to the letter
of the column that you would like to use to show the number of
instances, and change "2" to the number of the row in which the data
starts in.(i.e. I entered 2 assuming there was one row of headers and
data begins in row 2). Let me know if this works for you or if you
have any questions.

Sub Test()
Dim cell, cell2 As Range
Dim Dupe, Dupe2 As String
Dim LRow, LRow2, i, i2, DRow As Long
Dim n, DCol As String
'===================================
n = "Summary" 'Name Of Summary Sheet
DCol = "U" 'Column to show count
DRow = 2 'Row to begin count
'===================================
Worksheets(n).Activate

LRow = Worksheets(n).Range("A65536").End(xlUp).Row
Worksheets(n).Range(DCol & DRow & ":" & DCol & LRow).ClearContents
For Each cell In Worksheets(n).Range("A" & DRow & ":A" & LRow)
i = cell.Row
Dupe = Worksheets(n).Range("A" & i).Value &
Worksheets(n).Range("B" & i).Value & Worksheets(n).Range("C" &
i).Value _
& Worksheets(n).Range("D" & i).Value & Worksheets(n).Range("E" &
i).Value & Worksheets(n).Range("F" & i).Value _
& Worksheets(n).Range("G" & i).Value & Worksheets(n).Range("H" &
i).Value & Worksheets(n).Range("I" & i).Value _
& Worksheets(n).Range("J" & i).Value & Worksheets(n).Range("K"
& i).Value & Worksheets(n).Range("L" & i).Value _
& Worksheets(n).Range("M" & i).Value & Worksheets(n).Range("N"
& i).Value & Worksheets(n).Range("O" & i).Value _
& Worksheets(n).Range("P" & i).Value &
Worksheets(n).Range("Q" & i).Value & Worksheets(n).Range("R" &
i).Value _
& Worksheets(n).Range("S" & i).Value &
Worksheets(n).Range("T" & i).Value
Dim n2 As Single
For n2 = 1 To Sheets.Count
If Worksheets(n2).Name = Worksheets(n).Name Then GoTo nx
LRow2 = Worksheets(n2).Range("A65536").End(xlUp).Row
For Each cell2 In Worksheets(n2).Range("A1:A" & LRow2)
i2 = cell2.Row
Dupe2 = Worksheets(n2).Range("A" & i2).Value &
Worksheets(n2).Range("B" & i2).Value & Worksheets(n2).Range("C" &
i2).Value _
& Worksheets(n2).Range("D" & i2).Value & Worksheets(n2).Range("E"
& i2).Value & Worksheets(n2).Range("F" & i2).Value _
& Worksheets(n2).Range("G" & i2).Value &
Worksheets(n2).Range("H" & i2).Value & Worksheets(n2).Range("I" &
i2).Value _
& Worksheets(n2).Range("J" & i2).Value &
Worksheets(n2).Range("K" & i2).Value & Worksheets(n2).Range("L" &
i2).Value _
& Worksheets(n2).Range("M" & i2).Value &
Worksheets(n2).Range("N" & i2).Value & Worksheets(n2).Range("O" &
i2).Value _
& Worksheets(n2).Range("P" & i2).Value &
Worksheets(n2).Range("Q" & i2).Value & Worksheets(n2).Range("R" &
i2).Value _
& Worksheets(n2).Range("S" & i2).Value &
Worksheets(n2).Range("T" & i2).Value

If Dupe <> "" And Dupe = Dupe2 Then Worksheets(n).Range(DCol &
i).Value = Worksheets(n).Range(DCol & i).Value + 1
Next cell2
nx:
Next n2
Next cell
End Sub

 

[Versace77]

Thanks for the updated code. When attempting to run the macro here's what
happens: Macro makes the summary sheet active, hourglass appears for 10 or
so seconds and then nothing. Just a blank sheet.

I'll forward you the .xls that i'm using so you can get an idea or maybe see
something that is missing.

Thanks again GTV.

Mike

 

[Versace77]

GTVT06,

Sorry, I forgot to mention where it is hanging up. It is hanging up near
the end "If Dupe <> "" And Dupe = Dupe2 Then..."

Thanks!

 

[Versace77]

To make it simpler i can dump all the data into one sheet and work with that.
Let me know if that will make the code simpler. Thanks.