Sort words starts with first letter

Robert Mika

MrExcel MVP
Joined
Jun 29, 2009
Messages
7,256
I have a spreadsheet with about 6000 words in one column.
I would like to sort them in alphabetic order in another sheet:
First column in this worksheet (A) will host only words starts on A second column B will host only B words.,etc..
I can created formula which will do this by starting always from the first row in the Words spreadsheet but this always will leave empty cells:
Words in column B will start in row where A words have finished, C where B finished and so one.

Any help?
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
You don't specify if all 6000 words are unique - and if not - whether you want to return a unique listing or not ?

Formula wise a basic approach would be something like:

Excel Workbook
AB
1apple3
2banana4
3carrot8
4dog10
5aardvark1
6bell5
7bongo7
8bilbo6
9advert2
10elephant11
11cow9
Words


and with the key in place generating the output on sheet A is pretty straightforward, ie:

Excel Workbook
ABCDE
134211
2aardvarkbananacarrotdogelephant
3advertbellcow
4applebilbo
5bongo
6
A


obviously the formulae are to be extended across the requisite matrix
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
I should add:

- in the above I've assumed unique - if not you would need to adjust the formulae accordingly (ie simplest to adjust Words B and add further COUNTIF based on running count of given word)

- I also omitted the formulae in use in row 1 of sheet A, namely:

Code:
A1: =COUNTIF(Words!$A$1:$A$6000,CHAR(64+COLUMNS($A1:A1))&"*")
copied across
 

hdabbas

New Member
Joined
Apr 30, 2012
Messages
9
Greetings,

How the formulas would change if words are not unique?

Regards
 

Watch MrExcel Video

Forum statistics

Threads
1,122,803
Messages
5,598,131
Members
414,214
Latest member
marketingnumbersguy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top