Group values
 

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"

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

DataFilterAdvanced Filter.

--
HTH

Bob Phillips

"Remote Desktop Connection hotkey"
ft.com wrote in message
...
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"

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" wrote:

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

DataFilterAdvanced Filter.

--
HTH

Bob Phillips

"Remote Desktop Connection hotkey"
ft.com wrote in message
...
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"

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"
ft.com wrote in message
...
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" wrote:

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

DataFilterAdvanced Filter.

--
HTH

Bob Phillips

"Remote Desktop Connection hotkey"
ft.com wrote in
message
...
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"

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:B50)*(B2:B5MAX($C$1:C1)),B2:B5))0 ,MIN(IF((B2:B50)*(B2:B5MAX($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,
"Remote Desktop Connection hotkey" wrote:

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"

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

"Ashish Mathur" wrote in message
...
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:B50)*(B2:B5MAX($C$1:C1)),B2:B5))0 ,MIN(IF((B2:B50)*(B2:B5MAX($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,
"Remote Desktop Connection hotkey" wrote:

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"