Excel Help - Vlookups/ List Data

[CatD0203]

Hi All,

In need of some help with a spreadsheet i'm compiling for an online game.

 

I am trying to auto-populate a table based on raw data list. 

Image 1 is the list data.

Image 2 is the table that needs to populate based on list data.

Line 1 of the table does a vlookup to the raw data to populate table greyed out.

What I want to try to achieve is:

- Based on cell F11 'Rider', look to list > select next 'rider' in list

- When data selected, cumulative values in H3:H24, not to exceed cell I11 'max rally' figure.

I might be asking a lot here.... Any recommendations would be greatly appreciated.

Image 1

Image 2

Thank you in advance,

Cat

[#2]

Hi @CatD0203 

This is difficult to understand:

---

What I want to try to achieve is:

- Based on cell F11 'Rider', look to list > select next 'rider' in list

- When data selected, cumulative values in H3:H24, not to exceed cell I11 'max rally' figure.

---

Can you expand on each of the bullets to make it clearer?

What does "next 'rider' in list" mean exactly?

What does "cumulative values in H3:H24" mean? On the list sheet? Why not H2? What does cumulative mean in the context of column H on the list sheet, which seems to hold Yes and No values. 

[#3]

Do you want a dropdown list for quickly pick the datas in multiple columns at once?

[#4]

You're absolutely right. Sorry, I think I spent too many hours trying to articulate what I needed that I haven't been able to explain myself properly.

Let me know if this is clearer?

Within my workbook I have two tabs:

• Tab 1 (Image one) - 'List Data'
• Tab 2 (image 2) 'Turret Info'

Tab 1 'List Data', is a list of all active players and has a series of categories columns "A" - Alliance, through "I" - Mud Sit.

Tab 2 'Turret Info' is what I am hoping to populate based on tab 1 information.

Within Tab 2 table/cells start from D11:I11; D11:D24 (image 2 in original post)

What I want to do is select ‘Troop Type’ from a drop down in cell F11 – Rider, Shooter, Fighter (which I have); and hopefully create a VLOOKUP or function that would allow data from Tab1 (list data) to fill the following 12 rows based on troop type i.e., rider.

While the list is populating, I need to ensure that the sum total of cells H13:H24, does not exceed the value in I11. The value in I11 is based off of the first name in the list data matching ‘Rider’ type.

If this is too many circular references or too complex for excel let me know and I’ll manually do it!

Thanks again for looking and the feedback.

[#5]

@CatD0203 

Assuming you have access to the FILTER function, you can try this in cell E14 on the Troop Type sheet. Adjust the List Data range appropriately. 

=LET(
lookupData, 'List Data'!C2:G20, 
troopTypeCol, CHOOSECOLS(lookupData, 2),
nameCol, CHOOSECOLS(lookupData, 1),
filteredData, FILTER(lookupData, (troopTypeCol=F13)*(nameCol<>E13)),
filteredData
)

Please try that for now. It doesn't resolve the max-sum issue, but I need more detail to understand how to resolve that. 

When you say "ensure doesn't exceed", do you mean to just keep taking riders until one row before the sum would exceed the rally size? Does the order of the riders matter?

[#6]

@flexyourdata 

That definitely worked to populate the list!!

I need the list to stop at 12 names. So when 12 names/rows are populated, no more.

The not to exceed values:

So each 'Name' - comulmn C in the 'List Data' tab has a troop type (Rider, Shooter, Fighter), Troop Tier (T7, T8,T9, T10 - this bit isnt really relevant), March Size and Rally Size stats.

The first name populated in the table determines the 'Max Rally' shown in I11 - in real terms this mean the maximum number of troops that this player can carry. The 'March Size' per 'Name' is the max number of troops they can dispatch.

i.e. if max rally size was 1000

Player 1 - has a march size of 800

Player 2 - has a march size of 150

Player 3 has a march size of 150

Only player 1 and player 2 (or player 3) could fit without exceeding max rally size. The purpose of the table is try to populate 12 names/rows that most efficiently fit within the max rally size.

Hope that helps?

And thanks so much already - massive win!!

[#7]

@CatD0203 

I'm glad the formula is working so far. 

As for the selection of 12 players - this is quite a tricky problem. 

When you say "most efficiently fit" - do you mean the sum of the march sizes must be as close to but not exceeding the max rally? 

And must there be exactly 12 players and no fewer? 

Some considerations: 

1) what if the maximum number of players whose sum of march size is fewer than 12? 

2) to select the players, we could sort them in descending order by march size and then take the players as long as the sum doesn't exceed the rally size and continue until we've checked all players. This could end with total players fewer than 12 meeting the max rally size. 

3) we could try the same, but sort in ascending order by march size. The same risk of too-small cohort is possible

4) we could select 12 players at random and check how close they are to the rally size - but to ensure that we have the "best" 12 players, we'd have to repeat this until we've checked all combinations. Which would take way too long. 

For example, based on the data in your image, if we sort in ascending order, we can only select 10 players with the sum shown:

If we sort in descending order, we can only select 6 players before exceeding the max rally. 

[#8]

Thanks again for the input - not to exceed 12 works; or if it only take 8 rows to add up to max rally - also fine!

I did say big ask!

Thank you again.

[#9]

@CatD0203 

OK, it shouldn't be too difficult. It's just quite challenging to get it right without sample data.

Can you share an example file?

[#10]

@flexyourdata 

That would really help wouldn't it...... (sorry!!)

Should be attached now.

Thank you!

[#11]

@CatD0203 

I think the attached should do the trick. 

=LET(
    lookupData, 'List Data'!C2:G119,
    maxRally, $I$11,
    TroopTypeCol, CHOOSECOLS(lookupData, 2),
    nameCol, CHOOSECOLS(lookupData, 1),
    filteredData, FILTER(lookupData, (TroopTypeCol = F13) * (nameCol <> E13)),
    sorted, SORT(filteredData, 4, -1),
    scanned, SCAN(
        INDEX(sorted, 1, 4),
        SEQUENCE(ROWS(sorted)),
        LAMBDA(a, b,
            LET(
                curr, INDEX(sorted, b, 4),
                runSum, SUM(TAKE(CHOOSECOLS(sorted, 4), b)),
                runSum <= maxRally
            )
        )
    ),
    result, FILTER(sorted, scanned),
    result
)

[#12]

@flexyourdata 

Thanks again for your work on this.

It' giving a 'CALC' error. On the copy you sent through the Vlookup was missing from F13 on 3rd tab. As soon as i corrected this, the formulae didn't work.

Any suggestions?
Thank you!

[#13]

@CatD0203 

Sorry about that - user error. I typed over the formula in cell F13 to see if the bigger formula worked. Didn't realise it had a VLOOKUP in it. 

Looking at the file you just attached, I think when you replaced it, you've put the wrong column index in the VLOOKUPs in F13 and G13. You've got the Troop Tier Level in F13 and the Troop Type in G13. 

Change the third parameters:

// For Troop Type (column index should be 2):
=VLOOKUP($E$13,'List Data'!$C$2:$G$119,2,FALSE)

// For Troop Tier Level (column index should be 3):
=VLOOKUP($E$13,'List Data'!$C$2:$G$119,3,FALSE)

Alternatively, get rid of the formulas in columns F through I on row 13 and put this in F13 instead:

// Single formula for the blue cells F13:I13. Place in F13:
=XLOOKUP($E13,'List Data'!$C$2:$C$119,'List Data'!$D$2:$G$119,"Name not found")

See attached. 

[#14]

@flexyourdata 

Thank you so much for this. It's been working great, but i fear i may have broken the document.

I added in more data to the 'List Data' tab and adjusted the parameters but the data now doesn't pull through into the table. Especially when changing 'Captain' names.

Really sorry to throw this at you again. Much apprecaited as always.