+ Reply to Thread
Results 1 to 13 of 13

Search specific text from multiple sheets based on condition

  1. #1
    Registered User
    Join Date
    08-11-2010
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    58

    Search specific text from multiple sheets based on condition

    Good Day All!!

    We are looking for some help in extracting information from existing sheets based on a condition and copying it into another sheet.

    We have two systems one core and one updated. There are lot of screen in the Updated system where no one has clue what has been updated.
    The screen parameters could have been updated or a new screen could have been created. We are to figure out the difference between Core and Updated system and list it down for further evaluation and requirement analysis. We are to build new screens in mid-range systems so this would be the input to our requirements doc where we would be building new screens.

    We have around 300 sheets where we manually copied which are mainframe copybooks. The intent is to extract the list of parameters from each copybook and map it against a mid-range database table. We are trying to get to the solution but getting stuck while moving from line to line to search and extract from each of the 300 sheets and then place it one sheet in order like below.

    Below is the snapshot of the data.
    The first line is the Screen name and each section has
    a. Group Name
    b. Parameter-L
    c. Parameter-A
    d. Actual Parameter

    CORPORATE PARAMETER FILE ACCESS
    MM-SC1-AREA.
    FILLER PIC X(12).
    MM-SC1-CORPORATE-GROUP.
    05 MM-SC1-CORPORATE-L PIC S9(4) COMP.
    05 MM-SC1-CORPORATE-A PIC X.
    05 MM-SC1-CORPORATE PIC X(03).
    MM-SC1-REGION-GROUP.
    05 MM-SC1-REGION-L PIC S9(4) COMP.
    05 MM-SC1-REGION-A PIC X.
    05 MM-SC1-REGION PIC X(02).
    There are lot of sub components in writing macro, we are trying our best to get this done but help here would be really great as this would avoid lot of manual work.

    Attached is the sample spreadsheet for your reference. Await for your support.

    Regards,
    Sri
    Attached Files Attached Files
    Last edited by sreeks; 08-21-2017 at 01:50 AM. Reason: Uploaded an updated workbook with sheet names. Earlier it was Input-1 and Input-2 and now have given the actual sheet names.

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: Search specific text from multiple sheets based on condition

    Where is the macro code you are working on

    Consider:
    Go to the Commercial Services section of this site http://www.excelforum.com/commercial-services/
    And read the FAQ on how to solicit paid help.
    Ben Van Johnson

  3. #3
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Search specific text from multiple sheets based on condition

    Your explanation is not very clear.

    You have two columns of data on each sheet, Existing and updated.

    This formula in N2 filled down will flag the rows to be copied.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    You failed to explain how you want these rows copied across.

    This Macro will flag every sheet in the workbook

    Please Login or Register  to view this content.

    This Version will delete the unflagged rows on each sheet.

    What you need done next is unclear to me.


    Please Login or Register  to view this content.
    Last edited by mehmetcik; 08-21-2017 at 07:25 PM.
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  4. #4
    Registered User
    Join Date
    08-11-2010
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    58

    Re: Search specific text from multiple sheets based on condition

    Hi Mehmetcik ,

    Thank you so much for the response. This really saves us lot of time.
    This code is complex for me to decode however will try to understand and scale up on my macro skills which are at the basic level.

    We have two sections:
    1. Updated
    2. Existing

    The Updated (COL: A,B,C,D) section may have rows which are not in Existing (F,G,H,I).
    The first solution (will flag every sheet in the workbook) is marking for Updated.
    In Sheet-1: MMOD34DC --> Row 148 is for Updated (B,C,D) and is flagged in the Column N, however Row 149 (COL: G,H,I) is for Existing which is not flagged

    After running the second logic (delete the unflagged rows on each sheet):
    The below Row-36 is being displayed for Existing (G,H,I) which has -A towards the end: 07 MM-SC1-ADVISORY-A PIC X.

    If the Existing and Updated are identical, it runs flawless and accurate results. The issue is only when they are different.
    Please advise if we can flag for both sections i.e. Updated and Existing and then run the Logic-2 which would delete unflagged rows for both Updated and Existing separately.
    Result:
    Create an Output Sheet:
    1. Have two sections one for Updated and Existing
    2. Col A: # number of flagged parameters for each sheet
    3. Col B: Name of the Sheet
    4. Col C,D,E - Output Sheet
    4.1. Updated Section - B,C,D of all Input Sheet where we ran the macro
    5. Col F,G,H - Output Sheet
    5.1. Existing Section - G, H, I of all Input Sheet where we ran the macro
    I get stuck in loops, insert and append in next row and I come from analysis background and have very minimal knowledge of coding. However try my best to fit in using record macro and some basic macro skills.

    Thank you so much for the support and look forward for your advise. If my explanation is confusing please let me know will try to explain better.

    Kind Regards,
    Srikanth

  5. #5
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Search specific text from multiple sheets based on condition

    I do not understand what you need.

    I can flag both sections for retention separately.

    What if both sides are not flagged. Deleting just one side will mess up the data.

    Please give clear instructions on how you want the Macro to work. One Step at a time.

  6. #6
    Registered User
    Join Date
    08-11-2010
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    58

    Re: Search specific text from multiple sheets based on condition

    Hi Mehmetcik,

    Thank you for the response. Sorry for the confusion.

    Please find the details as below:

    Step-1:
    Flag both sections separately.

    Step-2:
    Delete unflagged ones for both sections separately.

    There will two result sets in each of the sheets:
    1. Updated System
    2. Existing System

    Kind Regards,
    Srikanth
    Last edited by sreeks; 08-23-2017 at 09:45 AM.

  7. #7
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Search specific text from multiple sheets based on condition

    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    08-11-2010
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    58

    Re: Search specific text from multiple sheets based on condition

    Hi Mehmetcik,

    I tried the code and it is getting stuck at the below line:

    Range("A" & LR2 & ":E" & LR).Delete Shift:=xlUp

    Result:
    1. Flagging of items for First section - successfull
    2. Autofilter for first section based on flag - successful
    3. Deletion of unflagged items for the first section - Unsuccessful

    It is not doing anything for the second section. The content remains the same.
    1. Flagging of items for First section - Unsuccessfull
    2. Autofilter for first section based on flag - Unsuccessful
    3. Deletion of unflagged items for the first section - Unsuccessful
    4. It is not performing for other sheets. It is only performing on the first sheet.

    I am now able to understand the logic little by little.

    Kind Regards,
    Srikanth
    Last edited by sreeks; 08-23-2017 at 04:05 PM.

  9. #9
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Search specific text from multiple sheets based on condition

    Ok try this version.


    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    08-11-2010
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    58

    Re: Search specific text from multiple sheets based on condition

    Hi Mehmetcik,

    I added .

    The code is doing the below:
    Sheet-1:
    1. Doing it right for Section-1: Updated System
    2. Not Doing it right for Section-2: Existing System - Unsuccessful

    Sheet-2:
    1. Doing it right for Section-1: Updated System
    2. Not doing it right for Section-2 - Unsuccessful[/I]

    Attached spreadsheet for reference.

    Kind Regards,
    Srikanth
    Attached Files Attached Files
    Last edited by sreeks; 08-23-2017 at 05:05 PM.

  11. #11
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Search specific text from multiple sheets based on condition

    Ahhh

    I missed a "."

    Please Login or Register  to view this content.

  12. #12
    Registered User
    Join Date
    08-11-2010
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    58

    Re: Search specific text from multiple sheets based on condition

    Hi Mehmetcik,

    Thank you so much for the help and support.

    For some reason it is not doing it right for section-2 Existing System.
    Attached for your reference.

    Regards,
    Srikanth
    Attached Files Attached Files
    Last edited by sreeks; 08-23-2017 at 08:14 PM.

  13. #13
    Registered User
    Join Date
    08-11-2010
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    58

    Re: Search specific text from multiple sheets based on condition

    Hi Mehmetcik,

    After several trials overnight, I managed to somehow make it happen.

    Changed M2 to M1 on right side of the below expression:
    .Range("M1:M" & LR).Value = .Range("M1:M" & LR).Value

    It works flawless. Thank you so much for the support and help.

    One last thing would be to put these results in one sheet called "Output" for which I should send a sample shortly.

    Kind Regards,
    Srikanth

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Copy rows from multiple sheets based on condition
    By murzilka in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-29-2016, 04:49 PM
  2. Replies: 2
    Last Post: 06-24-2015, 09:08 AM
  3. Search for specific match and calls the entire row from multiple sheets
    By gameybeets in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-09-2015, 03:41 PM
  4. sum multiple columns based on specific condition
    By chiwing in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 01-03-2014, 11:38 AM
  5. [SOLVED] How do you search through multiple sheets to find a specific value?
    By bean29 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-05-2013, 10:07 AM
  6. sum multiple sheets based on condition
    By Mile029 in forum Excel General
    Replies: 2
    Last Post: 05-25-2011, 04:59 PM
  7. [SOLVED] search multiple sheets for specific date, return data in cell to r
    By NonIllegitimiCarborundum in forum Excel General
    Replies: 0
    Last Post: 04-28-2006, 04:10 PM

Tags for this Thread

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