Help with a Fromula that finds the largest value in a Range and then....

"Solution Cells (in yellow) find the Largest Value in Range G:AA (signifying the Aircraft most "Fit" to perform the Flight Hours) THEN place the flight hours amount for that record ID on the aircraft type with the Largest Value. If there is a tie for largest value in G:AA the tie should go to the aircraft with the lower operating cost per hour (See Table1 column B). Please do not transpose aircraft types in the tables, they mimic the real workbook."

I think I know what you are asking, although your wording is ambiguous; especially regarding "G:AA".

Just for clarity, please can you advise what figure you are expecting to see in cell [ C2 ], & explain how that value was selected?

Thank you.

Sure,

Let's say that aircraft A was both the cheapest to operate and the most fit aircraft for all of the records in table 2. As such the sum of table2[flight hours] would go exclusively in cell c2. If aircraft A and B were equally as fit to perform all of the records the sum of table2[flight hours] would still go on aircraft A because it is cheaper to operate.


In terms of the workbook I attached record 1's 281 hours would go on Aircraft type N because there are no ties.

Thanks.

However, to repeat my query:

Just for clarity, please can you advise what figure you are expecting to see in cell [ C2 ], & explain how that value was selected?

I don't think 281 (hours) is to be found in cell [ C2 ] from your reply.

c2 should be the sum of flight hours from table2 in which aircraft A was the most fit aircraft to perform an individual record.  Ties managed as discussed above.

The values  for table2[a:u] (regular range g:aa) were random so I don't know where the above is true.

Looking at it by eye (on my phone) it appears aircraft A is the most fit only in one record (record is 20) therefore c2 would be 301 hours.

Hi again,

I am still a little confused about what you are looking for.

If we ignore "tied" results for the time being, I have placed the following formula in cell [C2]:

