Applying the same filter and operations to multiple sheets

[colormestacy]

Hi there,

New here, but I'm wondering if there's a time saving solution to a data processing issue I've encountered.

I have multiple files with 20+ sheets. All sheets have the same headers; they correspond to measurements from different experiments.

I need to do the following to each:

1) Filter 'Area' column for measurements between 10 and 100
2) Calculate the AVERAGE of all 'Area' column measurements post-filter
3) Calculate the AVERAGE of all 'Intensity' column measurements post-filter
4) Divide the the result from (3) by the result from (2)
5) Create a new sheet where the results from (2), (3), and (4) from each sheet are listed in a single row of three different columns

A challenge is that each sheet has different measurements, so the filter will produce different numbers of rows per sheet.

Any thoughts on how to create a macro to expedite this would be greatly appreciated.

Stace

 

[kevin9999]

Hi Stace & welcome to the the forum!
What you're asking for is very achievable, but rather than have helpers guess as to the layout etc. of your sheets, could you please provide a sample sheet using the XL2BB add in, or better still, share a file via Google Drive, Dropbox or something similar? Also, with regard to "measurements between 10 and 100" - do you mean inclusive of those figures or exclusive, in other words, equal to and greater than 10 or just greater than 10?

 

[Herakles]

The AVERAGEIF and AVERAGEIFS functions should do the job without having to filter the data.

 

[colormestacy]

Hi Stace & welcome to the the forum!
What you're asking for is very achievable, but rather than have helpers guess as to the layout etc. of your sheets, could you please provide a sample sheet using the XL2BB add in, or better still, share a file via Google Drive, Dropbox or something similar? Also, with regard to "measurements between 10 and 100" - do you mean inclusive of those figures or exclusive, in other words, equal to and greater than 10 or just greater than 10?

Thanks, Kevin!

I mean greater than and less than.

Here's the top of the data from one sheet:

20x_tile_dapi-647_555_8_Stitch_PlaqueComp.tif_555on555_master_FILTERED10-100.xlsx
	A	B	C	D	E	F	G
1		Area	Mean	Min	Max	IntDen	RawIntDen
2	1	6.592	6810.83	5410	8129	44895.813	1042057
3	2	9.349	11805.677	5432	20242	110373.55	2561832
4	3	6.936	7229.267	5407	8541	50145.794	1163912
5	4	18.44	8487.28	5442	13301	156504.447	3632556
6	5	3.317	6649.325	5424	7791	22058.838	511998
7	6	11.977	9308.694	5410	14275	111493.083	2587817
8	7	8.057	6348.503	5492	7848	51147.838	1187170
9	8	21.757	16708.095	5401	34919	363523.658	8437588
10	9	18.009	9241.014	5430	20679	166421.829	3862744
11	10	40.585	18494.136	5412	48773	750584.016	17421476
12	11	4.007	7045.344	5450	9326	28229.262	655217
13	12	26.324	7835.638	5418	12210	206267.096	4787575
14	13	94.311	13553.977	5407	44209	1278282.59	29669656
15	14	23.179	7211.34	5405	11041	167152.402	3879701
16	15	6.419	11860.389	5487	25220	76137.669	1767198
17	16	4.265	6194.364	5400	6721	26420.818	613242
18	17	13.011	37430.507	5406	65535	487020.243	11304013
19	18	5.429	6073.468	5402	6870	32970.207	765257
20	19	23.998	9203.551	5414	14436	220864.029	5126378
21	20	5.472	11518.378	5545	20710	63024.5	1462834
22	21	5.256	6554.172	5562	7761	34450.223	799609
23	22	43.127	7295.988	5399	10045	314653.49	7303284
24	23	15.898	8502.412	5426	14053	135170.796	3137390
25	24	28.694	9938.281	5444	17447	285167.387	6618895
26	25	51.184	11788.289	5405	26942	603367.022	14004487
27	26	19.905	7998.139	5398	12745	159200.806	3695140
28	27	20.422	6732.692	5404	9605	137493.274	3191296
29	28	4.179	8540.845	5423	11942	35693.321	828462
30	29	9.909	8363.413	5550	11930	82875.421	1923585
31	30	27.057	11049.924	5415	18675	298973.904	6939352
32	31	4.093	6225.368	5402	8019	25480.211	591410
33	32	12.365	7420.439	5450	9896	91754.181	2129666
34	33	3.145	5697.219	5397	6071	17918.438	415897
35	34	5.773	7207.366	5426	11098	41609.809	965787
36	35	8.358	9864.541	5605	15017	82450.442	1913721
37	36	13.184	7912.399	5419	14934	104314.325	2421194
38	37	11.288	8142.821	5406	12859	91915.874	2133419
39	38	6.204	6579.694	5432	7980	40820.901	947476
reg_20x_tile_dapi-647_555_8_Sti

 

[kevin9999]

Thanks Stace
I'll get onto this later today (local time) and can I assume all sheets have the same columns as you example? Is "Intensity" column F?

 

[colormestacy]

Hi Stace & welcome to the the forum!
What you're asking for is very achievable, but rather than have helpers guess as to the layout etc. of your sheets, could you please provide a sample sheet using the XL2BB add in, or better still, share a file via Google Drive, Dropbox or something similar? Also, with regard to "measurements between 10 and 100" - do you mean inclusive of those figures or exclusive, in other words, equal to and greater than 10 or just greater than 10?

Thanks Stace
I'll get onto this later today (local time) and can I assume all sheets have the same columns as you example? Is "Intensity" column F?

Yes, and it's G. Thanks!

 

[kevin9999]

