Excel 97 - want to automatically move data

[slurpee55]

Hi all,

I have a monthly report in which the column BF has any combination of the numbers 1-5, either singly or in combinations, and separated by commas if there is more than one number in a cell. A cell may be blank, also.
What I need is to create 5 columns BQ through BU where all the 1s go into BQ, all the 2s go into BR, all the 3s go into BS, all the 4s go into BT and all the 5s go into BU. I would also like to label these Q2 - 1, Q2 - 2, Q2 - 3, Q2 - 4 and Q2 - 5.
There is no guarantee that any specific number will appear in any cell or even at all (5 is by far the least frequent response).
I can't figure a macro or formula that will do this - can VBA?
Attached is a file showing what the data would look like and how I would like the data broken out (except I have all of it in columns A-F).
P.S. Thanks, Dreamboat

[mdmackillop]

Hi,
Welcome to VBAX.
Put the following formula into cell B2 in your example; fill acrosss and down.

=IF(ISERR(FIND(RIGHT(B$1,1),$A2)),"",RIGHT(B$1,1))

for your particular case this would be

=IF(ISERR(FIND(RIGHT(BQ$1,1),$BF2)),"",RIGHT(BQ$1,1))

Having set up and created the headers and formulae once, I would save this arrangement in a blank sheet which can be copied and pasted (PasteSpecial/Formula) into any required sheet.
If you would rather have some code to accomplish this, let us know.
MD

[slurpee55]

Many thanks!
And on another note, does anyone know why I can't use the Quick Reply using Mozilla?

[mdmackillop]

Try asking in the Site and Related Forum