Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Match Values Between 2 Sheets, Copy All Corresponding Data & Sum Results

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Match Values Between 2 Sheets, Copy All Corresponding Data & Sum Results

    Hello,

    I want to locate the corresponding acct number between worksheets “primary_data” and “qty_movement” and once a match is found (from acct worksheet) then copy over the acct numbers and the share data to the results page. If there is no match I don’t want anything copied to the results page, just ignore that data on either worksheet “primary_data” or “qty_movement”.

    • Both ranges on “primary_data” and “qty_movement” worksheet are variable, as accts are left off or kept on depending on the daily activity so the ranges are never set.
    • I need to concatenate on “primary_data” worksheet cells A&B&C to get acct number
    • concatenate on “qty_movment” worksheet cells B&C (need to keep zeros in front of actual numbers for acct number reasons)
    • these accts need to be cross referenced as the accounts on the “acct” worksheet (pre-populated with corresponding acct numbers that never change and will always be on that worksheet)
    • If a match is found on the “acct” sheet then I would like it to either replace the acct so they match on both worksheets or just to recognize those accounts correspond with each other and do the below.
    • then take the 2 concatenated acct numbers off of “primary_data” and “qty_movement” in the I cells and copy them over to the “results” worksheet as well as the share data from “primary_data (column E)” and “qty_movement(Column D)” and then compare the 2 share amounts on “results(column E)”

    Please let me know if i need to elaborate on anything, the attached sample sheet shows what i want to do, but without any formulas or code.

    Thank you!
    Rick
    Attached Files

  • #2
    Re: Match Values Between 2 Sheets, Copy All Corresponding Data & Sum Results

    Hi Rick,

    Welcome to board!!

    try,

    Sub kTest()
    Dim a, b, Acc, w(), n As Long, i As Long, j As Long, S1 As String, S2 As String
    Dim x, y
    Acc = Sheets("acct").Range("a1").CurrentRegion.Offset(1).Resize(, 2)
    a = Sheets("primary_data").Range("a1").CurrentRegion.Offset(1).Resize(, 6)
    b = Sheets("qty_movement").Range("a1").CurrentRegion.Offset(1).Resize(, 4)

    ReDim w(1 To UBound(Acc, 1), 1 To 5)

    For i = 1 To UBound(a, 1)
    S1 = a(i, 1) & a(i, 2) & a(i, 3)
    x = Application.Match(S1, Application.Index(Acc, 0, 2), 0)
    If Not IsError(x) Then
    For j = 1 To UBound(b, 1)
    S2 = b(j, 2) & b(j, 3)
    y = Application.Match(S2, Application.Index(Acc, 0, 1), 0)
    If Not IsError(y) Then
    If x = y Then
    n = n + 1
    w(n, 1) = S1: w(n, 2) = a(i, 5)
    w(n, 3) = S2: w(n, 4) = b(j, 4)
    w(n, 5) = w(n, 4) - w(n, 2)
    Exit For
    End If
    End If
    Next
    End If
    Next
    If n > 0 Then
    With Sheets("results").Range("a2")
    .CurrentRegion.Offset(1).ClearContents
    Union(.Resize(n), .Offset(, 2).Resize(n)).NumberFormat = "@"
    .Resize(n, 5).Value = w
    End With
    End If
    End Sub


    HTH
    Kris

    ExcelFox

    Comment


    • #3
      Re: Match Values Between 2 Sheets, Copy All Corresponding Data & Sum Results

      Hello Kris,

      what you have wrote works fine, albeit a tad slow. For the macro to run entirely through it took a tad over 30 minutes, as i can do this process in about 15 minutes manually i was wondering if there is anyway to increase the speed of the macro, such as skipping rows which have already been paired off?

      MODS - if the above needs to be posted as a seperate question please let me know.

      Thanks for your help!

      Comment


      • #4
        Re: Match Values Between 2 Sheets, Copy All Corresponding Data & Sum Results

        Try adding;

        Code:
        With Application
            .ScreenUpdating = False
            .Calculation = xlCalculationManual
            .EnableEvents = False
        End With
        
        'CODE
        
        With Application
            .ScreenUpdating = True
            .Calculation = xlCalculationAutomatic
            .EnableEvents = True
        End With

        Comment


        • #5
          Re: Match Values Between 2 Sheets, Copy All Corresponding Data & Sum Results

          See Dave's reply. Also can you post the recorded macro of the manual process?
          Kris

          ExcelFox

          Comment


          • #6
            Re: Match Values Between 2 Sheets, Copy All Corresponding Data & Sum Results

            Thank you to both of you. My initial response was unfounded as after i filtered out some raw data i never used the process has ran smoothly quite a bit quicker and frees up my time to do other things.

            Comment

            Trending

            Collapse

            There are no results that meet this criteria.

            Working...
            X