none
I want to retrieve data based on cell values RRS feed

  • Question

  • I want to retrieve some rows data to Sheet 2 (say) based on cell values in Sheet 1 (say). I have some data in A1:D100, in a range from A1:A5, A6:A20 and A21:A100 unique numbers x, y and z say, I want to retrieve the data from ranges A1:D5, A6:D20 and A21:D100 respectively. If the value in A1:A5 is commonly numeric 20(say), and to retrieve with reference of cell G3 I given numeric 20, the data A1:D5 should be pasted into G1:J5(say) from sheet 1 to sheet 2.  Thanks in advance.

    Regards,

    Harsha V.

    Tuesday, December 4, 2018 1:44 PM

Answers

  • Hi Harsha,

    Thanks for your asking. Please remember to mark the replies as answers if they helped and please help us close the thread.

     

    Thank you for understanding. If you have any question, or update, please feel free to let us know.

     

    I wish you a happy life!

     

    Best Regards,

     

    Lina


    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.

    Wednesday, December 12, 2018 2:53 AM

All replies

  • Hi Harsha,

    Please refer to the following code:

    Sub CreateMatchedOutput()
        Dim quickIDSht2 As Range, quickIDSht3 As Range, id As Range
        Dim rng1 As Range, rng2 As Range
        Dim matchIndex As Long, cnt As Long
    
        Set quickIDSht2 = Worksheets("Sheet2").Range("C1:C4") //quickID column in Sheet2
        Set quickIDSht3 = Worksheets("Sheet3").Range("A1:A4") //quickID column in Sheet3
        cnt = 1
    
        For Each id In quickIDSht2
            Set rng1 = Worksheets("Sheet2").Range("A" & id.Row & ":C" & id.Row) //Get all data in row from Sheet2
            matchIndex = WorksheetFunction.Match(id, quickIDSht3, 0) //match quickID in sheet2 to data in Sheet3
            Set rng2 = Worksheets("Sheet3").Range("B" & matchIndex & ":D" & matchIndex) //Get all data in Sheet3 based on rowindex given by match above
            rng1.Copy Destination:=Worksheets("Sheet4").Range("A" & cnt) 
            rng2.Copy Destination:=Worksheets("Sheet4").Range("D" & cnt)
            cnt = cnt + 1
        Next id
    End Sub

    For more information, please see the following links:

    Getting row data when a cell is a specific value

    Vba code to retrieve an entire column from sheet1 if the value in a cell of sheet2 matches the value of a header in sheet1

    Copy Data to Another Excel WorkBook Based on Criteria Using VBA

    Hopefully it helps you.

    Best Regards,

    Lina


    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.

    Wednesday, December 5, 2018 2:22 AM
  • Hi Lina,

    Thanks for your support. Let me explain my exact process what to get the result.

    My Data: 

    Sheet1 Sheet2 
    A B C D A B C
    1 Fruits ID Type Quantity 1 Fruits Type Quantity
    2 Apples A101 Red 20 2 Apples Green 15
    3 Apples A102 Green 15 3 Mangos Green 30
    4 Mango A103 Green 30
    5 Mango A104 Yellow 5

     

    The above is one example to express my view.

    Headers are in row 1 fixed (say). If my cell reference G5 of sheet 2 is given as "Green" then the rows 3 and 4 should be copied to sheet 2 (say where to get my required data) with respect to headers. If my selection is "Mango" then my requirement is to get rows 4 and 5 with respect to headers. Thanks.

    Regards,

    Harsha V.

    Thursday, December 6, 2018 7:58 AM
  • Hi Harsha,

    Sorry for the late reply.

    You can get the currently selected cell first.

    For one cell:

    ActiveCell.Select

    For multiple selected cells:

    Dim rng As Range
    Set rng = Range(Selection.Address)

    For more information, please see the following link:

    Get the current cell in Excel VB

    Get User Selected Range

    Then compare the currently selected value with all the values in one column, and copy the same to sheet 2.

    Hopefully it helps you.

    Best Regards,

    Lina


    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.

    Monday, December 10, 2018 5:27 AM
  • Hi Harsha,

    Thanks for your asking. Please remember to mark the replies as answers if they helped and please help us close the thread.

     

    Thank you for understanding. If you have any question, or update, please feel free to let us know.

     

    I wish you a happy life!

     

    Best Regards,

     

    Lina


    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.

    Wednesday, December 12, 2018 2:53 AM