(e-mail address removed) wrote...
...
I have a data in a column as :
Dina
Special
Mary
Jupiter
Saturn
Neptune
Note the actual spaces: none before Dina, 3 before Special, 6 before
Mary, 2 before Jupiter, 8 before Saturn, and 2 before Neptune.
I'll refer to the range containing your data using the defined name
data. I'll also define another name, data.key, referring to the
formula
=FIND(LEFT(TRIM(data),1),data)
I want to put if the leading spaces is least then put in next column,
if more than previous one then , to the next column so that end result
look like this
Dina
Neptune
Jupiter
Special
Mary
Saturn
Jupiter appears before Neptune in your sample data. If this is really
the output order you want, then there's no discernable rule, so manual
rearrangement is the only option. I'll assume you actually want to
maintain original entry order, so Jupiter before Neptune.
Given the actual spaces in your data, Dina would appear in the 1st
column, Jupiter and Neptune in the 3rd column, Special in the 4th
column, Mary in the 7th column and Saturn in the 9th column. That can
be produced using formulas. If the output range began in cell D1,
[these are ALL array formulas]
D1:
=IF(SUMPRODUCT(--(data.key=COLUMNS($D1
1))),
INDEX(data,MATCH(1,--(data.key=COLUMNS($D1
1)),0)),"")
E1:
=IF(COUNTIF($D1
1,"?*"),"",IF(SUMPRODUCT(--
(data.key=COLUMNS($D1:E1))),
TRIM(INDEX(data,MATCH(COLUMNS($D1:E1),data.key,0))),""))
D2:
=IF(SUMPRODUCT(--(data.key=COLUMNS($D2
2)))<=COUNTIF(D$1
1,"?*"),"",
INDEX(data,MATCH(1,(COUNTIF(D$1
1,data)=0)*(data.key=COLUMNS($D2
2)),
0)))
E2:
=IF(COUNTIF($D2
2,"?*"),"",
IF(SUMPRODUCT(--(data.key=COLUMNS($D2:E2)))=COUNTIF(E$1:E1,"?*"),"",
TRIM(INDEX(data,MATCH(1,
(COUNTIF(E$1:E1,MID(data,data.key,255))=0)*(data.key=COLUMNS($D2:E2)),
0)))))
Fill E1:E2 right into F1:L2, then fill D2:L2 down into D3:L6.
These formulas are rather inefficient. If your data doesn't change
much over time, it'd be easier to use a column to the left of data.
Select the rows in that column corresponding to your data, so if data
were in X99:X104, select W99:W104, then enter the array formula
=data.key
Then copy W99:W104 and paste special as values. Select W99:X104 (both
columns) and run Data > Sort, sorting on column W in ascending order.
Clear W99:W104. Select X99:X104 and run Data > Text to Columns. Choose
Delimited in step 1 of the Convert Text to Columns Wizard and click
Next. Check the box to the left of Space in the Delimiters section and
UNCHECK the box to the left of Treat consecutive delimiters as one,
and click Finish.
