Improve OFFSET and CONCATENATION

[graeme27uk]

If you play the game Bloodbowl then you may recognise this. It's an old workbook that I started quite a while back but left due to "life circumstances". However, now I would like to complete it.

Most of it is ok at the moment (though I can't remember why or how I created OFFSET tables..... but hey ho).

REQUEST 1

On the "TR 1" sheet, the formula in Column I is rather long but I am sure there is a simpler and neater way of doing the same thing.


REQUEST 2

On the "Player Data" sheet, the skills (Column K) are currently typed in. However, Columns CE:FH are all the skills available. As you can see if the player has that skill I have put an "x". Is it possible for Column K to pick out and concatenate those skills with the "x" so that Column K can be auto-filled in.


Any other things I need on this but can't work out I will also post into this thread.

Thanks for any support given, much appreciated.

BTW - whilst I can work out most things on Excel (even if I forget) I have no knowledge of Visual Basic so would prefer not to use Macros if avoidable.

[CAntosh]

For Request #2, you might find the following UDF helpful. You could input the following VBA function in a module:

Please Login or Register  to view this content.

Then paste the formula below in K4 and fill down:

=IFERROR(Lookup_concat("x",$CE4:$FH4,$CE$3:$FH$3),"")

The new function looks for the first term ("x") inside of the row defined by the second term ($CE4:$FH4) and returns the corresponding values in the third term ($CE$3:$FH$3) in a concatenated string.

EDIT: I fixed a typo that originally had the third term only going through FE. It has been corrected to FH.

DOUBLE EDIT(!!): I completely failed to notice your comment about avoiding macros. Apologies! That said, VBA would really simplify your issue. We can help you through it, if necessary.

[CAntosh]

Please note my edit above.

I think the same function might help you address request #1, but I'm still eyeballing what that one does...

[CAntosh]

You can try the formula below in I3 (then fill down) to address request #1. It will be a little imperfect with spaces, but I think it provides the information you're after? It returns matching values from Player Data, followed by "Loner" if column U has an "x", followed by whatever is entered in X3:AC3.

=IFERROR(TRIM(INDEX('PLAYER DATA'!$K:$K,MATCH($C3,'PLAYER DATA'!$B:$B,0))&IF($U3="x"," Loner"," ") & X3&" "&Y3&" "&Z3&" "&AA3&" "&AB3&" "&AC3),"")

[dflak]

Too bad you are dead set against VBA. This code would work. Put it in a module, and then in Cell K put the following formula: =GetStr(Row()) and copy it down.

Please Login or Register  to view this content.

[graeme27uk]

It's not that I am dead set against VBA.... I just have no clues how it works, how to input modules or whatever.

If someone were to talk me through it then I would use them. The thing is, if it goes wrong with a formula I can generally work out why. With VBA I couldn't.

[graeme27uk]

For Request #2, you might find the following UDF helpful. You could input the following VBA function in a module:

Please Login or Register  to view this content.

Then paste the formula below in K4 and fill down:

=IFERROR(Lookup_concat("x",$CE4:$FH4,$CE$3:$FH$3),"")

The new function looks for the first term ("x") inside of the row defined by the second term ($CE4:$FH4) and returns the corresponding values in the third term ($CE$3:$FH$3) in a concatenated string.

EDIT: I fixed a typo that originally had the third term only going through FE. It has been corrected to FH.

DOUBLE EDIT(!!): I completely failed to notice your comment about avoiding macros. Apologies! That said, VBA would really simplify your issue. We can help you through it, if necessary.

Eh? THis is where my understanding and knowledge falls flat. I have no idea what a UDF is or how to set up a VBA module.

[CAntosh]

Sorry, I overlooked your comment about macros earlier. If you want to give them a try, do the following:

1. Hit Alt + F11 to open the VB Editor
2. In the bar at the top, click Insert > Module
3. Paste my code or dflak's in the window that pops open in the VB Editor
4. Close the editor

Dflak and I both posted UDFs, which are User Defined Functions. Once they're in a module, they'll act just like regular functions - type: =Lookup_concat( and you'll see the UDF appear as a function option. If you want to add both functions and try them out, repeat steps 1-4. Give it a try!

[graeme27uk]

Too bad you are dead set against VBA. This code would work. Put it in a module, and then in Cell K put the following formula: =GetStr(Row()) and copy it down.

Please Login or Register  to view this content.

What do you mean "cell K" ?

Sorted now. So that solves #2.

Any more thoughts on how to get rid of the nasty messy concatenate formula?

[graeme27uk]

OK.

So Request #2 is solved. Yay! Thanks very much.

Request #1 about tidying up the big concatenate formula still remains open.

New bits:

REQUEST #3

On the Player Data sheet at the end there is now a "Player cost calculator".
I have sorted out the MA, ST, AG, AV bits.
The next bit I know it should be able to do but not sure how.
On the REFS sheet there is a list of skills, Column S.
In column T are the costs of each skill.

What I want it to be able to do is if I put X's in the skills that that player has then I would like it to total the costs of those skills.
Would I need to use a INDEX(MATCH)) nested array formula?

REQUEST #4

Is there a way of automatically updating the skills list in column S on the REFS sheet based on the list of skills in the PLAYER DATA sheet in cells CE3:FI3?
Even if I add or delete skills?
Is this a TRANSPOSE forumla?


And last for the moment... can someone explain what the OFFSET formula does and why its useful in naming lookup tables? I can't remember who or why now but the lookup table names all have the OFFSET formula in.

[ChemistB]

Not sure why you put the GetStr() UDF into I3?
For I3, I would use this another UDF (ConcatAll)
With that function (see below), the formula simplifies to
In I3
=IF(C3="","",CONCATENATE(IF(U3="x","Loner, ",""),IF(VLOOKUP(C3,stats,10,FALSE)=0,"",VLOOKUP(C3,stats,10,FALSE)&IF(COUNTA($Z3:$AC3)>0,", ","")),CONCATAll($X3:$AC3,", ")))

You can paste this UDF into the same module where you have GetStr()
Typically, it takes the form of CONCATAll( range, delimiter) where, in your case Range = X3:AC3, and delimiter = ", "

Please Login or Register  to view this content.

[ChemistB]

Request 3: Assuming you are putting "X" in Column R? You would use a SUMIF Function

=SUMIF(Refs!$R$1:$R$86, "X", Refs!$T$1:$T$86)

Request 4: No need for transpose. That would be a simple INDEX with ROWS
in REFS!S1 copied down
=INDEX('Player Data'!$CE$3:$FI$3, ROWS($S$1:$S1))
Do these work for you?

[graeme27uk]

OK. Thanks for the assistance as always.

The UDF's seem to work. However, they don't seem to stay. This then affects the concatenation as I end up with ,,,, for example.

The one in PLAYER DATA column K (K4) does not stay. It reverts to just , and I have to click enter in the cell for it to run. Can this be fixed?

I don't think I explained what I wanted very well in terms of REQUEST #3.

Each skill is worth a certain number of points. This is in the REFS sheet columns S:T. On the PLAYER DATA sheet there are the skills CE:FI. If a player has that skill then I have put an "x" in that column to indicate that they have that skill.

What I then need it to do is then total up the points total of skills that player has. This needs to go into column FO on the PLAYER DATA sheet.

[ChemistB]

Okay, just need some questions answered.
So, in reality, there will be a player name in Column A of "Player Data?"
And skill costs are the same no matter what the profession is? (You should try the TableTop RPG Rolemaster, skill costs and bonuses vary by profession).
Where do you want this Sum of all points used to develop skills?

Since you are using INDEX to match up the order that the skills appear on Refs and Player Data, the formula should not be difficult once I have those answers.

[ChemistB]

Not certain why GetStr is wigging out on you. Try changing this in the code

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

[graeme27uk]

And skill costs are the same no matter what the profession is? (You should try the TableTop RPG Rolemaster, skill costs and bonuses vary by profession).

Skill costs are the same.

There are some minor variability...

Leap and Pass skill costs 2 points if AG is 3 or less; 3 points if AG is 4 or more.

If a player has Block and Dodge then they are an extra point.

http://www.midgardbb.com/NewBBTeamBuildingGuide.html (about half way down are all the various variables for calculating player cost).


So, in reality, there will be a player name in Column A of "Player Data?"

Column A of PLAYER DATA sheet indicates whether they are a normal player or a Star Player.
Column B of PLAYER DATA sheet indicates the specific player type.

Where do you want this Sum of all points used to develop skills?

This can go into column FO on the PLAYER DATA sheet. I will just rename the column.



Also, I notice that the format of the text changes with the UDF Concatall Is that correct? Can I change the format of the text and use shading as normal, etc?

[graeme27uk]

That change to the GetSTR seems to have worked.

I also notice that with the Concatall UDF, it does not put a ", " in at the start.

On the TR 1 Sheet, in column I it should have all the skills that a player starts with AND then has acquired. So in the example I have put in a Human Thrower starts with Sure Hands and Pass skill. This comes from looking up the Human Catcher from the PLAYER DATA sheet and column K. Then, it should concatenate (with ", ") those starting skills and the ones earned as shown in TR 1 X:AC.

So what it should do is look up the skills that players start with

[ChemistB]

I also notice that with the Concatall UDF, it does not put a ", " in at the start.

Why would you want a comma at the start?

[ChemistB]

Okay, about that comma, formula should end in
IF(COUNTA($X3:$AC3)>0,", ","")),ConcatAll($X3:$AC3,", ")))
Note: originally it was COUNTA(Z3:AC3)

