Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

Hi Experts, this one has got me stumped. The attached sample workbook should explain what I'd like.

In short, I want to create a pseduo-solver type equation for a resource allocation problem. Ala, I want the the largest values in range G:AA to determine what Aircraft Type the Flight Hours value in column F should go to. A method to mitigate ties in Range G:AA is explained in the workbook.

Hoping you can help me save a lot of time here.

SampleWorkbook.xlsx

In short, I want to create a pseduo-solver type equation for a resource allocation problem. Ala, I want the the largest values in range G:AA to determine what Aircraft Type the Flight Hours value in column F should go to. A method to mitigate ties in Range G:AA is explained in the workbook.

Hoping you can help me save a lot of time here.

SampleWorkbook.xlsx

Do more with

EXPERT OFFICE^{®} is a registered trademark of EXPERTS EXCHANGE^{®}

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.

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.

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))
```

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

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!

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.

"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.

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
```

Your workbook after Solution...

Saurabh...

SolutionWithArrows.xlsm

No, thanks. Your condescending attitude may continue.

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

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.

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
```

Saurabh...

SolutionWithArrows.xlsm

Here is the revised code since you moved this to a different sheet i added a sheet reference and it gives the desired result again what you are looking for...

```
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
Dim ws As Worksheet
Set ws = Sheets("RealTable2")
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 = ws.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 ws.Cells(l, k).Value = vl Then
np = Application.WorksheetFunction.VLookup(ws.Cells(frow, k).Value, r1, 2, 0)
If np < cp Then
cp = np
z = Trim(UCase(ws.Cells(frow, k).Value))
End If
End If
Next k
If UCase(Trim(r.Value)) = z Then gethours = gethours + ws.Cells(l, r2.Column).Value
z = ""
Else
For k = fcol To lcol
If ws.Cells(l, k).Value = vl Then
If UCase(Trim(r.Value)) = Trim(UCase(ws.Cells(frow, k).Value)) Then gethours = gethours + ws.Cells(l, r2.Column).Value
End If
Next k
End If
Next l
End Function
```

Workbook...

Saurabh...

Saurabh-testingSolution.xlsm

## Premium Content

You need an Expert Office subscription to comment.Start Free Trial