=INDEX($F$3:$F$22,MATCH(MAX(OFFSET($F$3:$F$22,0,MATCH($A2,Table2[[#Headers],[A]:[U]]))),OFFSET($F$3:$F$22,0,MATCH(A2,Table2[[#Headers],[A]:[U]])),0))

Select all Open in new window

I have copied this down from cell [C2] to cell [C22].

The results I see are as follows:

[C2]: 301 (for Aircraft Type "A")
[C3]: 281 (for "B")
[C4]: 311 (for "C")
[C5]: 311 (for "D")
.
.
.
[C22]: 458 (for "U")


However, I really do not know if these figures are what you were expecting.

The formula in column [C] finds the relevant column in the range [G2:AA2] that matches the "Aircraft Type" stored in corresponding column [A].

Within the appropriate column, the maximum value is located, then the value for "Fight Hours" (column [F]) is retrieved.

However, I am lost what you are meaning by "most fit", & what the relevance of column [ B ], "CostperHour" has with the calculation (if at all).


Perhaps if you describe, in plain English, how to reach the value required (if I have missed the mark so far), I (or any other Expert) may have a better chance of providing what you require.

Also, a description of what to do (in plain English) with a "tied" scenario would be helpful.

Thank you.

I have attached your original workbook with the above formula applied.
Q-28696999.xlsx

Ignoring ties; there is something up with  the formula. If we look at the 281 hours from Record ID: 1 they were incorrectly placed on Aircraft B in cell C3. The MAX value for ID1 was Aircraft Type M value of 24; Aircraft B was a value of 23.

Regarding ties.... in plain English.... the values in table2 are a measure of "Fit" to perform the Flight Hours. (Lets pretend that the only Fit we were measuring was an Aircraft's ability to Hover). Table1[[CostperHour]] measures the costs to perform flight hours (by the hour). So.... knowing this ask yourself the question:

"If I have 2 aircraft that can hover equally as well, then where should I allocate the flight hours they'll be Hovering?"
Answer: "to the aircraft that can do it cheaper" Ala, ties are given to the aircraft that has the lower cost per hour.

PS I've used Index(Match(Match before but never nested an Offset, in the real workbook these two tables are on separate worksheets so I'd like to use column headers avoid the Offset unless absolutely necessary.

I've solved for the records with orange Flight Hours Amounts.

Note1 Record 2 had a 3 way tie between the green colored cells, the flight hour value for record 2 went to the aircraft with the lower cost per hour.

Note2 that Aircraft F was the highest value for more than 1 record, therefore the value is a Sum of flight hours from the records at which it was highest.
OrangeRecordsManuallySovled.xlsx

"Ignoring ties; there is something up with  the formula. If we look at the 281 hours from Record ID: 1 they were incorrectly placed on Aircraft B in cell C3. The MAX value for ID1 was Aircraft Type M value of 24; Aircraft B was a value of 23."

The value of 23 in cell [H3] is one of the (& the first of the) largest values in column [H] (for Aircraft Type "B"), so that is selected, & the corresponding value in column [F] ("Flight Hours") is 281 (i.e. cell [F3]) so that value is transposed to cell [C3].

If that logic not what you intended?

The rest of your reply still uses terms like "fit", & "hover"; these are still alien to me as I am not involved in your industry.  That is what I meant by "plain English".  You seem to continue using "plane English" :)

Also, regarding your postscript about using different worksheets.  Please provide the necessary sample workbook to match your exact requirements.  It makes any attempt to help you, so much easier!

The value of 23 in cell [H3] is one of the (& the first of the) largest values in column [H] (for Aircraft Type "B"), so that is selected, & the corresponding value in column [F] ("Flight Hours") is 281 (i.e. cell [F3]) so that value is transposed to cell [C3].

No, pivot your thinking. I'm interested in the largest value running horizontally across the Record ID.  

Did the solved 3/4 record IDs in the workbook I attached clarify things for you?

Sorry, I am really lost (again).

What is the correlation between cell [C2] & looking across the range [G3:AA3] (i.e. Record ID: 1)?

...and, no, your workbook did not give me any guidance, I am sorry to say.  Not only could I not match why the figures in cells [C7], [C9], & [C14] were there, I did not understand your colour-coding... & I don't think that is because I have Protanomaly & Deuteranomaly colour-vision deficiency!

Seriously, I must be missing something obvious if all the data is there.

Queries:

a) Why do you want to look across rows, rather than down columns, of data within "Table 2"?
b) How do the "Aircraft Types" in column [A] (of "Table 1") relate to the Record IDs (in column [E]; "Table 2")?
c) How are the values in column [C] (of "Table 1") "Sum of Flight Hours" calculated?

I think column [ B ] "CostperHour" may have relevance if more than one match is found in "Table 2", but adding that into the reply (now) may well confuse the issue I am having resolving the basic requirements.

If I am still at a loss after your further explanation, then I think leaving this question to somebody else may well be the best course of action.

a) Why do you want to look across rows, rather than down columns, of data within "Table 2"?
-Because each value going across the row is how well an aircraft performs that Record ID I care about comparing how an aircraft performs vs its replacement aircraft on each record ID.

b) How do the "Aircraft Types" in column [A] (of "Table 1") relate to the Record IDs (in column [E]; "Table 2")?
-They are the same aircraft, hence they have the same letter.

c) How are the values in column [C] (of "Table 1") "Sum of Flight Hours" calculated?
-I've drawn some arrows in my workbook to help illustrate how the values got there. Hoping that we can Fisher Price this solution.
SolutionWithArrows.xlsx

I fixed the "corrupt" attachment, not sure if you were having issues with it....

Lol... any better?

Anyone else wanna participate on this one i think my workbook with arrows scared fanpages into hiding.

(Comment 2015-07-15 at 21:22:22 ID: 40883779)

"If I am still at a loss after your further explanation, then I think leaving this question to somebody else may well be the best course of action. "

Sorry, I am still confused.

Please re-request attention to prompt any other willing party to assist.

Good luck.

Polak,

Their you go i wrote a udf for your problem to do what you are looking for.. Now if you compare the results which i'm getting with the results you are displaying + an comparison of hours which i did it's matching and it gives you what you are looking for..

Function gethours(r As Range, r1 As Range, r2 As Range, r3 As Range, r4 As Range)
    Application.Volatile
    Dim cell As Range, fcol As Long, lcol As Long, frow As Long
    Dim vl As Long, cp As Long, np As Long, rng As Range, rng1 As Range, l As Long, k As Long
    Dim fr As Long, lr As Long, z As String
    fcol = r4.Column
    lcol = r4.Columns.Count + fcol - 1
    fr = r4.Row
    lr = r4.Rows.Count + fr - 1
    frow = r3.Row
    cp = 99999999
    For l = fr To lr
        Set rng = Range(Cells(l, fcol).Address & ":" & Cells(l, lcol).Address)
        vl = Application.WorksheetFunction.Max(rng)
        If Application.WorksheetFunction.CountIf(rng, vl) > 1 Then
            For k = fcol To lcol
                If Cells(l, k).Value = vl Then
                    np = Application.WorksheetFunction.VLookup(Cells(frow, k).Value, r1, 2, 0)

                    If np < cp Then
                        cp = np
                        z = Trim(UCase(Cells(frow, k).Value))
                    End If
                End If
            Next k
            If UCase(Trim(r.Value)) = z Then gethours = gethours + Cells(l, r2.Column).Value
        Else
            For k = fcol To lcol
                If Cells(l, k).Value = vl Then
                    If UCase(Trim(r.Value)) = Trim(UCase(Cells(frow, k).Value)) Then gethours = gethours + Cells(l, r2.Column).Value
                End If
            Next k

        End If

    Next l

End Function

Select all Open in new window

Your workbook after Solution...

Saurabh...
SolutionWithArrows.xlsm

Ah ha! Glad I my talking in circles made sense to some one. Okay so doing some light testing I noticed an issue... go to Cell S8 in your workbook and increase the value to 25. You'll see that the flight hours for Aircraft L have increased to 2078 (Odd, given the record IDs FH Value is only 270). Even weirder is if after increasing to 25 we decrease to 0 it seems to modify the Values of Two other Aircraft simultaneously.... that shouldn't ever happy since if a "winner" of FH becomes a "loser" all the hours should only go to the next highest rated aircraft "in line".

I wish I could appreciate how cool your UDF solutions are, but they frequently go WAY over my head. That said I'm still using a unconcatenate and matrix lookup UDF you wrote me a while back and I love it.

However, how flexible is this UDF going to be? for example if in the really workbook table1 is on a different worksheet from table 2, or perhaps the record ID column is not directly to the left of the "fit" values, or perhaps a real Aircraft type has unusual characters in it "/" "-" etc... is any of that going to be an issue ? perhaps we can step through the code so I can know it's limitations?

"Been looking for that button, hasn't popped up yet. If you wanna message me some contact info of some sort I'm happy to explain in another conversation medium if you think that will help you. I don't think that this is as complicated as you think/we are making it."

No, thanks.  Your condescending attitude may continue.

Woah now. I think we've miscommunicated on more than just some spreadsheets. I'm not sure what I said that was condescending; perhaps it was my Plane English...., but nothing said here was meant to be inflammatory, sorry that it was interpreted that way.

OK, if that was not your intention, I apologise.

You have another party engaged with the project now, so I hope you will reach the required result soon.

Not at all, was just trying to get some attention from another expert & see if you had truly given up. Cheers.

I presume you are aware that the "Experts" are not paid staff, but volunteers participating in questions in their free time.  Volunteers that have their own commitments & priorities outside of the site, of course.

The fact we are in different time zones (me in the UK, & you in the United States) is also not conducive to providing expedient exchanges & resolutions either.

The medium of text-based communication is, as you probably realised not ideal to relay information, as the word of one party can easily be misinterpreted by another using localised turns of phrase, & jargon specific to their experience/knowledge.

Even if I cannot help directly, I will often try to establish more facts/re-wording to entice other volunteers to participate, but also to ascertain why a problem is seemingly being ignored.  It seems that approach finally prompted the interest of another party.

We obviously didn't meet on the same terms in this question, but hopefully that will not always be the case.

Hope you have a pleasant day.

Polak,

I notice now what you are try to do and their was a small thing that i didn't do since i had to take the assumption in loop and once i do that vola is fixed...

Polak always happy to help here and glad i have been able to do so in the past and yeah UDF is my logical mind i have written in formula...

Now couple of things for your answer on this formula...

1. It doesn't matter if your air type and cost per hour is in different sheet the UDF will still work.
2. It doesn't matter how you aircraft names are..till the time both are identical for both table-1 and table-2 it will match and work.
3. it doesn't matter the flight hours are on the right side or on the left side till the time you define that range correctly it will automatically pick those flight hours.
4. The only thing you need to be constraint about in table1 that aircraft type should be the first column and cost per hour should be second since i have used vlookup formula to determine cost per hour in case of tie.
5. I have assumed your cost per hour won't got more then 99999999 in this line cp = 99999999, However if it goes more than this..Then accordingly you need to change it..

Enclosed is the revised UDF and Workbook...

Function gethours(r As Range, r1 As Range, r2 As Range, r3 As Range, r4 As Range)

    Dim cell As Range, fcol As Long, lcol As Long, frow As Long
    Dim vl As Long, cp As Long, np As Long, rng As Range, rng1 As Range, l As Long, k As Long
    Dim fr As Long, lr As Long, z As String
    fcol = r4.Column
    lcol = r4.Columns.Count + fcol - 1
    fr = r4.Row
    lr = r4.Rows.Count + fr - 1
    frow = r3.Row
    
    For l = fr To lr
    cp = 99999999
        Set rng = Range(Cells(l, fcol).Address & ":" & Cells(l, lcol).Address)
        vl = Application.WorksheetFunction.Max(rng)
        If Application.WorksheetFunction.CountIf(rng, vl) > 1 Then
            For k = fcol To lcol
                If Cells(l, k).Value = vl Then
                    np = Application.WorksheetFunction.VLookup(Cells(frow, k).Value, r1, 2, 0)

                    If np < cp Then
                        cp = np
                        z = Trim(UCase(Cells(frow, k).Value))
                    End If
                End If
            Next k
            If UCase(Trim(r.Value)) = z Then gethours = gethours + Cells(l, r2.Column).Value
            z = ""
        Else
            For k = fcol To lcol
                If Cells(l, k).Value = vl Then
                    If UCase(Trim(r.Value)) = Trim(UCase(Cells(frow, k).Value)) Then gethours = gethours + Cells(l, r2.Column).Value
                End If
            Next k

        End If

    Next l

End Function

Select all Open in new window

Saurabh...
SolutionWithArrows.xlsm

Saurabh, just a heads up I haven't had a chance yet to incorporate and test in my real workbook, but I haven't forgotten. Also, good to know on points 1-5. If an aircraft is unavailable in a specific location in my workbook it's cost actually becomes 99999999999. So I will be modifying like you said! Thanks!

Sure Polak..and yeah you need to make changes accordingly basis of your data that you have...

Hi Saurabh, I've having a little trouble adapting your UDF to the real workbook. To Troubleshoot lets go back to your working solution first.

If you take Table2 in the working solution and move it to a new sheet (Even pasting it in Cell F2 on the new sheet) the UDF ceases find the largest value and mitigate ties.

To verify that we not only fix this in your most recently posted solution, but also in the real workbook, I've  duplicated the real workbooks slight differences in table positions / structure in the attached.  If possible, I'd like for us to have a column between Aircraft type and CPFH (cost per flight hour). I know you had mentioned in #4 above that you were using a vlookup and those had to be next to eachother. In the real workbook the solve of the UDF should be inbetween CPFH and Aircraft Type; however, if it's too much trouble I can live with moving CPFH next to Aircraft type. Please see the attached.
Saurabh-testingSolution.xlsm

Awesome Saurabh, thanks for all the help!

Yw..Polak Happy to Help.. :-)

Saurabh...

PS I changed Application.WorksheetFunction.VLookup(ws.Cells(frow, k).Value, r1, 2, 0) to
Application.WorksheetFunction.VLookup(ws.Cells(frow, k).Value, r1, 3, 0) so that CPFH didn't have to be right next to Aircraft Type. Wish I could understand the rest of it tho! thx again.

Polak,

I don't see any problem in their till the time your r1 range has 3 columns since this is simple vlookup formula and all the logics of vlookup stands true here..

Saurabh...