can I sort text data into bins?

[fern]

Hi,

Here's my problem - I hope you can understand it & help:

Let's say I've got a column, A (or row 1, it doesn't matter) tha
contains cells, each with a single word/category that I'm interested i
[example: dog, cat, bird, fish, gerbil] - we can assume that this is m
list of category headers/titles.
Plus I've also got various other columns, A-D (or rows) next to m
column A titles that each contain a list of items, one per cell: [eg
B1=dog + B2=cat, C1=bird + C2=cat + C3=gerbil].

Perhaps it would be clearer if I drew it out (ignore the dashes
they're just there to keep my columns straight):
A(titles)------- B ---------- C ----------- D
dog------------fish---------bird--------- fish
cat ----------- dog --------gerbil--------cat
bird--------------------------cat--------------
fish--------------------------------------------
gerbil------------------------------------------

Now, rather than just sorting columns A-D alphabetically to get:
A---------------B----------- C----------- D
bird ----------dog---------bird----------cat
cat------------fish----------cat---------- fish
dog------------------------ gerbil-----------
fish-------------------------------------------
gerbil-----------------------------------------

...I actually want to sort B-D according to the original order of A s
that missing or empty (horizontal) categories are actually left blank
rather than just collapsed into the smallest vertical list possible:
A----------------B----------C------------D
dog------------dog-----------------------
cat -------------------------cat---------cat
bird ------------------------bird----------
fish ------------fish---------------------fish
gerbil----------------------gerbil---------

Do you see what I mean? It's rather like sorting data into appropriat
bins before making a frequency histogram chart, but here I want to sor
text into categories not numbers into bins, so . And nothing I've trie
so far seems to work, so I'd really appreciate any suggestions you ca
give.

Thanks so much, F


:confused

 

[Frank Kabel]

Hi
see Dave's answer in public.excel

P.S.: please don't multipost

 

[Dick Kusleika]

F

There's no way to do that that's built-in, as far as I know. You could use
a macro to do it. Here's what I came up with. Select your range and run
this macro.

Sub SortBuckets()

Dim aAllVals() As Variant
Dim cell As Range
Dim i As Long, j As Long
Dim vTemp As Variant, vTemp2 As Variant
Dim lCurrRow As Long

'Make array big enough to hold all the cells
'second dimension is the column
ReDim aAllVals(1 To Selection.Cells.Count, 1 To 2)

'fill the array
For Each cell In Selection.Cells
i = i + 1
aAllVals(i, 1) = cell.Value
aAllVals(i, 2) = cell.Column
Next cell

Selection.ClearContents

'Sort the array based on the value
For i = LBound(aAllVals, 1) To UBound(aAllVals, 1) - 1
For j = i + 1 To UBound(aAllVals, 1)
If aAllVals(i, 1) > aAllVals(j, 1) Then
vTemp = aAllVals(i, 1)
vTemp2 = aAllVals(i, 2)
aAllVals(i, 1) = aAllVals(j, 1)
aAllVals(i, 2) = aAllVals(j, 2)
aAllVals(j, 1) = vTemp
aAllVals(j, 2) = vTemp2
End If
Next j
Next i

'Loop through the array
For i = LBound(aAllVals, 1) To UBound(aAllVals, 1)
'Skip blanks
If Len(aAllVals(i, 1)) > 0 Then
'If the first time through
If i = LBound(aAllVals, 1) Then
lCurrRow = 1
'If not the same value, increase the row
ElseIf aAllVals(i, 1) <> aAllVals(i - 1, 1) Then
lCurrRow = lCurrRow + 1
End If
'If it is the same value the row stays the same

'Write the value to its original column
Cells(lCurrRow, aAllVals(i, 2)).Value = aAllVals(i, 1)
End If
Next i

End Sub

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

Hi,

Here's my problem - I hope you can understand it & help:

Let's say I've got a column, A (or row 1, it doesn't matter) that
contains cells, each with a single word/category that I'm interested in
[example: dog, cat, bird, fish, gerbil] - we can assume that this is my
list of category headers/titles.
Plus I've also got various other columns, A-D (or rows) next to my
column A titles that each contain a list of items, one per cell: [eg:
B1=dog + B2=cat, C1=bird + C2=cat + C3=gerbil].

Perhaps it would be clearer if I drew it out (ignore the dashes,
they're just there to keep my columns straight):
A(titles)------- B ---------- C ----------- D
dog------------fish---------bird--------- fish
cat ----------- dog --------gerbil--------cat
bird--------------------------cat--------------
fish--------------------------------------------
gerbil------------------------------------------

Now, rather than just sorting columns A-D alphabetically to get:
A---------------B----------- C----------- D
bird ----------dog---------bird----------cat
cat------------fish----------cat---------- fish
dog------------------------ gerbil-----------
fish-------------------------------------------
gerbil-----------------------------------------

..I actually want to sort B-D according to the original order of A so
that missing or empty (horizontal) categories are actually left blank,
rather than just collapsed into the smallest vertical list possible:
A----------------B----------C------------D
dog------------dog-----------------------
cat -------------------------cat---------cat
bird ------------------------bird----------
fish ------------fish---------------------fish
gerbil----------------------gerbil---------

Do you see what I mean? It's rather like sorting data into appropriate
bins before making a frequency histogram chart, but here I want to sort
text into categories not numbers into bins, so . And nothing I've tried
so far seems to work, so I'd really appreciate any suggestions you can
give.

Thanks so much, F

 

[AlfD]

Hi!

Need more information. eg can the same item occur more than once in
given column (and, if so, how do you plan to deal with it).

Meanwhile, the following sub does a binning job on 4 columns(j: firs
one defines the bins) and 5 rows (i for the row no. of each non-bi
item: k for the row no. of a bin item.

It collects the bits and pieces 4 columns down the line.

Any use?

Al

 

[Dick Kusleika]

Hi
see Dave's answer in public.excel

P.S.: please don't multipost

I'd just like to say #@&%!*

 

[Frank Kabel]

Hi Dick
I was also in the middle of creating a macro then I saw his other
post........