This might take a few goes to get it just right, but here's a start. Put the code in a Standard module in its own file & save it as a macro-enabled workbook. Test it on a copy of one of your files & see if it does what you expect. The code first asks the user to open the file to be summarised (which I expect will be closed until you run the code). I took the liberty of calling the summary sheet "Summary" and placing it as the first sheet of the file with the data - that can be changed if you wish. Also look at the number format of the values in the summary sheet - can also be changed to whatever you want.
Let me know how it goes.

Option Explicit
Sub Stace()
    'Declare variables
    Dim wb As Workbook, ws As Worksheet
    Dim Filename As String, n As String, i As Long, j As Long
    Dim a As Double, b As Double, c As Double
    
    'Get user to open file
    Filename = Application.GetOpenFilename _
    (filefilter:="Excel files (*.xlsx),*.xlsx", MultiSelect:=False)
    Set wb = Workbooks.Open(Filename)
    
    'Check if the sheet "Summary" already exists
    'If it does - clear it; If it doesn't - create it
    For i = 1 To wb.Worksheets.Count
        If wb.Worksheets(i).Name = "Summary" Then
            j = 1
        End If
    Next i
    If j = 1 Then
        Set ws = wb.Worksheets("Summary")
        ws.Cells.ClearContents
    Else
        wb.Worksheets.Add(before:=wb.Worksheets(1)).Name = "Summary"
        Set ws = wb.Worksheets("Summary")
    End If
    
    'Add headers to Summary Sheet
    With ws.Cells(1).Resize(, 4)
        .Value = Array("Sheet", "Area (avg)", "Intensity (avg)", "Intensity/Area")  '<~~ change as you see fit
        .ColumnWidth = 30
        .Font.Bold = True
    End With
    
    'Loop through each sheet extracting values & placing in Summary sheet
    For i = 2 To wb.Worksheets.Count
        Set ws = wb.Worksheets(i)
        n = ws.Name
        a = WorksheetFunction.AverageIfs(ws.Columns(2), ws.Columns(2), ">10", ws.Columns(2), "<100")
        b = WorksheetFunction.AverageIfs(ws.Columns(7), ws.Columns(2), ">10", ws.Columns(2), "<100")
        c = b / a
        
        With wb.Worksheets(1).Cells(Rows.Count, "A").End(xlUp).Offset(1)
            .Resize(, 4).Value2 = Array(n, a, b, c)
        End With
    Next i
    
    'Tidy up
    With wb.Worksheets(1)
        .Columns("A:D").AutoFit
        .Columns("B:D").NumberFormat = "0.00"   '<~~ change to your desired number format
    End With
    Application.Goto Reference:=wb.Worksheets(1).Range("A1"), scroll:=True
End Sub

 

[kevin9999]

Just added a trap if no file selected:

Option Explicit
Sub Stace_V2()
    'Declare variables
    Dim wb As Workbook, ws As Worksheet
    Dim Filename, n As String, i As Long, j As Long
    Dim a As Double, b As Double, c As Double
    
    'Get user to open file
    Filename = Application.GetOpenFilename _
    (filefilter:="Excel files (*.xlsx),*.xlsx", MultiSelect:=False)
    If Filename = False Then
        MsgBox "No file selected"
        Exit Sub
    End If
    Set wb = Workbooks.Open(Filename)
    
    'Check if the sheet "Summary" already exists
    'If it does - clear it; If it doesn't - create it
    For i = 1 To wb.Worksheets.Count
        If wb.Worksheets(i).Name = "Summary" Then
            j = 1
        End If
    Next i
    If j = 1 Then
        Set ws = wb.Worksheets("Summary")
        ws.Cells.ClearContents
    Else
        wb.Worksheets.Add(before:=wb.Worksheets(1)).Name = "Summary"
        Set ws = wb.Worksheets("Summary")
    End If
    
    'Add headers to Summary Sheet
    With ws.Cells(1).Resize(, 4)
        .Value = Array("Sheet", "Area (avg)", "Intensity (avg)", "Intensity/Area")  '<~~ change as you see fit
        .ColumnWidth = 30
        .Font.Bold = True
    End With
    
    'Loop through each sheet extracting values & placing in Summary sheet
    For i = 2 To wb.Worksheets.Count
        Set ws = wb.Worksheets(i)
        n = ws.Name
        a = WorksheetFunction.AverageIfs(ws.Columns(2), ws.Columns(2), ">10", ws.Columns(2), "<100")
        b = WorksheetFunction.AverageIfs(ws.Columns(7), ws.Columns(2), ">10", ws.Columns(2), "<100")
        c = b / a
        
        With wb.Worksheets(1).Cells(Rows.Count, "A").End(xlUp).Offset(1)
            .Resize(, 4).Value2 = Array(n, a, b, c)
        End With
    Next i
    
    'Tidy up
    With wb.Worksheets(1)
        .Columns("A:D").AutoFit
        .Columns("B:D").NumberFormat = "0.00"   '<~~ change to your desired number format
    End With
    Application.Goto Reference:=wb.Worksheets(1).Range("A1"), scroll:=True
End Sub

 

[colormestacy]

Whew! Thanks, Kev.

I copy-pasted the code onto a Standard module in a new .xlsm file I created in a test directory with a copy of one of the .xlsx files I want to analyze.

I ran it, but it said

Run-time error '1004'

Method 'GetOpenFilename' of object '_Application' failed

 

[kevin9999]

Whew! Thanks, Kev.

I copy-pasted the code onto a Standard module in a new .xlsm file I created in a test directory with a copy of one of the .xlsx files I want to analyze.

I ran it, but it said

Run-time error '1004'

Method 'GetOpenFilename' of object '_Application' failed

When you pressed Debug, which particular line of code was highlighted yellow?