counting duplicates

[saman110 via OfficeKB.com]

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

 

[Dave Peterson]

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 C22 and drag down as far as your data goes.

Then convert columns C to values
Select column C
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]

Can anyone come up with nice macro?

thx.

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

 

[Bernd P]

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

 

[saman110 via OfficeKB.com]

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.

 

[Dave Peterson]

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]

The formula "=COUNTIF(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

 

[Bernd P]

Hello,

I updated my UDF. Now you can select C19 and array-enter:
=lfreq(A1:A9&B1:B9)

Regards,
Bernd

 

[Bernd P]

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(D2>0,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

 

[saman110 via OfficeKB.com]

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").Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("C1"),
_
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("C")
.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

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.aspx/ms-excel/200802/1

The formula "=COUNTIF(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

 

[saman110 via OfficeKB.com]

How can I have your formula without this part "Delete me" ?

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 C22 and drag down as far as your data goes.

Then convert columns C to values
Select column C
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.aspx/ms-excel/200802/1

 

[Gord Dibben]

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,C2))


Gord Dibben MS Excel MVP

How can I have your formula without this part "Delete me" ?

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 C22 and drag down as far as your data goes.

Then convert columns C to values
Select column C
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.aspx/ms-excel/200802/1