Arrange Data

Hi RemyD, and welcome to the forum.

If you put this formula in cell I2:

=IF(ISNA(INDEX($Y2:$AV2,MATCH("*"&I$1&"*",$Z2:$AW2,0))),"",INDEX($Y2:$AV2,MATCH("*"&I$1&"*",$Z2:$AW2,0)))

and then fill that right to cell X2, and then down to row 1220, you should get the results you're after. I hope.

Make SURE your column headers in columns I:X are what you would find in the data from Y:AW. For example, the header in column S is "Hit roll", but in your data range only "hit" appears in cells. My formula won't find that as a match since it's looking for a match to *hit roll* not just *hit*. (Column T is the same, change "damage roll" to "damage" and it will return the correct results. There may be other column headers that need adjusting, too.)

Once you're sure the numbers have calculated correctly, you can't just delete columns Y:AW. You could hide those columns, or you could copy I2:X1220 and use PasteSpecial -> Values on top of itself to convert the formulas to static numbers. You'd lose all your formulas, obviously, but it would allow you to delete Y:AW.

I'll give it a shot...

You'll notice that the basic format is just an IF statement, and the test is to see if my formula (in red) returns the N/A error using the ISNA() function (in green). If so, it returns a blank, "".

=IF(ISNA(INDEX($Y2:$AV2,MATCH("*"&I$1&"*",$Z2:$AW2,0))),"",INDEX($Y2:$AV2,MATCH("*"&I$1&"*",$Z2:$AW2 ,0)))

The main formula, INDEX($Y2:$AV2,MATCH("*"&I$1&"*",$Z2:$AW2 ,0) is where the work is done. Normally an INDEX/MATCH function is used for matching a value and then returning an associated value from another column in the same row (or another row in the same column).

In this case our two ranges overlap, being offset by 1 column. The MATCH values (what you're looking for) start in column Z, while the INDEX values (the values you want to return) start in column Y. So we're matching a value (the headers in columns I through X), using wildcards (asterisks), with the cell values in columns Z through AW. If a match is found, the INDEX portion then returns the value of the cell next to it due to the one column offset in the formula.

It's a bit difficult to explain, but hopefully that gives you a good idea of what I was trying to accomplish.

Note that in Excel 2007, there is a new IFERROR formula, so you could even shorten this to:

=IFERROR(INDEX($Y2:$AV2,MATCH("*"&I$1&"*",$Z2:$AW2,0)),"")