Sorting within a cell

[Megan]

I have a spreadsheet where there is a long list of part numbers that
constantly change, get added to, deleted from, etc. and all need to be in one
cell. The problem I have is that the list gets very long and needs to almost
be treated like the COLUMN feature in Word where you can take a long list
and make it read like 3 newspaper columns. Otherwise, each row is way way
too long and the data is on multiple pages when it actually needs to be on
one page and taking up minimal area. Is there any way to do that in one cell
in Excel?

Second part related to the first part above is that after items are
added/deleted to this long list, the remaining info needs to be sorted and
read correctly. This is a weekly report that i will be updating constantly
and not sure how to best approach it - or if there is even a way to do what i
am asking.

The ultimate goal is to have one cell with multiple pieces of data that read
like newspaper columns that I can sort from a to z and always have it fit in
the smallest space in one cell and stioll be readable.

 

[Sheeloo]

MS Word is more suited for this requirement... Excel is designed to process
tabular data where each cell has an item.

 

[Megan]

I agree - i was considering converting it to Word and just going from there.
Will be a serious growing pain but makes sense.

Do you know the best way to convert it over to Word and keep all of the
columns/rows separated and content intact?

I am sure I will need to change fonts, etc. but I would hate to have to
re-create the whole thing.

Thanx so much for your prompt response!!!

 

[Sheeloo]

You can just copy the cells from Excel and paste into a Word document... Word
will create a table with the required number of rows and columns.

 

[Chip Pearson]

I don't think you need to go to Word to do this. You can use the code
below. Change the lines marked with <<<< to your appropriate values.
Separator is the character that separates part numbers in the original
cell. ListCell is the cell that has the list of part numbers.
NumColumns is the number of columns into which you want to split the
part numbers. Destination is the first cell where the part numbers are
written to.


Sub AAA()
Dim ListCell As Range
Dim Count As Long
Dim Destination As Range
Dim V As Variant
Dim N As Long
Dim M As Long
Dim Separator As String
Dim NumColumns As Long
Dim FirstRow As Long

Separator = ";" '<<<< CHANGE
Set ListCell = Range("A1") '<<<< CHANGE
NumColumns = 3 '<<<< CHANGE
Set Destination = Range("G1") '<<< CHANGE
FirstRow = Destination.Row

V = Split(ListCell, Separator)
Count = (UBound(V) - LBound(V) + 1) / NumColumns
For N = LBound(V) To UBound(V)
Destination.Value = V(N)
M = M + 1
Set Destination = Destination(2, 1)
If M > Count Then
Set Destination = _
Destination(1, 2).EntireColumn.Cells(FirstRow, 1)
M = 0
End If
Next N

End Sub


This writes the part numbers in their original order. If you want to
sort the part numbers before writing them out to the columns, enter
the following line of code immediately AFTER the V = Split(ListCell,
Separator) line of code:

QSortInPlace V, -1, -1, False, vbTextCompare

The QSortInPlace procedure is available at
http://www.cpearson.com/excel/SortingArrays.aspx. Download the zip
file to some folder (it doesn't matter where) and the unzip the file.
Then in VBA, go to the File menu, choose Import File, and navigate to
where you unzipped the file, and open modQSortInPlace. This will
insert a module into your project that contains the sorting
procedures.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Sheeloo]

Hello Chip,

Megan wanted to have the list of items in one cell in Excel and sort within
the cell. As you know almost anything can be done using VBA (your output in
Col G can be combined back to the original cell) but given the basic
requirement (have a list of part nos which can be printed in newspaper like
columns) I thought that Word would be the best option.

Megan can also have list in different cells in one Col, hide the gridlines
at the time of printing... and distribute them in multiple columns at the
time of printing...

Regards,
Sheeloo