[graeme27uk]

OK.

However, do I need to use another IF function in there so that if there are no skills for the player at the start then there is no comma at the start, if there is then the gained ones are additional.

So if you change the position to Human Lineman then put a gained skill in, it comes up with a comma then the skill.

[ChemistB]

To get a sumif when one set is horizontal and the other vertical, we need to resort to MMULT
in Player Data FO4 copied down

=MMULT(--($CE$4:$FI$4="x"), REFS!$T$1:$T$83)

I am at a loss on how to incorporate your modifiers into that.

[graeme27uk]

Well I can get it working with this if I were to use it, it's just it would be a HUGELY long formula. My experience is that HUGELY long formula can generally be shortened by more adept people.

In the exemplar attached, a, b, c, d, etc represent the various skill names. Mr X is the player. Mr X has skills a, c, d and f. Using the lookup table this gives a total of 14 points spent on skills.

[graeme27uk]

Still don't get it.... the =GetSTR still wigs out...


sometimes it comes up with the #VALUE error now. I click on the cell and press enter and then it all works again.

[ChemistB]

Looking at your post #22, I do not see anywhere in there where you are modifying skill costs based on their physical characteristics (Ag, St, etc.) If it's a straight vlookup, then you can use the MMULT formula I proposed.

[graeme27uk]

