multiple items in database
 

I have a database in excel where there are duplicated text items in col A.
In Col B are text items (names). I want to create a "table" that in Col A of
the table will have only one accurance of the items in Database col A, and in
the adjacent column (or columns) will have the items from the Database Col B.
I've tried pivot tables but I couldn't find a way to have text displayed in
the data section. I've tried functions but couldn't find something that
worked with multiple occurances.

Database: Table:
col a col b col a col b (and/or
Col c, d etc)
blue Joe Blue Joe
green Ed Green Ed Bob
green BoB Orange Ralph
orange Ralph Red Ed Mary Sam
red Ed
red Mary
red Sam

Hi!

Use DataAdvanced Filter to copy the unique values from Database column A to
another location.

I'll assume you do that and the new location is Sheet2 A1:An. A1 being the
header.

This requires that there are no empty rows within your Database range of
interest.

In Sheet2 B2 enter this formula with the key combo of CTRL,SHIFT,ENTER:

=INDEX(Sheet1!$B$2:$B$8,SMALL(IF(Sheet1!$A$2:$A$8= $A2,ROW(INDIRECT("1:"&COUNTA(Sheet1!$A:$A)))),COLU MN(A:A)))

Copy across to enough cells that will be needed to return all possible
matches. How many will that be? 5? 15?

Then copy down as far as the list of data extends in column A.

Cells with no matches will return a #NUM! error. The easiest way to "hide"
those error returns is to use conditional formatting.

Select the entire range of formulas.
Goto FormatConditionalFormatting
Formula is: =ISERROR(B2)
Set the font color to be the same as the background color
OK out.

Biff

"Peter" wrote in message
...
I have a database in excel where there are duplicated text items in col A.
In Col B are text items (names). I want to create a "table" that in Col A
of
the table will have only one accurance of the items in Database col A, and
in
the adjacent column (or columns) will have the items from the Database Col
B.
I've tried pivot tables but I couldn't find a way to have text displayed
in
the data section. I've tried functions but couldn't find something that
worked with multiple occurances.

Database: Table:
col a col b col a col b
(and/or
Col c, d etc)
blue Joe Blue Joe
green Ed Green Ed Bob
green BoB Orange Ralph
orange Ralph Red Ed Mary Sam
red Ed
red Mary
red Sam

When I did the CTRL,SHIFT, ENTER I got brackets {} around the formula but the
result was #N/A.

"Biff" wrote:

Hi!

Use DataAdvanced Filter to copy the unique values from Database column A to
another location.

I'll assume you do that and the new location is Sheet2 A1:An. A1 being the
header.

This requires that there are no empty rows within your Database range of
interest.

In Sheet2 B2 enter this formula with the key combo of CTRL,SHIFT,ENTER:

=INDEX(Sheet1!$B$2:$B$8,SMALL(IF(Sheet1!$A$2:$A$8= $A2,ROW(INDIRECT("1:"&COUNTA(Sheet1!$A:$A)))),COLU MN(A:A)))

Copy across to enough cells that will be needed to return all possible
matches. How many will that be? 5? 15?

Then copy down as far as the list of data extends in column A.

Cells with no matches will return a #NUM! error. The easiest way to "hide"
those error returns is to use conditional formatting.

Select the entire range of formulas.
Goto FormatConditionalFormatting
Formula is: =ISERROR(B2)
Set the font color to be the same as the background color
OK out.

Biff

"Peter" wrote in message
...
I have a database in excel where there are duplicated text items in col A.
In Col B are text items (names). I want to create a "table" that in Col A
of
the table will have only one accurance of the items in Database col A, and
in
the adjacent column (or columns) will have the items from the Database Col
B.
I've tried pivot tables but I couldn't find a way to have text displayed
in
the data section. I've tried functions but couldn't find something that
worked with multiple occurances.

Database: Table:
col a col b col a col b
(and/or
Col c, d etc)
blue Joe Blue Joe
green Ed Green Ed Bob
green BoB Orange Ralph
orange Ralph Red Ed Mary Sam
red Ed
red Mary
red Sam