Group values

[Guest]

I have column B with 20 values in it. Some of the values repeat more than one
time. For example word fox appears 2 times, word wolf appears 4 times e.t.c

Is there any way to group non-unique values? I mean like perform select with
grouping ? For example I have range of values B1:B20 and i want to put
resulting range to C1:C20, but i only want to have unique values in resulting
range.
If excel would support SQL in order to get resulting range i want, i would
write: "SELECT DISTINCT column B FROM workbook1"

 

[Bob Phillips]

Look at advanced filter which allows you to copy unique values to a new
location.

Data>Filter>Advanced Filter.

--
HTH

Bob Phillips

"Remote Desktop Connection hotkey"

 

[Guest]

Ok. this is solution, but is there any way to make it happen automatically ?
Cuz looks like with this solution I still have to manualy do it for each of
my tabs. (I have 1 tab for each day of the month and there is still 12 of
them in a year !)

Any way to perform the same using formulas would be exelent ...

 

[Bob Phillips]

If they all work the same, record the steps on one worksheet, then just add
a loop to do for each sheet.

--
HTH

Bob Phillips

"Remote Desktop Connection hotkey"

 

[Guest]

Hi,

There may be another roundabout way to achieve this (using an array formula
(Ctrl+Shift+Enter))

Assume your data is in A2:A5
In cell B2, enter the following formula and copy down-
IF(COUNTIF($A$2:$A$5,A2)>1,0,MAX($B$1:B1)+1)
In cell C2, entet the following array formula and copy downward

IF(MIN(IF((B2:B5>0)*(B2:B5>MAX($C$1:C1)),B2:B5))>0,MIN(IF((B2:B5>0)*(B2:B5>MAX($C$1:C1)),B2:B5)),"")

In D2, enter the following formula and copy downward

=INDEX($A$2:$B$5,MATCH(C2,B2:B5,0),1)

Though this formula achieves the result, i believe it can be made more sleek
by giving some more time and thought

Hope this helps

Regards,

 

[Biff]

Hi!

Try this:

Array entered:

=INDEX(B$1:B$20,SMALL(IF(COUNTIF(B$1:B$20,B$1:B$20)=1,ROW($1:$20)),ROW(A1)))

Biff