No, in the example I have given it's simply as you say. But the formula will need to take into account if a player has AG 4 or more then Leap and Pass are 3 points instead of 2... and one or two other little niggles.

[graeme27uk]

I put the MMULTI formula in PLAYER DATA column FO. I have made some adjustments but it does not work.

[ChemistB]

You only needed the MMULT because you were looking at a horizontal array on Player Data and a vertical array on REFS. Now that you moved the vertical array on refs to a horizontal array on Player Data (CE4:FI4) you should use SUMPRODUCT instead.

FO7 copied down
=SUMPRODUCT(--($CE7:$FI7="x"), $CE$4:$FI$4)

[graeme27uk]

Ok, that is now fixed.

[graeme27uk]

I seem to have broken the formula on PLAYER DATA column I

[ChemistB]

You must have removed a column in Stats. You need to pull 9 not 10 now. Modify accordingly.

[graeme27uk]

What do you mean?

[graeme27uk]

If you look at PLAYER DATA column I then the Human Lineman has gained 6 skills. He starts with no skills.

So what should happen is that it should concatenate the skills together with a ", " deliminator, No ", " at the start.

[ChemistB]

Change the part
IF(VLOOKUP(C4,stats,9,FALSE)=0
to
IF(VLOOKUP(C4,stats,9,FALSE)=""

[graeme27uk]

Any luck with the other bits?

[ChemistB]

Most of what I read in the rules was limitations on the team, not the individual players. I think you can set up conditional formatting so that, for example,
Total player cost doesn't exceed 130 or
A team with ST 4+ may not have any AG 4+ players.