Macro to Find a specific data set and do a count

[kkirank.kmp]

Hi,
Here is my problem - I have 5 workbooks in a folder, each with 11
columns. Could anyone please help me with this - I want to take value
from C1, G1 & I1 and compare it with C2, G2 & I2 and down. If it finds
a match then I need to increment a counter. Once it goes through the
whole list, I need to do the same with the next not same row. I hope
the last part made sense. For example
Row C Row G Row I
1234 01 01
1234 01 01
1224 01 02
1244 01 03
1224 10 01
1234 01 01
1224 01 02

So it starts with first row goes through the whole list writes the
result as shown below, then looks at the 2nd row and since it is the
same as the first one, it should skip that row and go to the next one
(i.e., row 3) and start counting down
Result set should be

Row A Row B Row C Count
1234 01 01 3
1224 01 02 2
1244 01 03 1
1224 10 01 1

Need to write this result to the worksheet "data_from_files" in the
workbook "Combined". Need to do the same from all the 5 workbooks.
Each of the 5 workbook has only 1 sheet, but will have more than 30000
rows.

Any help on this would be greatly appreciated. Thanks in advance.

 

[merjet]

Counts for each workbook separately or across all 5 workbooks?

If the former, info easily obtained by using Data | Filter | Advanced
Filter |
Copy to Another Location | Unique records only. Be sure to include
headers in the data-to-filter range. Suppose output is to columns M-O.
Then put in column P next to first row of output the formula:
=SUMPRODUCT(--(G$2:G$8=M2),--(H$2:H$8=N2),--(I$2:I$8=O2))
Then copy this formula to rows below it.

Hth,
Merjet

 

[kkirank.kmp]

Hi Merjet..thanks for the information, but my problem is I need to do
it via a macro and it should look into columns C, G & I and exclude
the other columns and I need to do it across all the spreadsheets.
Also I cannot run Advanced filter on specific columns. These 5
workbooks are generated by a different macro.

Thanks

 

[merjet]

Sub Macro1()
Dim ws As Worksheet
Dim wb2 As Workbook
Dim ws2 As Worksheet
Dim iEnd As Integer
Dim iEnd2 As Integer
Dim str1 As String

'copy cols C, G & I from 5 wkbks to this one
Set ws = ThisWorkbook.Sheets("data_from_files")
For iCt = 1 To 5
Set wb2 = Workbooks.Open("c:\Temp\Book" & iCt & ".xls")
Set ws2 = wb2.Sheets("Sheet1")
iEnd2 = ws2.Range("C1").End(xlDown).Row
iEnd = 1 + ws.Range("A65536").End(xlUp).Row
ws2.Range("C2:C" & iEnd2).Copy ws.Range("A" & iEnd)
ws2.Range("G2:G" & iEnd2).Copy ws.Range("B" & iEnd)
ws2.Range("I2:I" & iEnd2).Copy ws.Range("C" & iEnd)
wb2.Close
Next iCt

'do advanced filter on copied data
iEnd = ws.Range("A65536").End(xlUp).Row
ws.Range("A1:C" & iEnd).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range("E1"), Unique:=True
iEnd2 = ws.Range("E65536").End(xlUp).Row

'formula to count occurrences
str1 = "--(R2C[-7]:R" & iEnd & "C[-7]=RC[-3])," & _
"--(R2C[-6]:R" & iEnd & "C[-6]=RC[-2])," & _
"--(R2C[-5]:R" & iEnd & "C[-5]=RC[-1]))"
ws.Range("H2").FormulaR1C1 = "=SUMPRODUCT(" & str1
ws.Range("H2").Copy ws.Range("H3:H" & iEnd2)
End Sub

Hth,
Merjet