Results 1 to 1 of 1

Problem comparing two records using excel vba macro

Threaded View

  1. #1
    Registered User
    Join Date
    07-06-2016
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    1

    Problem comparing two records using excel vba macro

    I am currently comparing a value in column A: "1234" with the value in column B "1234" if this matches then look at the value in column E "01/01/2000" and compare this with the value in the below row in column E "01/01/2000" if this all matches then move to the next row and compare the same sets of data. If they match then repeat the process, if the next line does not match then take the values in column C and sum them together and place the sum of all the rows which match in the first number set which matched in column D. I only want to look at the first 4 characters from rows A and B any numbers which come after this I do not want included for example :12345 would only take 1234.

    Table:
    A B Money Column1 Closing
    1234 1234 200.00 500.00 01/01/2000
    1234 1234 300.00 01/01/2000
    1256 1543 400.00 400.00 01/01/2001
    1256 1585 500.00 500.00 01/01/2000


    Here is what I have tried to far:

    
    Sub DemoNew()
    
        Dim dict1 As Object
        Dim c1 As Variant, k As Variant
        Dim currWS As Worksheet
        Dim i As Double, lastRow As Double, tot As Double
        Dim number1 As Double, number2 As Double, firstRow As Double
    
        Set dict1 = CreateObject("Scripting.Dictionary")
        Set currWS = ThisWorkbook.Sheets("Sheet1")
    
        'get last row withh data in Column A
        lastRow = currWS.Cells(Rows.Count, "A").End(xlUp).Row
    
        'put unique numbers in Column A in dict1
        c1 = Range("A2:B" & lastRow)
        For i = 1 To UBound(c1, 1)
            If c1(i, 1) <> "" Then
                'make combination with first 4 characters
                dict1(Left(c1(i, 1), 4) & "," & Left(c1(i, 2), 4)) = 1
            End If
        Next i
    
        'loop through all the numbers in column A
        For Each k In dict1.keys
            number1 = Split(k, ",")(0)
            number2 = Split(k, ",")(1)
            tot = 0
            firstRow = 0
    
            For i = 2 To lastRow
                If k = Left(currWS.Range("A" & i).Value, 4) & "," & Left(currWS.Range("B" & i).Value, 4) Then
                    If firstRow = 0 Then
                        firstRow = i
                    End If
                    tot = tot + currWS.Range("C" & i).Value
                End If
            Next i
            currWS.Range("D" & firstRow) = tot
        Next k
    End Sub
    Please see attachment below

    Example6.PNG
    Last edited by GeorgeH12345; 07-06-2016 at 10:13 AM. Reason: code addition

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. comparing two excel files and saving the unique records in file 1
    By irfanparbatani in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 08-14-2014, 09:18 AM
  2. [SOLVED] Comparing 2 Excel spreadsheets to find missing records in one
    By calvr1ch in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-06-2014, 06:37 PM
  3. [SOLVED] Comparing records to result in a list of unique records
    By greenmat in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-17-2013, 07:45 PM
  4. Comparing two sheets to find common records
    By kthakur in forum Excel General
    Replies: 6
    Last Post: 07-30-2010, 11:39 AM
  5. Comparing common records between 2 arrays
    By Rituraj in forum Excel General
    Replies: 1
    Last Post: 07-27-2010, 02:51 AM
  6. Comparing and deleting records
    By stajna in forum Excel General
    Replies: 0
    Last Post: 08-06-2008, 11:15 AM
  7. Excel Template Problem - updating records
    By elisebev in forum Excel General
    Replies: 3
    Last Post: 01-26-2007, 08:04 AM

Tags for this Thread

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