sorting data by a certain attribute

[ebt]

I am having trouble coming up with a way to do this, any help would be
greatly appreciated:

I have a list of 500 names in column A, in column B I have a series of
attributes (text values, which are separated by commas). In column C
I'd like to take the dozen or so individuals with a specific attribute
listed in column B and list them from C2:C13 (I am only finding
functions that would list these names in the same row they appear in
column A).


Example of how I want this to look (actual doc includes a column for
each attribute):

Names Attributes MI RI
Banks MI, RI, OF Banks Banks
Davis C Smith Jones
Jones RI Walsh Smith
Smith MI, RI, C
Thomas C
Walsh MI

 

[Debra Dalgleish]

You could use an Advanced Filter to extract names with a specific
attribute, and update the lists by running the Advanced Filter again,
manually or programmatically.

Another approach would be to list one attribute per row, e.g.:
Banks MI
Banks RI
Banks OF

Then, create a pivot table with Attribute and Name in the row area, and
count of Name in the data area. There's information on pivot tables in
Excel's Help, and here:

http://www.contextures.com/xlPivot01.html

 

[Herbert Seidenberg]

Here is a solution with formulas, R1C1 Ref Style and Advanced Filter.
Select the attributes and do
Data > Text to Column
Delimited
Check space, comma
Treat consecutive limiters as one
With headers and other addittions, your data might then look like this:
Cust
..
Banks MI RI OF
Davis Cn
Jones RI
Smith MI RI Cn
Thomas Cn MI
Walsh MI

Select the range that includes the header <Cust>,
a cell with a period (or a space) and 6 data cells and
Insert > Name > Create > Top Row
Select the 6 x 3 array of attributes and
Insert > Name > Define > Names in Workbook: array1
Also define these names:
rown Refers To: =ROW(INDEX(C1,1):INDEX(C1,ROWS(array1)))
coln Refers To: =COLUMN(INDEX(R1,1):INDEX(R1,COLUMNS(array1)))
natts Refers To: =ROW(INDEX(C1,1):INDEX(C1,COUNTA(array1)))
roco Refers To: =LARGE((10*rown+coln)*NOT(ISBLANK(array1)),natts)

To extract the unique attributes in array1, use this setup

Alist alist 1 2 3 4 mcnts
MI MI Walsh Thomas Smith Banks
MI Cn Thomas Smith Davis .
Cn RI Smith Jones Banks .
Cn OF Banks . . .
RI
MI
RI
Cn
OF
RI
MI

<Alist> consists of 11 entries ( 11=COUNTA(array1))
Select all 11 cells and enter this array formula with Cntrl+Shift+Enter
=INDEX(array1,LEFT(roco),RIGHT(roco))
Create a second <alist> of unique values with
Advanced Filter and name it <alist>.
Create the horizontal series from 1 to 4
(4=MAX(COUNTIF(array1,array1)))
and name it <mcnts>.
At the intersection of alist=MI and mcnts=1, enter this array formula
=INDEX(Cust,LARGE((array1=alist R)*rown,mcnts C)+1)
Copy this cell to the rest of the array.
Do not select the whole array or more than one cell when hitting
Cntrl+Shift+Enter.
If your list of <Cust> is in the double digits,
then the 10 in <roco> has to be increased to 1000 and
LEFT/RIGHT(roco) changed to LEFT/RIGHT(roco,2)
The array can be be rotated to appear as in your post with
Copy > Paste Special > Values, Transpose
Reversion to A1 Reference Style can be done now.