Results 1 to 4 of 4

Thread: Extract data between match pattern, process in another sheet

  1. #1

    Extract data between match pattern, process in another sheet



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have this workbook which contains 3 sheets as per image. Googled at lot...

    1) User uses drop downs in Sheet 1, and enters the Variables or some data.

    2) In Sheet 2, the headers are captured. As per down down selection in Sheet 1 by the user.

    3) In Sheet 3, the user enters details for the corresponding drop down value of Sheet 1. The details entered in Sheet 3 are not the same in Sheet 1 Variables. Those are different as mentioned in image. And the details entered in Sheet 3 varies for respective header values, there is no definite length. May observe the same in image. Yes, only one column is used in Sheet 3.

    4) Now in Sheet 2, need to get the values entered in Sheet 3.
    For example: 1 to 5 values of JAVA_1 i.e code to code4, just above JAVA_2 must be captured or extracted from Sheet 3 to Sheet 2 if header value is entered. If not should be blank as in image. To make it understandable just mentioned count to the left values, marked in blue block. Otherwise its blank, when find ###, after code10 of JAVA_3 will have 3 ###.

    5) The header values JAVA_1 to JAVA_5 and the details entered in Sheet 3 for respective header also varies as per user requirement.

    Could I know how to achieve this in simplest way. Possibly without VB. Anyways is fine.

    I was thought this can be achieved by extracting values between 2 match patterns. Like between JAVA_1 to JAVA_2. But not sure how to achieve this.

    I tried this macro but its lengthy and not dynamic.

    Click image for larger version. 

Name:	Extract.jpg 
Views:	18 
Size:	92.2 KB 
ID:	2295

    Code:
        Range("A1").Select
        Cells.Find(What:="JVM_1", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
            :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            True, SearchFormat:=False).Activate
        Cells.Find(What:="save", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
            :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            True, SearchFormat:=False).Activate
        Range("C9").Select
        Range(Selection, Selection.End(xlUp)).Select
        Selection.Cut
        Sheets("Sheet5").Select
        Cells.Find(What:="JVM_1", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
            :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            True, SearchFormat:=False).Activate
        Range("B3").Select
        Range("B2").Select
        ActiveSheet.Paste
        Range("B1").Select
        Sheets("Sheet4").Select
        Selection.Find(What:="JVM_2", After:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=True, SearchFormat:=False).Activate
        Range("C8").Select
        Cells.Find(What:="JVM_2", After:=ActiveCell, LookIn:=xlFormulas, LookAt _

  2. #2
    Post an example workbook, with Sheet 1, 2 and 3 completed (obviously without the values form Sheet3 in Sheet 2).

  3. #3
    @Bob Phillips, u may find the screenshot in attachment.
    Click image for larger version. 

Name:	Extract.jpg 
Views:	13 
Size:	92.2 KB 
ID:	2297

  4. #4
    Yeah, but seeing as you have a workbook, you can save me the effort.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •