+ Reply to Thread
Results 1 to 11 of 11

Extract information from data

  1. #1
    Registered User
    Join Date
    05-11-2019
    Location
    Leicester, England
    MS-Off Ver
    Pro Plus 2016
    Posts
    36

    Extract information from data

    Hi everyone,

    This has been bugging me for a while now and i cant think of a solution. i have a excel sheet with a lot of information on that shows checks completed and any issues weve had. i would like a tab that lists the issues instead of having to search through all the data.

    I have attached a copy of the document and i have created a tab with headings for the information id like it to show and an example of the data i want to see.

    Thank you in advance for your help

    Martin
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    20,914

    Re: Extract information from data

    Your question is ambiguous. Do you want ALL issues reported... or just those for a selected week? All may be rather messy, a selected week, quite simple.
    Glenn



  3. #3
    Registered User
    Join Date
    05-11-2019
    Location
    Leicester, England
    MS-Off Ver
    Pro Plus 2016
    Posts
    36

    Re: Extract information from data

    every week, so every time there is an issue i would like it to go straight into that tab

  4. #4
    Registered User
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    95

    Re: Extract information from data

    I wonder if you'd be able to present this list in a table and structure it so the weeks go down the page. I've attached an example of what I'm thinking. To get what you want, you just need to filter the Issue column so it doesn't show blanks. This allows you to sort and filter to get any information you may want.
    I've assumed that some columns are the same depending on the store name (that may be incorrect, but may give you options).
    I moved the Store column to the right so as you enter new records, you just need to type in the Store name, and everything else to the left fills in automatically. Some of that may need to be changed depending on how you get those values.
    Would this work for you?
    Attached Files Attached Files
    Last edited by Gregb11; 07-14-2019 at 04:51 PM. Reason: attachment didn't upload

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    20,914

    Re: Extract information from data

    Greg didn't copy across enough columns... and you'll need an extra column for week number, but his suggestion is the only practical way to go. Sorting out 52 different data blocks would be pretty much impossible for a formula to cope with. One big long table will work just fine, with filters.

  6. #6
    Registered User
    Join Date
    05-11-2019
    Location
    Leicester, England
    MS-Off Ver
    Pro Plus 2016
    Posts
    36

    Re: Extract information from data

    is there no other way?
    i had a thought about filtering the issues fo that week so it doesnt show the ones that have no issues, selecting all the filtered information that week only and creating a button that runs a macro copy and pasting only the information higlighted to the end of another tab.

    I feel this would work without having to change the document, the trouble is i dont know how i would add in the week number, again another thought would be creating a form asking the user what week they are copying over may work?

    My knwledge of the coding for this is bare minimum i can create the form for the week number but unable to add code to it and I can create a macro to copy and paste however i wouldnt know the code to say copy and paste only the highlighted cells. and add the week number i selected at the start of every row.

  7. #7
    Registered User
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    95

    Re: Extract information from data

    Column G is the week #.

  8. #8
    Registered User
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    95

    Re: Extract information from data

    Quote Originally Posted by mtwa1990 View Post
    is there no other way?
    There are a million ways to do anything pretty much in Excel

    What is the reason you don't want to do what I had suggested? It is a simplified answer and gives the user a lot of flexibility when using tables (with filtering and sorting). I'm not saying what I had suggested is correct because it may not be based on how you are using the spreadsheet, so I'm just trying to get more information so you can get a better answer.

    And yes, you can create a macro that will take the records you want and copy them over to the other sheet, and that might be the way to go, but again, you need to balance complexity and simple with usefulness.

  9. #9
    Registered User
    Join Date
    05-11-2019
    Location
    Leicester, England
    MS-Off Ver
    Pro Plus 2016
    Posts
    36

    Re: Extract information from data

    The document is used by 30 odd people and weve just got people using it properly. The actual document we use is a lot bigger than the one I have provided you to see and does a whole lot more so changing the layout would affect other documents that also look at this data which would mean they would need changing too.

  10. #10
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO200;Win10/MSO2016
    Posts
    10,371

    Re: Extract information from data

    This sol'n uses formulas, but it is very slow...
    A1, Pick week from scrollbar
    C1, Start column for the week: =MATCH("Week " & A1,Data!1:1,0)
    D1, Last used row for the week: =COUNTIF(OFFSET(Data!A1,2,MATCH("week " &A1,Data!1:1,0)+1,300,1),"<>"&"")
    E1, build week's range address: =ADDRESS(3,C1,,,"data")&":"&ADDRESS(D1,C1)
    -----------------------------
    F3, Array* formula for non blank** 'issue' rows:
    Please Login or Register  to view this content.


    *basis for the remaining columns...
    **Many/most of your "blank" cells actually have a single SPACE character. You also have unexpected spaces in many of the 'ext numbers'
    Attached Files Attached Files
    Ben Van Johnson

  11. #11
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    20,914

    Re: Extract information from data

    Non-volatile ordinary (not array) formulas achieving the same - pretty quick.

    For the store info:

    =IFERROR(INDEX(Data!A:A,AGGREGATE(15,6,ROW(Data!$3:$300)/(TRIM(INDEX(Data!$A$3:$HF$3,,MATCH("Week "&$B$1,Data!$1:$1,0)+2):INDEX(Data!$A$10:$HF$300,,MATCH("Week "&$B$1,Data!$1:$1,0)+2))<>""),ROWS($1:1))),"")

    copied across and down and for the fault info:

    =IFERROR(INDEX(INDEX(Data!$A:$HF,,MATCH("Week "&$B$1,Data!$1:$1,0)+COLUMNS($E:E)-1),AGGREGATE(15,6,ROW(Data!$3:$300)/(TRIM(INDEX(Data!$A$3:$HF$3,,MATCH("Week "&$B$1,Data!$1:$1,0)+2):INDEX(Data!$A$10:$HF$300,,MATCH("Week "&$B$1,Data!$1:$1,0)+2))<>""),ROWS($1:1))),"")

    copied across and down
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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