Challenging formula how to do this without helper column

please see attached file.  I have the working formula in Column G. however, what is really annoying for me is use of helper column F.  

how can the formula be modified perhaps with SUMPRODUCT or any other function that can help getting rid of the helper column F.

thanks.
Book11.xlsm
LVL 6
FloraAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rspahitzCommented:
It seems to me that if the formula is working, the simplest solution is to simply hide column F.
0
Saurabh Singh TeotiaCommented:
Flora,

Not sure what you are trying to do but if you apply this formula even..

=VLOOKUP(A2,$A$2:$E$34,5,0)

Open in new window


It gives you the same results what you have..

Saurabh...
0
FloraAuthor Commented:
rspahits, thanks.  but with the actual workbook i have, having helper column will mislead my users. so i know the current formula works. but i wanted to know it is possible to do it without helper column that is why i opened this question.


Saurabh,
many thanks for looking into this.   this question is related to my previous question,  http://www.experts-exchange.com/questions/28788979/Excel-formula-needed-to-Return-text-ID-that-its-sum-has-Maximum-Value.html   Martin had previously helped with VBA solution and also i have currently the formula solution with helper column, i was wondering if formula could be modified which will work without using the helper column.
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

rspahitzCommented:
Since this is already an xlsm file, I assume you are okay with macros.
Would you be opposed to a custom function that could eliminate the helper column?

Looking at this further, the macro would be sufficiently more complex.

Another option is to move the helper column to another worksheet and hide the worksheet (and even password protect if needed.) This way it's out of the way of the users and won't confuse them.
I'm assuming that the users have no reason to look at the formulas (because if they're advanced enough for that they shouldn't get confused by a hidden column on a sheet.) If they shouldn't look at the formulas, you should also probably lock the cells that contain formulas (so they can't change them)...and unlock the cells they should be interacting with, and then protect the sheet.
0
Saurabh Singh TeotiaCommented:
Just a quick question...You open to a UDF or a macro solution as i guess the UDF can easily do what you are looking for..

Saurabh...
0
FloraAuthor Commented:
ok,

thanks.  UDF would be okay too
0
FloraAuthor Commented:
Saurabh,

i do not want to be nagging you on this, if UDF is not possible, i will close the question.
0
Saurabh Singh TeotiaCommented:
Flora,

Give me time till thursday..I'm catch up on couple of things will post a solution for this max by thursday.

Saurabh...
0
FloraAuthor Commented:
many thanks Saurabh
0
Saurabh Singh TeotiaCommented:
Flora,

Just to re-cap so that i understand the question correctly to write a udf for you..This is what i understood that what you are trying to do...

You want to use Column-A first as a identifier for Order Id...Now You want to look at Column-C And Column-D and where these values match You want to sum Column-B.. Now after summing Column-B and grouping by Column-E which is category... Now the category which has the highest sum you want to show that as an answer...

Let me know if this is not what you are looking for...

Saurabh...
0
FloraAuthor Commented:
you understood correctly Saurabh
0
Saurabh Singh TeotiaCommented:
Flora,

Enclosed is the udf, Along with workbook for your reference which does what you are looking for..

Function getcategory(r As Range, r1 As Range, r2 As Range, r3 As Range, r4 As Range, r5 As Range)
    Application.Volatile
    Dim cell As Range, str As String
    Dim z As Long, k As Long, str1 As String

    n3 = r5.Column - r1.Column
    str1 = r.Offset(0, n3).Value
    k = Application.Evaluate("=SUMPRODUCT((" & r1.Address & "=" & r.Address & ")*(" & r2.Address & "=" & r3.Address & ")*(" & r4.Address & ")*(" & r5.Address & "=" & r.Offset(0, n3).Address & " ))")

    str = r.Offset(0, n3).Value
    For Each cell In r1

        If cell.Value = r.Value And cell.Offset(0, n3).Value <> r.Offset(0, n3).Value And InStr(1, str, cell.Offset(0, n3).Value, vbTextCompare) = 0 Then

            z = Application.Evaluate("=SUMPRODUCT((" & r1.Address & "=" & cell.Address & ")*(" & r2.Address & "=" & r3.Address & ")*(" & r4.Address & ")*(" & r5.Address & "=" & cell.Offset(0, n3).Address & " ))")

            If z > k Then
                k = z
                str1 = cell.Offset(0, n3).Value
            End If


            If str = "" Then
                str = cell.Offset(0, n3).Value
            Else
                str = str & "," & cell.Offset(0, n3).Value
            End If

        End If
    Next cell

getcategory = str1
End Function

Open in new window


Saurabh...
Book11.xlsm
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FloraAuthor Commented:
Saurabh

wow, how did you do this?. i am really impressed.

thanks a million.
0
Saurabh Singh TeotiaCommented:
Flora,

Glad it worked for you ..and i sit down and thought about couple of ways you can do this and this was the best possible solution which i could have thought of so their you go..

Always happy to help.. :-)

Saurabh...
0
FloraAuthor Commented:
@Saurabh  

you are indeed genius.

people says, i do not get easily impressed, but this work really impressed me.

thanks again for your help and support.
0
Saurabh Singh TeotiaCommented:
@Flora,

Thanks for the kind words really appreciate it.. :-)

Saurabh...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.