Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Find a specific data set and do a count
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Find a specific data set and do a count
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Find a specific data set and do a count
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Find a specific data set and do a count
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to find specific data in a cell, then delete that row on down | Excel Programming | |||
HOw do i find and sort out data repeats for specific data set | Excel Discussion (Misc queries) | |||
count specific value with filtered data | Excel Worksheet Functions | |||
How do i create a macro to find a specific data? | Excel Programming | |||
How do I count my data that are between specific values? | Excel Worksheet Functions |