+ Reply to Thread
Results 1 to 5 of 5

Macro to Cycle data and extract those values that meet criteria

  1. #1
    Registered User
    Join Date
    10-23-2013
    Location
    Norwich
    MS-Off Ver
    Excel 2010
    Posts
    24

    Macro to Cycle data and extract those values that meet criteria

    Hi Forum,

    I am wondering if anyone has a solution to my question:

    I have a list of data (1,000 lines/ 15 columns, but different line amount every month) that reflect jobs that have been undertaken. For those that have failed, I need to go through the list, filtering on the relevant column to select all the "Failed" status', then copy the relevant jobs to another sheet ... this will later be published in a word for senior management.

    To me it seems possible, but not sure how to code, to have a macro that will go down column a of my data (this column contains job numbers) and look in the corresponding column H of that row to see if it contains the status "Failed". If the field / job does not have this status the macro will move to the next line of data, if it does contain the status, the macro will copy the job number (column A) and populate into another sheet ... having done the copy, it will return to continue cycling through the remaining jobs.

    I will then be able to use look ups on the other sheet to populate the required info needed for the Failed jobs ... as this is in a different order to the raw data I am cycling through, and do not require all the columns available in the raw data.

    Thanks

  2. #2
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Version 2301 (Build 16012.20000) Win 11 Home 64 Bit
    Posts
    22,350

    Re: Macro to Cycle data and extract those values that meet criteria

    Before you go down the macro route, which can be written, try this and see if it will work for you.

    Highlight the header row fields. On the ribbon, select Data, Filter. Click on the header field that has the Failed item, and filter it on Failed.
    Highlight the range of data shown, Copy and Paste into new sheet.

    If you still feel you need a macro to do this, post back with a sample spreadsheet uploaded. It is only a couple of lines of code but is easier to write with some actual test data.

    Alan
    Alan


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    10-23-2013
    Location
    Norwich
    MS-Off Ver
    Excel 2010
    Posts
    24

    Re: Macro to Cycle data and extract those values that meet criteria

    Thank you for responding Alan ... much appriciated!

    See your point and that is what I am current doing, but the task is slightly more demanding than that, as I have to filter multiple times to get a number of different tables and then publish into a 25 page word report (with other various imputs that have already been achieved. By using a macro it enables prcision, ease and ables me to copllate into sheet ready for a further macro to pick up and drop into the word report.

    Give me 2 mins, and I will upload some sample data

    Regards

  4. #4
    Registered User
    Join Date
    10-23-2013
    Location
    Norwich
    MS-Off Ver
    Excel 2010
    Posts
    24

    Re: Macro to Cycle data and extract those values that meet criteria

    TestData.xlsx

    Please find test data attached as requested

    Raw Data is some random data i have collated. As stated, where a job meets the requirements of having a "Failed" status, I would like macro to collate / list in column A of Sheets("TargetDestination") ... look up in column D of same sheet to pull through required info for that item ... would obvioulsy be more columns in both sheets in the actual report

    Thanks and Regards
    Ben
    Last edited by Thomo88; 11-19-2013 at 11:48 AM.

  5. #5
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Version 2301 (Build 16012.20000) Win 11 Home 64 Bit
    Posts
    22,350

    Re: Macro to Cycle data and extract those values that meet criteria

    give this a try:

    Please Login or Register  to view this content.

+ 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. [SOLVED] Component Life Cycle Management, How to Extract Correct Data
    By JONBOY666 in forum Excel General
    Replies: 5
    Last Post: 05-01-2013, 10:52 PM
  2. Replies: 0
    Last Post: 02-16-2013, 01:39 PM
  3. Replies: 0
    Last Post: 12-18-2012, 05:40 AM
  4. Macro for analyzing data and returning lines that meet certain criteria
    By Tdunlap in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-10-2012, 08:32 PM
  5. Totalling up values that meet two criteria
    By johnmitch38 in forum Excel General
    Replies: 5
    Last Post: 06-04-2009, 05:49 AM

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