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