Search multiple values to return single values

I have a worksheet that has multiple labor categories listed on different
lines. On another worksheet I want to pull each labor category only once and
put them each on a different line. Please see example below. Is there a way
to do this in excel and if so how?
Thanks,
Jana

WORKSHEET A
A
1 S1
2 T3
3 T4
4 S1
5 T3
6 S2

I want the formulas in worksheet B so they will deliver the following data -
don't need them in any certain order, just need each to only list once on a
different line.
WORKSHEET B
A
1 S1
2 S2
3 T3
4 T4

One way ..

In sheet: A
-------
In an empty col to the right, say col E,
Put in E1: =IF(A1="","",IF(COUNTIF($A$1:A1,A1)>1,"",ROW()))
Copy down to say, E100, to cover the max expected data in col A

In sheet: B
-------
Put in A1:
=IF(ISERROR(SMALL(A!E:E,ROWS($A$1:A1))),"",
INDEX(A!A:A,MATCH(SMALL(A!E:E,ROWS($A$1:A1)),A!E:E,0)))

Copy down to A100
(cover the same extent as done in col E in "A")

The above will return the required results from "A",
all neatly bunched at the top
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"JANA" <[email protected]> wrote in message
news:[email protected]...
> I have a worksheet that has multiple labor categories listed on different
> lines. On another worksheet I want to pull each labor category only once
and
> put them each on a different line. Please see example below. Is there a
way
> to do this in excel and if so how?
> Thanks,
> Jana
>
> WORKSHEET A
> A
> 1 S1
> 2 T3
> 3 T4
> 4 S1
> 5 T3
> 6 S2
>
> I want the formulas in worksheet B so they will deliver the following
data -
> don't need them in any certain order, just need each to only list once on
a
> different line.
> WORKSHEET B
> A
> 1 S1
> 2 S2
> 3 T3
> 4 T4
>

"JANA" <[email protected]> wrote in message
news:[email protected]...
>I have a worksheet that has multiple labor categories listed on different
> lines. On another worksheet I want to pull each labor category only once
> and
> put them each on a different line. Please see example below. Is there a
> way
> to do this in excel and if so how?
> Thanks,
> Jana
>
> WORKSHEET A
> A
> 1 S1
> 2 T3
> 3 T4
> 4 S1
> 5 T3
> 6 S2
>
> I want the formulas in worksheet B so they will deliver the following
> data -
> don't need them in any certain order, just need each to only list once on
> a
> different line.
> WORKSHEET B
> A
> 1 S1
> 2 S2
> 3 T3
> 4 T4

This gruops your data without ordering
(replace Ra4 with your data range):

{=IF(ROW(A1)>SUM(IF(LEN(Ra4)>0,1/COUNTIF(Ra4,Ra4))),"",
INDEX(Ra4,SMALL(IF(MATCH(Ra4,Ra4,0)=ROW
(INDIRECT("1:"&ROWS(MATCH(Ra4,Ra4,0)))),
MATCH(Ra4,Ra4,0),""),ROW(A1))))}
FormulaArray => enter with CTRL+SHIFT+ENTER

Ciao
Bruno

Thanks Max, still a problem though. The formula you gave me for column E
puts the row #, not the value in the cell.

Also, the formula you gave me to put in A1 is looking at row E in worksheet
B, not worksheet A where the data is. How do I change that to lookin column
E, worksheet A?

"Max" wrote:

