+ Reply to Thread
Results 1 to 1 of 1

Modifying code for different columns

Hybrid View

  1. #1
    Registered User
    Join Date
    12-05-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    35

    Modifying code for different columns

    The below formula will remove duplicates based on column A and B it will also apend column D with the values before removing the duplicate.

    For the following statement, am I correct in thinking i,1 is referring to column A and i, 2 to Column B . so If I want to have it join with column A and C I just increment the i,2 to i,3??
    What is the Chr(2)
     ( txt = Join$(Array(a(i, 1), a(i, 2)), Chr(2))
    for this statement would I just change all 3 of the 4's to 5's if I needed to apend column E vice D?

     a(.Item(txt), 4) = a(.Item(txt), 4) & "," & a(i, 4)

    Sub Impact_Assesment()
        Sheets("Impact Assesment").Select
        Dim a, i As Long, ii As Long, n As Long, txt As String
        a = Sheets("Consolidated").Range("a1").CurrentRegion
        With CreateObject("Scripting.Dictionary")
            .CompareMode = 1
            For i = 1 To UBound(a, 1)
                txt = Join$(Array(a(i, 1), a(i, 2)), Chr(2))
                If Not .exists(txt) Then
                    n = n + 1: .Item(txt) = n
                    For ii = 1 To UBound(a, 2)
                        a(n, ii) = a(i, ii)
                    Next
                Else
    
                End If
            Next
        End With
        With Sheets("Impact Assesment").Range("b1").Resize(n, UBound(a, 2))
            .CurrentRegion.ClearContents
            .Value = a
            .Columns.AutoFit
        End With
    Last edited by JBeaucaire; 02-16-2013 at 10:15 AM. Reason: Added code tags, as per forum rules. Don't forget!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1