Creating a list from random cells

[mikewalker]

I want to be able to create a series of lists from a range of cells in excel. For example, the cells in rows 1 to 6, colums A to F may each contain a different surname. I want then to produce a list with all names beginning with 'A', another list with those beginning 'B' and another with those starting 'C'. These lists could be further down in the same sheet or another sheet of the same workbook.

Thanks for any ideas

Mike

 

[btadams]

The simple non-macro approach would be to combine columns A - F into a single column and then sort it. Are you looking to automate this?

P.S. Welcome to the board!

 

[mikewalker]

Yes, I need to automate it, so that a simple push button will run the appropriate macro.

Thanks,

Mike

 

[tusharm]

Turn on the macro recorder (Tools | Macro > Record new macro...), do whatever it is you want to do by hand, turn off the recorder, and switch to the VBE. In most cases, XL will give you the necessary code, which you can then customize further.

 

[mikewalker]

Thanks, but I don't think that will work. There will be a different number of As, Bs, Cs each time the macro is run, and the challenge is to add each successive item to the bottom of the existing list, however long it is already.

Mike

 

[btadams]

If it will always be the same range (A1:F6), then Tushar is correct in that it can be accomplished using the macro recorder.

 

[mikewalker]

Sorry, I still can't see how. Although the range will always be the same, the names will be different on each occasion. The macro will have to search the range until it finds a name beginning with 'A' and put it in the 'A' list; similarly for B, C, etc. Using the macro recorder, how can it search the range until it finds an 'A', without using some formulae?

Thanks,

Mike