locked
compare and fetch the values between two sheets of the same excel using VBA macro with the help of for...each loop RRS feed

  • Question

  • I wanna compare two sheets of the myexcel.xlsx file in my EXCEL 2010 and with  2 coilumn values using foreach loop.

    if the column value, say B2 of sheet1 == G2 of sheet2 then i need to fetch the value of  H2 COLUMN FROM THE SHEET2  and

    update the J2 column of sheet1 with value.

    how its possible using VBA macro .

     i tried  with the below code: , but am missing something:

    Option Explicit

    Sub test()

        Dim varSheetA As Variant
        Dim varSheetB As Variant
        
        Dim varSheetSource As Variant
        Dim varSheetTgt As Variant
        
        Dim strRangeToCheckSRC As String
        Dim strRangeToCheckTarget As String
        Dim iRow As Long
        Dim iCol As Long
        Dim rngSource As Range, cellSource As Range
        Dim rngTarget As Range, cellTarget As Range
        
        varSheetSource = Worksheets("SOURCE")
        varSheettgt = Worksheets("TARGET")
        
        Set rngSource = varSheetSource.Range("A2:A100")
        Set rngTarget = varSheetTgt.Range("F35000:F37000")
        
        For Each singleSrceSheetCell In rngSource.Cells
            Debug.Print singleSrceSheetCell.Address, singleSrceSheetCell.Value
           
        Next singleSrceSheetCell
        
         'strRangeToCheckSRC = "A2:A100"   'strRangeToCheckTarget = "F35000:F37000"
     

        Debug.Print Now
    End Sub

    • Edited by SaMolPP Wednesday, April 25, 2018 9:56 AM
    Wednesday, April 25, 2018 9:54 AM

Answers

  • Copy the code for the ConcatenateIf function from http://www.eileenslounge.com/viewtopic.php?f=27&t=12298&p=91187#p91187 into a module in the Visual Basic Editor.

    Let's say the first ID value on the TARGET sheet is in A2, and the WFDescription should go into D2. Enter the following formula in D2:

    =ConcatenateIf(SOURCE!$A$2:$A$1000,A2,SOURCE!$C$2:$C$1000,", ")

    Adjust the ranges if needed, then fill down.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by SaMolPP Saturday, May 5, 2018 1:16 PM
    Wednesday, April 25, 2018 8:25 PM

All replies

  • Perhaps

    Sub Test()
        With Worksheets("SOURCE").Range("J2:J100")
            .Formula = "=IFERROR(VLOOKUP(B2,TARGET!$G$2:$H$1000,2,FALSE),"""")"
            .Value = .Value
        End With
    End Sub

    Adjust the ranges if needed, then run the macro.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, April 25, 2018 1:16 PM
  • thanks for your time. the issue, there are 100+ instances of the same ID column of my sourcelexcel.

    so, i wanna combine those multiple cell values which is having same ID number and concatenate these and insert into the target excel's new column's cell  value.

    SOURCE EXCEL

    TARGET  EXCEL :

     




    • Edited by SaMolPP Wednesday, April 25, 2018 8:16 PM
    Wednesday, April 25, 2018 7:47 PM
  • I see only one screenshot...

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, April 25, 2018 8:02 PM
  • sorry for the late update:

    in the newly created WFdescription column, initially it will be empty. that column has to be filled with the values from the source excel's description columns combined with the same ID ,

    kind of primary key- foriegn key concept:

    in the target excel, the ID column is UNIQUE. but in the source excel , ID column is having many rows. with multiple tasks descriptions.( its a workflow based process data ) 

    in the source excel, there were 4 records with same same ID 6023. i need to fetch the description column of each 6023 RECORDs and concatenate them and insert into the TARGET EXCEL's WFDescription column after running the macro against the same ID 6023 in the target excel.

    i think VLOOKUP may not work, as i need to get the description cell values from the multiple ROWS of the same ID number. i think i just tried with VLookup WITHOUT MACRO, but that didnt work

    if i think from the pseudo code logic : i was thinking like:

     1)fetch the ID column value (say 6023) and fetch the description from the source excel and concatenate the values.

    2) now i will pass this ID column and concatenated string value as a parameter ( here its 6023 and string value) to TARGETEXCEL  and find the ID column, WFDescription column and insert this concatentated to the WFDescription column's cell value where ID = 6023.

    am unable to do this logic, programmatically.

    • Edited by SaMolPP Wednesday, April 25, 2018 8:27 PM
    Wednesday, April 25, 2018 8:10 PM
  • Copy the code for the ConcatenateIf function from http://www.eileenslounge.com/viewtopic.php?f=27&t=12298&p=91187#p91187 into a module in the Visual Basic Editor.

    Let's say the first ID value on the TARGET sheet is in A2, and the WFDescription should go into D2. Enter the following formula in D2:

    =ConcatenateIf(SOURCE!$A$2:$A$1000,A2,SOURCE!$C$2:$C$1000,", ")

    Adjust the ranges if needed, then fill down.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by SaMolPP Saturday, May 5, 2018 1:16 PM
    Wednesday, April 25, 2018 8:25 PM
  • Hello SaMolPP,

    Has your original issue,fetching data from target worksheet via source worksheet data, resolved? If it has, I would suggest you mark helpful reply to close the thread.

    For the new issue about combing same ID, please post a new thread for new issue.

    Thanks for understanding.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, April 26, 2018 6:58 AM