View Poll Results: Please help me solve this issue

Voters
0. This poll is closed
  • Macro using Lookup

    0 0%
  • Macro using lookup

    0 0%
+ Reply to Thread
Results 1 to 10 of 10

Macro for the fetching values on basic of unique combination of more than one column value

  1. #1
    Registered User
    Join Date
    04-23-2013
    Location
    new york
    MS-Off Ver
    Excel 2003
    Posts
    12

    Post Macro for the fetching values on basic of unique combination of more than one column value

    In sheet1 having 4 columns .
    In sheet2 also having 4 columns.
    First three columns are common between both the sheets.
    So we need values from sheet2 depend on unique combination of col A, B and C in different result sheet .
    We also need need differences beetween both the risk values in result sheet and % variance ..
    Expected result shown in result sheet .

    I tried lot with help of vlookup macro but no luck .
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Macro for the fetching values on basic of unique combination of more than one column v

    try the macro, results are in result tab
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    04-23-2013
    Location
    new york
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: Macro for the fetching values on basic of unique combination of more than one column v

    Thanks a lot this is working for less number of rows . But when i tried it with 50,000 row it taking lost of time . If we can do anything with performance issue then it will great for us .

    Actually looking for one more modification , if any records of sheet 1 not found in sheet2 for that record we have to mark it with red color and in risk values column should replace "NA" Not available

    We also like to implement summery report as given in summery tab of attached sheet.

    Expected result and summary given in attached sheet .


    Thanks in adavance
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Macro for the fetching values on basic of unique combination of more than one column v

    This code is faster, but it does not do any calculations. I think you can do them manually.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    04-23-2013
    Location
    new york
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: Macro for the fetching values on basic of unique combination of more than one column v

    Many thanks . i will try this micro with huge numeber of records . I have also below created micro to find out differences .
    Please let me know how we can mark record with red color in result sheet which are not present in sheet2

    Please Login or Register  to view this content.
    Last edited by arlu1201; 04-29-2013 at 01:44 AM.

  6. #6
    Registered User
    Join Date
    04-23-2013
    Location
    new york
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: Macro for the fetching values on basic of unique combination of more than one column v

    Hi ,

    I have tested this code and working fine with large data as well . But this is giving only matching records information between both the files in result sheet . Thats also ok ....

    So we like to pull not matching record of sheet1 and sheet2 in new result sheet called Sheet1_Not_matching and Sheet2_Not_matching respectively

    Sample result sheet attached herewith..

    Thanks in advance
    Attached Files Attached Files

  7. #7
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Macro for the fetching values on basic of unique combination of more than one column v

    I have added another code (Module3) and the names of the tabs are used as a name of subs.
    My results are not the same as the sample.
    Attached Files Attached Files

  8. #8
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Macro for the fetching values on basic of unique combination of more than one column v

    Manish,

    Welcome to the forum.

    I have added code tags to your 5th post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. In order to put code tags, either type [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] at the end of it, OR you can highlight your code and click the # icon at the top of your post window.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  9. #9
    Registered User
    Join Date
    04-23-2013
    Location
    new york
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: Macro for the fetching values on basic of unique combination of more than one column v

    I have one micro which is given output as below

    TCN Risk Type PST CURR Time Bucket Primo-USDVALUES Sojara-USDVALUES Differences
    AT0000383864-0165 IRDELTA Bond EUR 3Y -2.1147 -9.966441647 7.851741647
    AT0000383864-0165 IRDELTA Bond EUR 3Y -6.6821 -9.966441647 3.284341647
    AT0000383864-0165 IRDELTA Bond EUR 3Y -1.2375 -9.966441647 8.728941647
    SUM -10.0343 -29.89932494 19.86502494

    Should be:
    TCN Risk Type PST CURR Time Bucket Primo-USDVALUES Sojara-USDVALUES Differences
    AT0000383864-0165 IRDELTA Bond EUR 3Y -2.1147 -2.1147
    AT0000383864-0165 IRDELTA Bond EUR 3Y -6.6821 -9.966441647 3.284341647
    AT0000383864-0165 IRDELTA Bond EUR 3Y -1.2375 -1.2375
    SUM -10.0343 -9.966441647 -0.067858353


    Macro is

    <CODE>

    Option Explicit
    Sub nomatch1()

    Dim X, Z, i As Long, j As Long, n As Long, Y(), dic As Object, k&, TT

    With Worksheets("Sojara")
    X = .Range("a2").CurrentRegion.Value
    End With

    Set dic = CreateObject("scripting.dictionary")
    dic.CompareMode = 1
    With dic
    ReDim Z(UBound(X, 2))
    For i = 2 To UBound(X, 1)
    For j = 1 To UBound(X, 2)
    Z(j - 1) = X(i, j)
    Next
    TT = Join$(Array(X(i, 1), X(i, 2), X(i, 3), X(i, 4), X(i, 5)))
    .Item(TT) = Z
    Next
    End With

    With Worksheets("Primo")
    X = .Range("a2").CurrentRegion
    End With

    ReDim Y(1 To UBound(X, 1), 1 To UBound(X, 2) + 1)
    With dic

    For i = 2 To UBound(X)
    TT = Join$(Array(X(i, 1), X(i, 2), X(i, 3), X(i, 4), X(i, 5)))
    If .exists(TT) Then
    k = k + 1
    Z = .Item(TT)
    For j = 1 To UBound(X, 2)
    Y(k, j) = X(i, j)
    Next
    Y(k, 7) = Z(5)
    End If
    Next
    End With

    With Worksheets("Matching")
    '.Cells.ClearContents
    .Range("a2").Resize(k, UBound(Y, 2)) = Y
    .Columns.AutoFit
    End With
    MsgBox "Macro Completed"
    End Sub

    <CODE>

    if for the single records of sheet1 there is multple record in sheet2 the it is giving the that many records in result sheet3 . as shown in above result , duplicate entries

    But for one recorrd in sheet1 one there is multiple record in sheet2 then i need sum last column (Risk values ) of sheet2 records in result sheet coresponding to one single recordds of sheet1 as below

    Current output

    TCN Risk Type PST CURR Time Bucket Sojara-USDVALUES Primo-USDVALUES Differences
    AT0000383864-0165 IRDELTA Bond EUR 3Y -9.966441647 -2.1147 -7.851741647
    AT0000383864-0165 IRDELTA Bond EUR 3Y -9.966441647 -6.6821 -3.284341647
    AT0000383864-0165 IRDELTA Bond EUR 3Y -9.966441647 -1.2375 -8.728941647


    Expected output :
    Should be:
    TCN Risk Type PST CURR Time Bucket Sojara-USDVALUES Primo-USDVALUES Differences
    AT0000383864-0165 IRDELTA Bond EUR 3Y -9.966441647 -10.0343 0.067858353


    Could you please let me on this

  10. #10
    Registered User
    Join Date
    04-23-2013
    Location
    new york
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: Macro for the fetching values on basic of unique combination of more than one column v

    Hi , can anyone please help me on above isssue . i am not able to solve it

+ 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