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) 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

 

[Dave Peterson]

I think I'd use a helper worksheet. I'm gonna assume that your data is on
Sheet1.

Put this in A1 and copy down until you're out of data:
=Sheet1!A1

Then select your range (B15) and type this:
=IF(COUNTIF(Sheet1!B$1:B$5,$A1)>0,$A1,"")
(adjust the rows/columns accordingly)

But hit ctrl-enter instead of enter and all the cells will be filled with that
type of formula.

Convert it to values and do your sort on column A (if you need to).

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

 

[fern]

Thanks so much. it took some fiddling so that the formulas fit m
dataset, but I think I've finally got it working right. Very exciting
Thanks for stopping this day from being a total waste.
Cheers,