Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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" |
#2
|
|||
|
|||
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" |
#3
|
|||
|
|||
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" |
#4
|
|||
|
|||
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" |
#5
|
|||
|
|||
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" |
#6
|
|||
|
|||
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" |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Match function with duplicate values in an array | Excel Worksheet Functions | |||
Calculate cell row and column | Excel Discussion (Misc queries) | |||
Count Distinct Values by Group Using Pivot Table (NM) | Excel Worksheet Functions | |||
Can't group pivot table items by month in Excel | Excel Discussion (Misc queries) | |||
Subtotal of Subtotal displays Grand Total in wrong row | Excel Worksheet Functions |