Need array formulas to return names meeting criteria

TinaP

Well-known Member
Joined
Jan 26, 2005
Messages
528
I have the following list:

Excel 2010
ABCDEFG
1MONTHChuckDavidEmmaFredGraceHarry
2Jan123153
3Feb000004
4Mar010002
5Apr000010
6May000000
7Jun001000
8Jul200010
9Aug000000
10Sep000200
11Oct010001
12Nov000000
13Dec000000
Sheet1

I need formulas that will return the names of anyone who has a number >3 in any month and/or a total >=8 in any rolling 3 month period. In this instance, the formulas would return:
Emma (>3)
Grace (>3)
Harry (>=8)

I'd like to avoid VBA, if possible. I figure this could be done with an array formula, but I need more education to tackle one.
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Emma does not have a number greater than three in any month; please clarify. Also, the "and/or" construction can simply be "or."
 
Upvote 0
My bad--originally we wanted the condition to be >=3, but changed it at the last minute.

You can substitute "or" for and/or if you like. I was just giving the option of using two formulas instead of one to the person who decided to help me.
 
Upvote 0
A VBA solution is pretty simple, unlike the only single cell formula I can think of (which is very long and nasty):


Excel 2010
ABCDEFG
1MONTHChuckDavidEmmaFredGraceHarry
2Jan123153
3Feb000004
4Mar010002
5Apr000010
6May000000
7Jun001000
8Jul200010
9Aug000000
10Sep000200
11Oct010001
12Nov000000
13Dec000000
Sheet6 (2)


Code:
Sub rollingsumorvalue()
Dim d As Object, c As Range
Set d = CreateObject("scripting.dictionary")
For Each c In Range("b2:g13")
If Application.Sum(c.Resize(3)) >= 8 Or c.Value > 3 Then
d.Add Cells(1, c.Column).Value, 1
End If
Next c
Cells(Rows.Count, 1).End(xlUp).Offset(2, 0).Resize(d.Count).Value = Application.Transpose(d.keys)
End Sub


Excel 2010
ABCDEFG
1MONTHChuckDavidEmmaFredGraceHarry
2Jan123153
3Feb000004
4Mar010002
5Apr000010
6May000000
7Jun001000
8Jul200010
9Aug000000
10Sep000200
11Oct010001
12Nov000000
13Dec000000
14
15Grace
16Harry
Sheet6 (2)


(I hard coded the range but it can easily be changed to dynamic)
 
Last edited:
Upvote 0
Wait use this instead:

Code:
Sub rollingsumorvalue()
Dim d As Object, c As Range
Set d = CreateObject("scripting.dictionary")
For Each c In Range("b2:g13")
If Application.Sum(c.Resize(3)) >= 8 Or c.Value > 3 Then
d.Item(Cells(1, c.Column).Value) = 1
End If
Next c
Cells(Rows.Count, 1).End(xlUp).Offset(2, 0).Resize(d.Count).Value = Application.Transpose(d.keys)
End Sub
 
Upvote 0
Yeah, I know that a formula is nasty and painful, but I'm trying to avoid VBA because of all the hassle I get from auditors. The end user let me know that this isn't a critical item, but I'm still going to look into it as a learning exercise. It's become my "white whale" and I'm not going to let it go without a fight.

Thank you for the effort. Your macro is far more elegant than I could have created and I can't wait until I can devote some time to see how you made it work in so few statements.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top