VBA Duplicate Name Check and Calculating the Values

[Aussiebear]

The above posts will appears to be out of sync with normal conversation. This is a result of having to merge two threads on the same topic.

Kumar, please do not start another thread on the same topic.

[GTO]

Greetings again,

In just glancing at the code, it is very easy to see that you have put in a lot of work on this. We would like to help, but I'm afraid we may be having a slight communication gap. Also, and I mean this in the kindest way, your naming conventions (ie - the names you assign to variables) are a bit hard to follow, due at minimum, to their length. In one line of code, I believe it was at column (that is character in the line of code) 412 or so!

Anyways, I think this is part of the problem, at least I know my poor ol' eyes nearly went crossed!

Now let's at least break up your question a bit, and see if we can make better progress.

Looking at row 10 and 11, I see that the vendor name is indeed the same, to wit: "101 California Venture". You state you want to remove the "duplicate value" in the "Excel sheet".

You then state you want the values (resultant) like:
"For Eg : In Column 3 , I want the Value as 77+- = 77 " etc...

Now please help us help you. I know its frustrating, but carefully see if this would better describe what you want. (If not, please carefully re-explain, and/or correct where I go awry.)

I think that what you want so far is this:

1. 'Build' the sheet (Occupancy Consultant Automated), by running the code you have so far. That is, by running the code you have so far to put all the data in.
2. Then, you want to search for duplicate names in the 'Vendor' column.
3. If a duplicate name is found:
   1. add the values in each category, from ea of the 'duplicate records' (so-to-speak), such as "Actual YTD".
   2. Place the total for each category in just one of the duplicate records; let's say the first record.
4. Then delete the entire row(s) to eliminate the unnecessary duplicate records.

Now if I got that all correct, I would say that I'm not so sure about 'adding' N/A's.... But let's not even worry about that yet. Please advise if I have understood what the goal is thus far.

Thanks,

Mark

[georgiboy]

I have helped with this before havent i, this is the code i gave you...

[vba]Sub Cons()
Dim rCell As Range, LastRow As Integer, Tot As Integer
Dim r As Long, N As Long, MyRange As Range
Dim V As Variant

N = 0
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Set MyRange = Range("A10:A" & LastRow)


On Error Resume Next
For Each rCell In MyRange.Cells

Tot = WorksheetFunction.CountIf(Range("A:A"), rCell.Value)

If Tot > 1 Then
rCell.Offset(, 2).Value = WorksheetFunction.SumIf(Range("A:A"), rCell.Value, Range("C:C")) '+ Tot
rCell.Offset(, 4).Value = WorksheetFunction.SumIf(Range("A:A"), rCell.Value, Range("E:E")) '+ Tot
rCell.Offset(, 6).Value = WorksheetFunction.SumIf(Range("A:A"), rCell.Value, Range("G:G")) '+ Tot
rCell.Offset(, 8).Value = WorksheetFunction.SumIf(Range("A:A"), rCell.Value, Range("I:I")) '+ Tot
rCell.Offset(, 10).Value = WorksheetFunction.SumIf(Range("A:A"), rCell.Value, Range("K:K")) '+ Tot
rCell.Offset(, 12).Value = WorksheetFunction.SumIf(Range("A:A"), rCell.Value, Range("M:M")) / 2
End If
Next


For r = MyRange.Rows.Count To 1 Step -1
V = MyRange.Cells(r, 1).Value
If Application.WorksheetFunction.CountIf(MyRange.Columns(1), V) > 1 Then
MyRange.Rows(r).EntireRow.Delete
N = N + 1
End If
Next r

End Sub[/vba]