> One way ..
>
> In sheet: A
> -------
> In an empty col to the right, say col E,
> Put in E1: =IF(A1="","",IF(COUNTIF($A$1:A1,A1)>1,"",ROW()))
> Copy down to say, E100, to cover the max expected data in col A
>
> In sheet: B
> -------
> Put in A1:
> =IF(ISERROR(SMALL(A!E:E,ROWS($A$1:A1))),"",
> INDEX(A!A:A,MATCH(SMALL(A!E:E,ROWS($A$1:A1)),A!E:E,0)))
>
> Copy down to A100
> (cover the same extent as done in col E in "A")
>
> The above will return the required results from "A",
> all neatly bunched at the top
> --
> Rgds
> Max
> xl 97
> ---
> Singapore, GMT+8
> xdemechanik
> http://savefile.com/projects/236895
> --
> "JANA" <[email protected]> wrote in message
> news:[email protected]...
> > I have a worksheet that has multiple labor categories listed on different
> > lines. On another worksheet I want to pull each labor category only once
> and
> > put them each on a different line. Please see example below. Is there a
> way
> > to do this in excel and if so how?
> > Thanks,
> > Jana
> >
> > WORKSHEET A
> > A
> > 1 S1
> > 2 T3
> > 3 T4
> > 4 S1
> > 5 T3
> > 6 S2
> >
> > I want the formulas in worksheet B so they will deliver the following
> data -
> > don't need them in any certain order, just need each to only list once on
> a
> > different line.
> > WORKSHEET B
> > A
> > 1 S1
> > 2 S2
> > 3 T3
> > 4 T4
> >
>
>
>

Try this sample file which contains the implemented construct:
http://cjoint.com/?kBqvXyjRnp
(Things should be clearer, I hope)

> The formula you gave me for column E
> puts the row #, not the value in the cell.

Yes, nothing wrong there <g>. Col E is a helper criteria col. The row
numbers returned are arbitrary values which will be read by the formulas in
sheet: B

> Also, the formula you gave me to put in A1 is looking at row E in
worksheet
> B, not worksheet A where the data is. How do I change that to lookin
column
> E, worksheet A?

