Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 6 of 6

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

  1. #1
    Join Date
    2nd February 2009
    Posts
    5

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,678

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

    Hi Rick,

    Welcome to board!!

    try,

    VB:
    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

  3. #3
    Join Date
    2nd February 2009
    Posts
    5

    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!

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,798

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

    Try adding;

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

  5. #5
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,678

    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?

  6. #6
    Join Date
    2nd February 2009
    Posts
    5

    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.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Replies: 2
    Last Post: February 13th, 2009, 23:30
  2. Replies: 3
    Last Post: February 5th, 2008, 23:51
  3. Replies: 3
    Last Post: June 27th, 2007, 02:45
  4. Match And Copy Data Between Sheets
    By Hambone in forum Excel General
    Replies: 8
    Last Post: November 23rd, 2006, 00:16
  5. Using MATCH on multiple sheets to copy data
    By onereggie in forum Excel General
    Replies: 7
    Last Post: December 4th, 2005, 05:56

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
  •  
porno