sorting data by a certain attribute
 

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


--
ebt
------------------------------------------------------------------------
ebt's Profile: http://www.excelforum.com/member.php...fo&userid=5575
View this thread: http://www.excelforum.com/showthread...hreadid=528832

sorting data by a certain attribute
 

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 he

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



ebt wrote:
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
Contextures
http://www.contextures.com/tiptech.html

sorting data by a certain attribute
 

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.