| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how many duplicates in the one column
i have about 7000 rows with 5 column but one column has some duplicates i
would like to count duplicates as ones and single as ones also A B 1 Nika Well 2 Nika Well 3 Mikaney 4 Lisa Web 5 Lisa Web i used formula as =sumif(IF(FREQUENCY(MATCH(B2:B5,B2:B5,0),MATCH(B2: B5,B2:B5,0))0,1)) IT GIVE ME THE WRON aSNWER because there is space after B2 cell how i can trim it before use the Match help me any one |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how many duplicates in the one column
Please note that this is an array formula. press CTRL+SHIFT+ENTER to enter
the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =SUM(IF(FREQUENCY(MATCH(TRIM(B2:B5),TRIM(B2:B5),0) , MATCH(TRIM(B2:B5),TRIM(B2:B5),0))0,1)) If this post helps click Yes --------------- Jacob Skaria "J Walia" wrote: i have about 7000 rows with 5 column but one column has some duplicates i would like to count duplicates as ones and single as ones also A B 1 Nika Well 2 Nika Well 3 Mikaney 4 Lisa Web 5 Lisa Web i used formula as =sumif(IF(FREQUENCY(MATCH(B2:B5,B2:B5,0),MATCH(B2: B5,B2:B5,0))0,1)) IT GIVE ME THE WRON aSNWER because there is space after B2 cell how i can trim it before use the Match help me any one |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how many duplicates in the one column
=SUMPRODUCT((A1:A5<"")/COUNTIF(A1:A5,A1:A5&""))
"J Walia" wrote: i have about 7000 rows with 5 column but one column has some duplicates i would like to count duplicates as ones and single as ones also A B 1 Nika Well 2 Nika Well 3 Mikaney 4 Lisa Web 5 Lisa Web i used formula as =sumif(IF(FREQUENCY(MATCH(B2:B5,B2:B5,0),MATCH(B2: B5,B2:B5,0))0,1)) IT GIVE ME THE WRON aSNWER because there is space after B2 cell how i can trim it before use the Match help me any one |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how many duplicates in the one column
=SUM(IF(FREQUENCY(MATCH(TRIM(B2:B5),TRIM(B2:B5),0 ),
MATCH(TRIM(B2:B5),TRIM(B2:B5),0))0,1)) That'll count empty cells. -- Biff Microsoft Excel MVP "Jacob Skaria" wrote in message ... Please note that this is an array formula. press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =SUM(IF(FREQUENCY(MATCH(TRIM(B2:B5),TRIM(B2:B5),0) , MATCH(TRIM(B2:B5),TRIM(B2:B5),0))0,1)) If this post helps click Yes --------------- Jacob Skaria "J Walia" wrote: i have about 7000 rows with 5 column but one column has some duplicates i would like to count duplicates as ones and single as ones also A B 1 Nika Well 2 Nika Well 3 Mikaney 4 Lisa Web 5 Lisa Web i used formula as =sumif(IF(FREQUENCY(MATCH(B2:B5,B2:B5,0),MATCH(B2: B5,B2:B5,0))0,1)) IT GIVE ME THE WRON aSNWER because there is space after B2 cell how i can trim it before use the Match help me any one |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how many duplicates in the one column
i have about 7000 rows
=SUMPRODUCT((A1:A5<"")/COUNTIF(A1:A5,A1:A5&"")) Array entered** : =SUM(IF(FREQUENCY(IF(A1:A7000<"",MATCH(A1:A7000,A 1:A7000,0)),ROW(A1:A7000)-ROW(A1)+1),1)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. The array formula is significantly more efficient on a range that size. AVG calc time on 7000 rows: SUMPRODUCT = 2.88 secs FREQUENCY = 0.09 secs -- Biff Microsoft Excel MVP "Teethless mama" wrote in message ... =SUMPRODUCT((A1:A5<"")/COUNTIF(A1:A5,A1:A5&"")) "J Walia" wrote: i have about 7000 rows with 5 column but one column has some duplicates i would like to count duplicates as ones and single as ones also A B 1 Nika Well 2 Nika Well 3 Mikaney 4 Lisa Web 5 Lisa Web i used formula as =sumif(IF(FREQUENCY(MATCH(B2:B5,B2:B5,0),MATCH(B2: B5,B2:B5,0))0,1)) IT GIVE ME THE WRON aSNWER because there is space after B2 cell how i can trim it before use the Match help me any one |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how many duplicates in the one column
Biff, I think you have missed the point which OP is raising.
"there is space after B2 cell how i can trim it before use the Match" If this post helps click Yes --------------- Jacob Skaria "T. Valko" wrote: i have about 7000 rows =SUMPRODUCT((A1:A5<"")/COUNTIF(A1:A5,A1:A5&"")) Array entered** : =SUM(IF(FREQUENCY(IF(A1:A7000<"",MATCH(A1:A7000,A 1:A7000,0)),ROW(A1:A7000)-ROW(A1)+1),1)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. The array formula is significantly more efficient on a range that size. AVG calc time on 7000 rows: SUMPRODUCT = 2.88 secs FREQUENCY = 0.09 secs -- Biff Microsoft Excel MVP "Teethless mama" wrote in message ... =SUMPRODUCT((A1:A5<"")/COUNTIF(A1:A5,A1:A5&"")) "J Walia" wrote: i have about 7000 rows with 5 column but one column has some duplicates i would like to count duplicates as ones and single as ones also A B 1 Nika Well 2 Nika Well 3 Mikaney 4 Lisa Web 5 Lisa Web i used formula as =sumif(IF(FREQUENCY(MATCH(B2:B5,B2:B5,0),MATCH(B2: B5,B2:B5,0))0,1)) IT GIVE ME THE WRON aSNWER because there is space after B2 cell how i can trim it before use the Match help me any one . |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how many duplicates in the one column
Biff, I think you have missed the point which OP is raising.
No, that was already answered. I was making a suggestion based on the generic count of uniques formula TM posted with regards to "big" ranges. -- Biff Microsoft Excel MVP "Jacob Skaria" wrote in message ... Biff, I think you have missed the point which OP is raising. "there is space after B2 cell how i can trim it before use the Match" If this post helps click Yes --------------- Jacob Skaria "T. Valko" wrote: i have about 7000 rows =SUMPRODUCT((A1:A5<"")/COUNTIF(A1:A5,A1:A5&"")) Array entered** : =SUM(IF(FREQUENCY(IF(A1:A7000<"",MATCH(A1:A7000,A 1:A7000,0)),ROW(A1:A7000)-ROW(A1)+1),1)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. The array formula is significantly more efficient on a range that size. AVG calc time on 7000 rows: SUMPRODUCT = 2.88 secs FREQUENCY = 0.09 secs -- Biff Microsoft Excel MVP "Teethless mama" wrote in message ... =SUMPRODUCT((A1:A5<"")/COUNTIF(A1:A5,A1:A5&"")) "J Walia" wrote: i have about 7000 rows with 5 column but one column has some duplicates i would like to count duplicates as ones and single as ones also A B 1 Nika Well 2 Nika Well 3 Mikaney 4 Lisa Web 5 Lisa Web i used formula as =sumif(IF(FREQUENCY(MATCH(B2:B5,B2:B5,0),MATCH(B2: B5,B2:B5,0))0,1)) IT GIVE ME THE WRON aSNWER because there is space after B2 cell how i can trim it before use the Match help me any one . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How can I get rid of duplicates in a column? | Excel Discussion (Misc queries) | |||
| compare data in column A with column B to find duplicates | Excel Discussion (Misc queries) | |||
| How to find duplicates within same column | New Users to Excel | |||
| Duplicates in column | Excel Discussion (Misc queries) | |||
| Duplicates in 1 column | Excel Discussion (Misc queries) | |||
Linear Mode
