Link to home
Start Free TrialLog in
Avatar of ExpExchHelp
ExpExchHelpFlag for United States of America

asked on

Need assistance with OFFSET formula

Experts:

I need some assistance with, I believe, an OFFSET function.   I have attached an XLS which includes the problem background, process, and what I would like to achieve.

Essentially, I have a column (H) which includes counts of "high" values based on values in columns C:F.   If a count (in column H) either equals "3" or "4", I then would like the corresponding values in columns A:B listed on a secondary tab.

In this example, there are six responses that I need to **dynamically** display on the second tab.   One response occurs 4 times... thus, it should be at the top of the list (array).   Five responses occur 3 times; therefore, they should be listed in the order they occur in columns A:B (first tab "Raw Data").

As always, a "picture is worth a thousand words"... if the above description sounded confusing, I am confident the attached spreadsheet should make more sense.

Thank you for your assistance in advance.

EEH
Offset-Function.xlsx
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image

Achieved with Pivot Table again.

Attached.
Offset-Function.xlsx
Avatar of ExpExchHelp

ASKER

Rob - thank you for chiming in.

In this case (for other reasons not explained here), I need to stay away from using pivot tables.   Right now, I am using the LARGE function as a helper column... combined with INDEX to look up values.  

Still I am not getting the accurate accounts yet.

Thank you,
EEH
Please find attached spreadsheet "testing"... I have included a variety of approaches (incl. LARGE, COUNTIF, etc.)  

I tried to used the LARGE in conjunction with the INDEX/MATCH function.   However, given the lack of distinct/unique high counts, the associated categories (values from columns A:B) are incorrect.

Any thoughts on how I might achieve what's currently displayed in the 2nd tab ("Output")?

Thank you,
EEH
Sorry... the file wasn't posted in my last message.   Here it is.
Testing.xlsx
Honestly, I would prefer to do this with VBA instead of having any complex and volatile formulas on the sheet.
If you are okay with a VBA solution, please find the attached and click the button called "Create Summary" to populate the desired output on Output Sheet.

Sub Summary()
Dim wsData As Worksheet, wsDest As Worksheet
Dim rng As Range, cell As Range
Dim lr As Long, r As Long, i As Long
Dim x()
Dim maxCnt As Long

Application.ScreenUpdating = False
Set wsData = Sheets("RawData")  'Data Sheet
Set wsDest = Sheets("Output")   'Destination Sheet
wsDest.Range("A1").CurrentRegion.Offset(1).Clear
lr = wsData.Cells(Rows.Count, 2).End(xlUp).Row
ReDim x(1 To lr, 1 To 3)
maxCnt = Application.Max(wsData.Range("H3:H" & lr))
If maxCnt = 0 Then Exit Sub

For Each rng In wsData.Range("B2:B" & lr).SpecialCells(xlCellTypeConstants, 2).Areas
    For Each cell In rng
        r = cell.Row
        If wsData.Range("H" & r).Value >= maxCnt - 1 Then
            i = i + 1
            x(i, 1) = wsData.Cells(r, "H").Value
            x(i, 2) = rng.Cells(1).Offset(-1, -1).Value
            x(i, 3) = cell.Value
        End If
    Next cell
Next rng
wsDest.Range("A2").Resize(i, 3).Value = x
wsDest.Range("A1").CurrentRegion.Sort key1:=wsDest.Range("A2"), order1:=xlDescending, key2:=wsDest.Range("B2"), order2:=xlAscending, Header:=xlYes
wsDest.Range("A1").CurrentRegion.Offset(1).Borders.Color = RGB(191, 191, 191)
wsDest.Rows(wsDest.UsedRange.Rows.Count).Delete
wsDest.Activate
Application.ScreenUpdating = True
End Sub

Open in new window

Offset-Function.xlsm
Subodh:

Yet again, another brilliant solution that you've provided!   Thank you!

Based on the example data, the solution works perfectly.   I would like to ensure I can follow your VBA code given that I will need to consider actual/new data.   Please allow me to explain where I'd like to make adjustments.

Right now, as depicted in my original question, I only want to include those matches equal to either "4" or "3".   Your code does exactly that.  

1. What if I wanted to also include # of occurrences equal to "2" as well... maybe even "1".    Where in the code could I include those changes?
2. Also, my example data only included four (4) calendar years.  Once I add a 5th calendar year, there's a 100% probability that I will have occurrence counts between 1:5.  

All that said, is there a way I could enter a "Max" & "Min" value -- let's say in cells L1:M1 (next to the "Create Summary" command button) whose values would determine what the VBA outputs in the tab "Output"?   In other words, if L1 = 3 and M1 = 4, I would get exactly what the VBA currently produces.   However, if I were to change L1 = 2, it then would show all additional records upon clicking the command button.

Is that doable?

Again, thank you for your assistance thus far.   I very much appreciate it.

EEH
You're welcome! :)

1. What if I wanted to also include # of occurrences equal to "2" as well... maybe even "1".    Where in the code could I include those changes?
Right now the code finds the max count in column H and say it is 4, then the code will extract all the occurrences which are greater or equal to 4 - 1 i.e. 3.
If that's not what you want and want to make it dynamic as per your requirement, you can create a helper cell with that count, say cell AB1 and input 2 in there and then replace the line#14
maxCnt = Application.Max(wsData.Range("H3:H" & lr))

Open in new window

WITH
maxCnt = wsData.Range("AB1").Value

Open in new window

And replace line#20
If wsData.Range("H" & r).Value >= maxCnt - 1 Then

Open in new window

WITH
If wsData.Range("H" & r).Value >= maxCnt  Then

Open in new window



2. Also, my example data only included four (4) calendar years.  Once I add a 5th calendar year, there's a 100% probability that I will have occurrence counts between 1:5.
Right now you are using column H as a helper column and if you add data for another calendar year and you are sure that the column H will remain intact, I don't see any issue. But if you are using column H as helper column only, you can shift that helper column to far from the actual data set, say you create column AA as a helper column. That way, the helper column will not be visible to users.
And then all you need is to replace the line#20
If wsData.Range("H" & r).Value >= maxCnt - 1 Then

Open in new window

WITH
If wsData.Range("AA" & r).Value >= maxCnt Then

Open in new window

I am assuming here that you will input the count criteria in Cell AB1 as suggested above and that's why I have removed -1 from the line.

In this case, you will also need to change the line#22 to this...
x(i, 1) = wsData.Cells(r, "AA").Value

Open in new window

Subodh:

Brilliant!    That's exactly the solution I needed.   It's flexible and easy to follow.

I have made the VBA changes but I wasn't entirely clear on the last recommendation.
x(i, 1) = wsData.Cells(r, "AA").Value

Open in new window


You recommended moving the helper column into AA (btw, I moved it into "O" for now).  What does the last line of code exactly do?    Pls see attached the most current version with proposed changes and a 5th calendar year.  

Again, it looks like everything is being generated correctly (I will thoroughly test tomorrow morning at work), but I am still curious on the last change recommendation (whether or not it's needed).

Thank you,
EEH
Sorry... last posting didn't include the latest XLS.   Here it is...
Offset-Function----with-VBA-v02.xlsm
ASKER CERTIFIED SOLUTION
Avatar of Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj)
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Subodh:

Thank you for the positive feedback.   Your change recommendation were precise, so it was very easy to make the proposed changes.  

I wish all "Experts Exchange experts" were as supportive and kind as you have been.   Again, I appreciated your assistance with this topic.

EEH
You're welcome! Glad I was able to help.
Thanks for the feedback.
My pleasure... all the best until the "next time".  :)