Nothing wrong here, too. The desired unique values are returned correctly
from col A in Sheet: A by the INDEX(A!A:A ... part of the formula which
points to col A in sheet: A.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"JANA" <[email protected]> wrote in message
news:[email protected]...
> Thanks Max, still a problem though. The formula you gave me for column E
> puts the row #, not the value in the cell.
>
> Also, the formula you gave me to put in A1 is looking at row E in
worksheet
> B, not worksheet A where the data is. How do I change that to lookin
column
> E, worksheet A?

Max,
Thanks for all your patience. I've almsot got it working. Got the formulas
to both work correctly, they just are still entering row numbers instead of
the actual data in the cell. In worksheet A, the formulas I set up in Column
E is listed as follows based on my example:
WORKSHEET A
A E
1 S1 1
2 T3 2
3 T4 3
4 S1
5 T3
6 S2 6

Worksheet B now lists this:

WORKSHEET B
A
1 1
2 2
3 3
4 6

So, the formula is working, but not pulling the LC data. How do I fix this?

THANKS



You say this is fine, it's just a value. In Worksheet B, the formula is
working correctly, but still lists the exact values from Column E, the row
numbers and not the LC data.

"Max" wrote:

> Try this sample file which contains the implemented construct:
> http://cjoint.com/?kBqvXyjRnp
> (Things should be clearer, I hope)
>
> > The formula you gave me for column E
> > puts the row #, not the value in the cell.
>
> Yes, nothing wrong there <g>. Col E is a helper criteria col. The row
> numbers returned are arbitrary values which will be read by the formulas in
> sheet: B
>
> > Also, the formula you gave me to put in A1 is looking at row E in
> worksheet
> > B, not worksheet A where the data is. How do I change that to lookin
> column
> > E, worksheet A?
>
> Nothing wrong here, too. The desired unique values are returned correctly
> from col A in Sheet: A by the INDEX(A!A:A ... part of the formula which
> points to col A in sheet: A.
> --
> Rgds
> Max
> xl 97
> ---
> Singapore, GMT+8
> xdemechanik
> http://savefile.com/projects/236895
> --
> "JANA" <[email protected]> wrote in message
> news:[email protected]...
> > Thanks Max, still a problem though. The formula you gave me for column E
> > puts the row #, not the value in the cell.
> >
> > Also, the formula you gave me to put in A1 is looking at row E in
> worksheet
> > B, not worksheet A where the data is. How do I change that to lookin
> column
> > E, worksheet A?
>
>
>

> So, the formula is working, but not pulling the LC data. How do I fix
this?

I think you probably amended the original formula suggested for sheet: B

Re-check the formula you have placed in A1 in sheet: B,
and ensure that the INDEX part of it is reading: ... INDEX(A!A:A,
[ Do *not* change this part to: INDEX(A!E:E .. ]

Here's the original suggested formula again for your reference

In sheet: B
-------
Put in A1:
=IF(ISERROR(SMALL(A!E:E,ROWS($A$1:A1))),"",
INDEX(A!A:A,MATCH(SMALL(A!E:E,ROWS($A$1:A1)),A!E:E,0)))
Copy down ...

Btw, did you try the sample file provided in my previous post ?
It contains the working implementation ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"JANA" <[email protected]> wrote in message
news:[email protected]...
> Max,
> Thanks for all your patience. I've almsot got it working. Got the
formulas
> to both work correctly, they just are still entering row numbers instead
of
> the actual data in the cell. In worksheet A, the formulas I set up in
Column
> E is listed as follows based on my example:
> WORKSHEET A
> A E
> 1 S1 1
> 2 T3 2
> 3 T4 3
> 4 S1
> 5 T3
> 6 S2 6
>
> Worksheet B now lists this:
>
> WORKSHEET B
> A
> 1 1
> 2 2
> 3 3
> 4 6
>
>
> THANKS
>
>
>
> You say this is fine, it's just a value. In Worksheet B, the formula is
> working correctly, but still lists the exact values from Column E, the row
> numbers and not the LC data.

Max,
I used the spreadsheet you gave me and copied the formulas exactly into my
spreadsheet and for some reason in the spreadsheet I'm using, it doesn't give
me the same LC data as in your example - it gives me row numbers. Can this
have something to do with how my cell is formatted. It's a form I've set up
in excel.
Thanks,
Jana

"Max" wrote:

> Try this sample file which contains the implemented construct:
> http://cjoint.com/?kBqvXyjRnp
> (Things should be clearer, I hope)
>
> > The formula you gave me for column E
> > puts the row #, not the value in the cell.
>
> Yes, nothing wrong there <g>. Col E is a helper criteria col. The row
> numbers returned are arbitrary values which will be read by the formulas in
> sheet: B
>
> > Also, the formula you gave me to put in A1 is looking at row E in
> worksheet
> > B, not worksheet A where the data is. How do I change that to lookin
> column
> > E, worksheet A?
>
> Nothing wrong here, too. The desired unique values are returned correctly
> from col A in Sheet: A by the INDEX(A!A:A ... part of the formula which
> points to col A in sheet: A.
> --
> Rgds
> Max
> xl 97
> ---
> Singapore, GMT+8
> xdemechanik
> http://savefile.com/projects/236895
> --
> "JANA" <[email protected]> wrote in message
> news:[email protected]...
> > Thanks Max, still a problem though. The formula you gave me for column E
> > puts the row #, not the value in the cell.
> >
> > Also, the formula you gave me to put in A1 is looking at row E in
> worksheet
> > B, not worksheet A where the data is. How do I change that to lookin
> column
> > E, worksheet A?
>
>
>

I'm stumped why it didn't work for you,
notwithstanding the sample provided ?

Send me a copy of your file, and I'll take a look at it.
Email to: demechanik <at> yahoo <dot> com
But let me know here whether you are sending or not ..

Or, perhaps upload a sample copy of your file via a free filehost,
say: http://flypicture.com/
and then just post the link to your file in your response here
(like what I did earlier for the sample file)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"JANA" <[email protected]> wrote in message
news:[email protected]...
> Max,
> I used the spreadsheet you gave me and copied the formulas exactly into my
> spreadsheet and for some reason in the spreadsheet I'm using, it doesn't
give
> me the same LC data as in your example - it gives me row numbers. Can
this
> have something to do with how my cell is formatted. It's a form I've set
up
> in excel.
> Thanks,
> Jana