| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
counting duplicates
I have numbers in two columns like A and B. How can I get the number of
duplicates in column C and delete the rest. I have: A B 256 256 256 256 256 256 345 345 789 789 631 879 436 789 523 282 523 282 I want: A B C 256 256 3 345 345 1 789 789 1 631 879 1 436 789 1 523 282 2 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200802/1 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
counting duplicates
I would use a couple of helper columns:
First, I'd add headers in A1 and B1 In C1, put a header of "Combo" In D1, put a header of "Count" In C2, put this formula: =A2&"--"&B2 In D2, put this formula: =IF(COUNTIF($C$2:C2,C2)1,"Delete me",COUNTIF($C$2:$C$999,C2)) Then select C2:D2 and drag down as far as your data goes. Then convert columns C:D to values Select column C:D edit|copy followed by edit|paste special|values Select column D Data|Filter|Autofilter Filter to only show the "delete me" rows Select those visible cells edit|delete row Remove the autofilter (data|filter|autofilter again) Delete column C "saman110 via OfficeKB.com" wrote: I have numbers in two columns like A and B. How can I get the number of duplicates in column C and delete the rest. I have: A B 256 256 256 256 256 256 345 345 789 789 631 879 436 789 523 282 523 282 I want: A B C 256 256 3 345 345 1 789 789 1 631 879 1 436 789 1 523 282 2 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200802/1 -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
counting duplicates
How can I have your formula without this part "Delete me" ?
Dave Peterson wrote: I would use a couple of helper columns: First, I'd add headers in A1 and B1 In C1, put a header of "Combo" In D1, put a header of "Count" In C2, put this formula: =A2&"--"&B2 In D2, put this formula: =IF(COUNTIF($C$2:C2,C2)1,"Delete me",COUNTIF($C$2:$C$999,C2)) Then select C2:D2 and drag down as far as your data goes. Then convert columns C:D to values Select column C:D edit|copy followed by edit|paste special|values Select column D Data|Filter|Autofilter Filter to only show the "delete me" rows Select those visible cells edit|delete row Remove the autofilter (data|filter|autofilter again) Delete column C I have numbers in two columns like A and B. How can I get the number of duplicates in column C and delete the rest. [quoted text clipped - 25 lines] Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200802/1 -- Message posted via http://www.officekb.com |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
counting duplicates
Why would you not want the "delete me"?
It designates which cells are extraneous so's you can follow the rest of Dave's instructions. If left blank, how would you find the cells later? But if you want that, just get rid of delete me from between the double quotes. =IF(COUNTIF($C$2:C2,C2)1,"",COUNTIF($C$2:$C$999,C 2)) Gord Dibben MS Excel MVP On Mon, 11 Feb 2008 20:14:42 GMT, "saman110 via OfficeKB.com" <u35670@uwe wrote: How can I have your formula without this part "Delete me" ? Dave Peterson wrote: I would use a couple of helper columns: First, I'd add headers in A1 and B1 In C1, put a header of "Combo" In D1, put a header of "Count" In C2, put this formula: =A2&"--"&B2 In D2, put this formula: =IF(COUNTIF($C$2:C2,C2)1,"Delete me",COUNTIF($C$2:$C$999,C2)) Then select C2:D2 and drag down as far as your data goes. Then convert columns C:D to values Select column C:D edit|copy followed by edit|paste special|values Select column D Data|Filter|Autofilter Filter to only show the "delete me" rows Select those visible cells edit|delete row Remove the autofilter (data|filter|autofilter again) Delete column C I have numbers in two columns like A and B. How can I get the number of duplicates in column C and delete the rest. [quoted text clipped - 25 lines] Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200802/1 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
counting duplicates
Can anyone come up with nice macro?
thx. saman110 wrote: I have numbers in two columns like A and B. How can I get the number of duplicates in column C and delete the rest. I have: A B 256 256 256 256 256 256 345 345 789 789 631 879 436 789 523 282 523 282 I want: A B C 256 256 3 345 345 1 789 789 1 631 879 1 436 789 1 523 282 2 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200802/1 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
counting duplicates
Hello,
You can use my UDF lfreq: http://www.sulprobil.com/html/listfreq.html If you want to use the UDF as it is, select C1:C9 and array-enter: =A1:A9&B1:B9 Then select D1:E9 and array-enter =lfreq(C1:C9) for example. But you might want to adapt this macro for your own purpose. Regards, Bernd |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
counting duplicates
Hello,
I updated my UDF. Now you can select C1:D9 and array-enter: =lfreq(A1:A9&B1:B9) Regards, Bernd |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
counting duplicates
This is the formula that I'm looking for.
=COUNTIF(C:C,C1) Does anyone know how can I create this function in macro? thx. saman110 wrote: I have numbers in two columns like A and B. How can I get the number of duplicates in column C and delete the rest. I have: A B 256 256 256 256 256 256 345 345 789 789 631 879 436 789 523 282 523 282 I want: A B C 256 256 3 345 345 1 789 789 1 631 879 1 436 789 1 523 282 2 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200802/1 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
counting duplicates
If you want to add the formula to a cell, try recording a macro when you do it
manually and you'll have the code. "saman110 via OfficeKB.com" wrote: This is the formula that I'm looking for. =COUNTIF(C:C,C1) Does anyone know how can I create this function in macro? thx. saman110 wrote: I have numbers in two columns like A and B. How can I get the number of duplicates in column C and delete the rest. I have: A B 256 256 256 256 256 256 345 345 789 789 631 879 436 789 523 282 523 282 I want: A B C 256 256 3 345 345 1 789 789 1 631 879 1 436 789 1 523 282 2 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200802/1 -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
counting duplicates
The formula "=COUNTIF(D:D,D1)" looks at the entire two column and and then
outputs number of repeating numbers. What I want is, it look in col. B and if numbers gets changed it start the calculation again. For example if I have same number in two different places of my range it will add them together which I don't want. Is there anyway to do this? thx. For example: I have: A B 256 256 256 256 256 256 345 345 789 789 631 879 436 789 523 282 523 282 256 256 256 256 256 256 I want: A B C 256 256 3 345 345 1 789 789 1 631 879 1 436 789 1 523 282 2 256 256 3 <=== Instead of 6 saman110 wrote: I have numbers in two columns like A and B. How can I get the number of duplicates in column C and delete the rest. I have: A B 256 256 256 256 256 256 345 345 789 789 631 879 436 789 523 282 523 282 I want: A B C 256 256 3 345 345 1 789 789 1 631 879 1 436 789 1 523 282 2 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200802/1 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
counting duplicates
Hello,
This you can do with some helper columns: Enter into C1:H1: 1 =IF(C1=1,MATCH(1,C2:C$999,FALSE),0) 1 =INDEX($A$1:$A$999,MATCH(ROW(), $E$1:$E$999,FALSE)) =INDEX($B$1:$B$999,MATCH(ROW(),$E$1:$E$999,FALSE)) =INDEX($D$1:$D$999,MATCH(ROW(),$E$1:$E$999,FALSE)) And into C2:H2: =IF(AND(A1=A2,B1=B2),0,1) =IF(C2=1,MATCH(1,C3:C$999,FALSE),0) =IF(D20,E1+1,E1) =INDEX($A$1:$A$999,MATCH(ROW(),$E$1:$E$999,FALSE)) =INDEX($B$1:$B$999,MATCH(ROW(),$E$1:$E$999,FALSE)) =INDEX($D$1:$D $999,MATCH(ROW(),$E$1:$E$999,FALSE)) Copy C2 down to C13 and D2:H2 down to D12:H12 Regards, Bernd |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
counting duplicates
This is what I got so far, but I get wrong calculation when either column has
more repeated numbers. In sheet1 I have data in A:K and col. G:H contains my numbers. Any Help? Sub change_rows() Dim RowNdx As Long Dim LastRow As Long Dim name As String name = InputBox("Enter Customer Name") Sheets("Sheet1").Select Columns("I:K").Select Range("K1").Activate Selection.Delete Shift:=xlToLeft Columns("C:F").Select Range("F1").Activate Selection.Delete Shift:=xlToLeft Columns("A:B").Select Range("B1").Activate Selection.ClearContents Rows("1:1").Select Selection.Delete Shift:=xlUp Range("C1").Select Application.ScreenUpdating = False LastRow = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Row For RowNdx = LastRow To 1 Step -1 With Cells(RowNdx, "C") .Offset(0, -2).Value = name .Offset(0, -1).Formula = " " .Offset(0, 2).Formula = " " .Offset(0, 3).Formula = " " .Offset(0, 4).Value = "208.122.9.46" End With Next RowNdx End Sub Sub sorting() Columns("C:D").Select ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Clear ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Add Key:=Range("C1"), _ SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Sheet1").Sort .SetRange Range("C:D") .Header = xlNo .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub Sub colscalc() Range("A1").Select Range(ActiveCell.Offset(0, 1), Range("B" & Rows.Count).End(xlUp)).Select Dim cel As Range For Each cel In Selection cel.FormulaR1C1 = "=COUNTIF(C[2],RC[2])" Next cel Range("D1").Select Range(ActiveCell.Offset(0, 1), Range("E" & Rows.Count).End(xlUp)).Select Dim cel2 As Range For Each cel2 In Selection cel2.FormulaR1C1 = "=COUNTIF(C[-2],RC[-2])" Next cel2 Range("E1").Select Range(ActiveCell.Offset(0, 1), Range("F" & Rows.Count).End(xlUp)).Select Dim cel3 As Range For Each cel3 In Selection cel3.FormulaR1C1 = "=IF(RC[-1]<RC[-4],RC[-1],RC[-4])" Next cel3 End Sub Dave Peterson wrote: If you want to add the formula to a cell, try recording a macro when you do it manually and you'll have the code. This is the formula that I'm looking for. [quoted text clipped - 33 lines] Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200802/1 saman110 wrote: The formula "=COUNTIF(D:D,D1)" looks at the entire two column and and then outputs number of repeating numbers. What I want is, it look in col. B and if numbers gets changed it start the calculation again. For example if I have same number in two different places of my range it will add them together which I don't want. Is there anyway to do this? thx. For example: I have: A B 256 256 256 256 256 256 345 345 789 789 631 879 436 789 523 282 523 282 256 256 256 256 256 256 I want: A B C 256 256 3 345 345 1 789 789 1 631 879 1 436 789 1 523 282 2 256 256 3 <=== Instead of 6 I have numbers in two columns like A and B. How can I get the number of duplicates in column C and delete the rest. [quoted text clipped - 21 lines] 436 789 1 523 282 2 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200802/1 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| counting function but not double counting duplicates | Excel Worksheet Functions | |||
| Counting Names in a Column, Ignoring Duplicates | Excel Discussion (Misc queries) | |||
| Counting duplicates | Excel Discussion (Misc queries) | |||
| Counting and duplicates | Excel Discussion (Misc queries) | |||
| counting duplicates Among Many Sheets, Possible?? | New Users to Excel | |||
Hybrid Mode
