ExpExchHelp
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
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
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
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
ASKER
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
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
ASKER
Sorry... the file wasn't posted in my last message. Here it is.
Testing.xlsx
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.
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
Offset-Function.xlsm
ASKER
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
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! :)
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
And then all you need is to replace the line#20
In this case, you will also need to change the line#22 to this...
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))
WITHmaxCnt = wsData.Range("AB1").Value
And replace line#20If wsData.Range("H" & r).Value >= maxCnt - 1 Then
WITHIf wsData.Range("H" & r).Value >= maxCnt Then
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
WITHIf wsData.Range("AA" & r).Value >= maxCnt Then
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
ASKER
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.
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
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
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
ASKER
Sorry... last posting didn't include the latest XLS. Here it is...
Offset-Function----with-VBA-v02.xlsm
Offset-Function----with-VBA-v02.xlsm
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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.
Thanks for the feedback.
ASKER
My pleasure... all the best until the "next time". :)
Attached.
Offset-Function.xlsx