multiple items in database

Hi!

Use Data>Advanced 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)))),COLUMN(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 Format>Conditional>Formatting
Formula is: =ISERROR(B2)
Set the font color to be the same as the background color
OK out.

Biff

"Peter" <Peter@discussions.microsoft.com> wrote in message
news:8371FFF2-5994-4191-81B5-16D2DCF3AD20@microsoft.com...
>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 Data>Advanced 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)))),COLUMN(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 Format>Conditional>Formatting
> Formula is: =ISERROR(B2)
> Set the font color to be the same as the background color
> OK out.
>
> Biff
>
> "Peter" <Peter@discussions.microsoft.com> wrote in message
> news:8371FFF2-5994-4191-81B5-16D2DCF3AD20@microsoft.com...
> >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
> >
>
>
>

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 Data>Advanced 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)))),COLUMN(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 Format>Conditional>Formatting
Formula is: =ISERROR(B2)
Set the font color to be the same as the background color
OK out.

Biff

"Peter" <Peter@discussions.microsoft.com> wrote in message
news:8371FFF2-5994-4191-81B5-16D2DCF3AD20@microsoft.com...
>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 Data>Advanced 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)))),COLUMN(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 Format>Conditional>Formatting
> Formula is: =ISERROR(B2)
> Set the font color to be the same as the background color
> OK out.
>
> Biff
>
> "Peter" <Peter@discussions.microsoft.com> wrote in message
> news:8371FFF2-5994-4191-81B5-16D2DCF3AD20@microsoft.com...
> >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
> >
>
>
>