Search multiple values to return single values

[Guest]

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

 

[Max]

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

 

[Bruno Campanini]

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

 

[Guest]

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]

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.

--

 

[Guest]

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]

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 ..

 

[Guest]

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]

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)