How to put this data sorted by character in each cell

  • Thread starter Thread starter Laura
  • Start date Start date
L

Laura

Hi!

I don´t know if this can be done or not. I have this data in column A:

Anne
Ashton
Angel
Blister
Bolton
Bright
Cotton
Cash

I need that data to be put on the column that starts by the first
character of each cell and have to be sorted alphabetically. Like this

Column A Column B Column C

Angel Blister Cash
Anne Bolton Cotton
Ashton Bright

I don´t know if this is possibly with formulas and functions

Thank you so much!!
 
I've assumed that your data is in A1 to A8 of Sheet1, and that you
want the sorted output to appear in the correct columns in Sheet2.
First of all, put these formulae in the cells stated in Sheet1:

B1: =COUNTIF(A$1:A$8,">"&A1)+COUNTIF(A$1:A1,A1)
C1: =INDEX(A$1:A$8,MATCH(9-ROW(A1),B$1:B$8,0))
D1: =LEFT(C1)&"_"&COUNTIF(C$1:C1,LEFT(C1)&"*")

and copy these down to row 8. The sheet will now look like this:

Anne 7 Angel A_1
Ashton 6 Anne A_2
Angel 8 Ashton A_3
Blister 5 Blister B_1
Bolton 4 Bolton B_2
Bright 3 Bright B_3
Cotton 1 Cash C_1
Cash 2 Cotton C_2

(the columns might not be correctly aligned). The formula in column B
sets up a ranking of the eight names. Column C then picks the names in
the correct order, and column D gives a sequential number for each
letter, in readiness for the output in Sheet 2.

Put this formula in A1 of Sheet2:

=IF(ISNA(MATCH(CHAR(COLUMN(A1)+64)&"_"&ROW(A1),Sheet1!$D$1:$D
$8,0)),"",INDEX(Sheet1!$C$1:$C
$8,MATCH(CHAR(COLUMN(A1)+64)&"_"&ROW(A1),Sheet1!$D$1:$D$8,0)))

and then just copy it across and down as far as you need. This is what
you get with your sample data (copied to column E and down to row 8):

Angel Blister Cash
Anne Bolton Cotton
Ashton Bright

If you have more data then you will need to adjust all the ranges
which refer to A1:A8, B1:B8, C1:C8 and D1:D8 as appropriate, but you
should retain the $ symbols where they are, and you will also have to
adjust the 9 in the formula in C1 - this is one more than the data
range.

Hope this helps.

Pete
 
Back
Top