Consulting

Page 2 of 2 FirstFirst 1 2
Results 21 to 23 of 23

Thread: VBA Duplicate Name Check and Calculating the Values

  1. #21
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,237
    Location
    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.
    Remember To Do the Following....
    Use tags when posting code to the thread,
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  2. #22
    Knowledge Base Approver VBAX Guru GTO's Avatar
    Joined
    Sep 2008
    Posts
    3,364
    Location
    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

  3. #23
    VBAX Expert
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    854
    Location
    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]
    If things don't change they stay the same
    Quite often there is a picnic problem (problem in chair not in computer)
    "We were not told it was impossible, so we did it."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •