Wildcard operator for numbers?

Hello kdisley,

Welcome to the Forum.

Your workbook contains no VBA to see what your Code looks like, so I was wondering if the attached sample workbook will work for you?

Also see the "Please consider" note at the bottom of this post.

Use the UDF here:

http://www.excelforum.com/showthread...=1#post3096647

Then, enter this array formula**:

=concatall(IF(B2:B53>0,A2:A53,""),", ")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Sorry, I think I've possibly confused everyone with my previous ham-fisted explanation...! After much trial-and-error (I've never used VB at all before, except for that one cut-and-paste for the UDF I'm using which explained in idiot language how to get it running, so I'm still trying to figure it all out), I've created a better example that should illustrate more clearly what I'm on about: Wildcard Needed (Better Explanation).xlsm

The second sheet shows a working instance of LOOKUP_CONCAT so that you can see that searching for "Y" works as the first value (in this case, the context is "has this character met this particular NPC in the course of a game?"), and on the first sheet I've added a text representation of the results I'm trying to get to in red, highlighted in yellow. I've also managed to upload a VB-enabled worksheet this time, so the formula actually works!

Like I say, the problem I've got here is finding a substitute for "Y" so that it'll return a concatenated entry of what each player has spent their experience points on (regardless of what the actual number value of points spent is), so that I'll be able to see at-a-glance what each player has bought in a single cell and re-display that cell in the players' individual reference sheets with a simple "show cell contents". That way I can have a growing list as they buy more stuff just by entering it into the table and having it update itself as we progress (as with the NPC sheet I used as an example, which gets copied to each player's sheet individually).

The reason for the number totals at the bottom, by the way, is to again be copied to the players' sheets to be subtracted from their total points earned so that I can also keep a track of how many they have left (hence the values have to stay as numbers). I actually have about 14 separate sheets in all, some collating and some displaying various data about the game's progress, the idea being I can keep a few sheets open with sensitive information for entries then hide them to show the stats I want the players to see at the end of a session.

It looks to me like I need a numerical wildcard or a >0 to return a TRUE response for any non-blank cell in the column regardless of the actual number value, but Excel just doesn't seem to want to accept anything but an exact number as the logical argument... it seems like I'm missing some fundamental logic here and I'm sure it's staring me in the face, but I'm stumped.

EDIT: Also, the way that LOOKUP_CONCAT was constructed puts the comma at the start rather than the end of each result, resulting in the weird output... if anyone knows how to swap that around so that it's at the front (so that Player 1's entry would be "Potence 1, Potence 2, ") then that would be a fantastic bonus, but is by no means essential.

I kind of got lost in your explanation...

It may be that your UDF doesn't accept that type of argument. I didn't download your updated file to look at the code. I'm not much of a programmer.

The link I posted has a very good concatenation UDF. I've tried a few over the years and I think the one I linked to is the easiest to implement.

Try this UDF

Formula:

Please Login or Register  to view this content.

I have put two examples of how it can be used in your workbook

In J34, and then manually changed to suit each player

Formula:

Please Login or Register  to view this content.

In K34, drag/fill down

Formula:

Please Login or Register  to view this content.

Don't use merged cells, they are a pain in the ***, and will give you nothing but grief ...