Macro to distribute data from one column into different columns alphabetically

[terryhenderson]

I have a column of data (names of products) of nearly one million cells. What I'm after is a macro that sorts data in that column alphabetically a-z, 0-9 and then distributes each letter's chunk of data to its own column yielding new 26 columns, plus only one column for data where product name starts with a number from 0-9.

Can any one kindly help me with this request?
Thanks in advance.

[terryhenderson]

Anybody interested in helping with my request?

[arlu1201]

Do you have a sample file that you can upload?

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

[terryhenderson]

Thank you very much arlu1201 for your sincere initiative to help me. I have attached a sample product file for your consideration.

I may also say that if Sheet2 has already existing data previously distributed, can we add the newly sorted and distributed data to the bottom of the old existing data with the same macro?

Much obliged for your assistance.

[arlu1201]

So if there is no data for a particular alphabet, the column should be blank to retain the series?

In that case, is it fine if there are alphabets in the 1st row of Sheet2? This will assist in simplifying the macro.

[terryhenderson]

Thank you very much for your reply and time& effort spent in understanding this issue.

As a matter of fact, in my real workbook Sheet2 we are using the columns letters to "manually" distribute the sorted data from Sheet1. I think it is entirely returning to your opinion whether to use columns letters or place alphabets on the first row of Sheet2 instead.

I just wanted to highlight that real Sheet2 has existing data, and we'd like this macro (to be) to sort data in Sheet1 a-z0-9, and then distribute each letter's chunk of data to the bottom of each designated column of the letter in Sheet2. We will follow your way if you decided to consider the first row alphabets as columns headers in Sheet2.

I hope I could make it much clearer.

Many thanks..

[terryhenderson]

I was wondering if the following sub tmp() could help selecting sorted data by letter:

Please Login or Register  to view this content.

Appreciating your precious assistance..

[arlu1201]

Is it ok if the source data is sorted before use?

[terryhenderson]

For sure. Actually should be sorted (by the macro) before being distributed (moved) to Sheet2.

[arlu1201]

Try this code - i didnt need to sort the data, just copied it to its respective column -

Please Login or Register  to view this content.

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose View | Macros
Select a macro in the list, and click the Run button

[terryhenderson]

I don't know what to say arlu1201 as thanking will not be enough for this masterpiece you have produced. You are a real genius with a talent that is really rare to find. I still have to say "thank you very much" for your sincere effort and time that you utilized to bring something that was only in my vision to reality.

Wish you all the best.

[arlu1201]

Thank you. Am glad its solved.

[snb]

or

Please Login or Register  to view this content.