+ Reply to Thread
Results 1 to 16 of 16

Extract Specific Data from range dependant on text criteria

  1. #1
    Forum Contributor Grimace's Avatar
    Join Date
    05-04-2009
    Location
    Gold Coast, Australia
    MS-Off Ver
    Excel 2013 and Office 365 Plus
    Posts
    378

    Extract Specific Data from range dependant on text criteria

    Good morning

    I have a challenge that I cannot quite get right.

    With the attached sheet as an example, I am trying to extract a summary of "Bagroom" shifts done by various teams and list them seperately for easy reference. The challenge is that some teams will have no bagroom component, some have 1 shift in the bagroom, and others have 2 shifts. These are highlighted in purple in the data.

    To the right is my desired outcome:
    - where a team has no bagroom shift, leave it blank (eg LC8)
    - Where a team has one bagroom shift place the task start and task finish times in columns N and O.
    - If a team has a second bagroom shift, place the task start and task finish times in columns P and Q.

    The pasrt i am finding difficult is matching the Team name (LC7 etc) to the bagroom component only in that team.

    Any assistance would be greatly appreciated.

    Thanks in advance

    Darren
    Attached Files Attached Files
    Handy things to keep in mind:

    Click *, if my suggestion has helped you
    If your problem is solved, then please mark the thread as SOLVED

    Sharing is Caring .... spread the knowledge

  2. #2
    Forum Contributor Grimace's Avatar
    Join Date
    05-04-2009
    Location
    Gold Coast, Australia
    MS-Off Ver
    Excel 2013 and Office 365 Plus
    Posts
    378

    Re: Extract Specific Data from range dependant on text criteria

    Any ideas on this one please ??

  3. #3
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,164

    Re: Extract Specific Data from range dependant on text criteria

    Is your sample file in the same format as your original file? I am asking you this so you / i dont have to do any rework incase the formats dont match.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  4. #4
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,637

    Re: Extract Specific Data from range dependant on text criteria

    see attached

    Can you make each team have the same spacing?
    say lc7 has 7 lines, make lc 8 have 7 even though some may be blank?

    it worked (by coincidence) without the spacing but to be "accurate" for all scenarios it would be better to have it setup in a block
    Attached Files Attached Files
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  5. #5
    Forum Contributor Grimace's Avatar
    Join Date
    05-04-2009
    Location
    Gold Coast, Australia
    MS-Off Ver
    Excel 2013 and Office 365 Plus
    Posts
    378

    Re: Extract Specific Data from range dependant on text criteria

    Hi Arlu1201,

    The sheet is in the same format, however this is only a part of 1 day i am using as an example.
    In effect there will be 7 days side by side on a worksheet, with each day having these columns in it.

    The desired outcome part will go below the columns of each day, so each sheet will have the columns D:I repesated 7 teims across it.

    Humdingaling: unfortunately not. The data is extracted from another system and is presented in this way. there may be no "items" in a team, or it may be anywhere up to around 10 "items" under that team. Also, the data for each day will be around 300 rows long. this is just a small sample to use as an example. If you picture up to LC 45 on a quiet day it gives an idea.

    Hope this helps, and thanks in advance for taking a look

  6. #6
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,637

    Re: Extract Specific Data from range dependant on text criteria

    Take two

    ok minor data entry needed

    you will need to type in "END" at the bottom to sign off/indicate where to stop
    and in the the table you need to put "End" to make sure the last team gets picked up

    i have highlighted in yellow these cells
    basically when a new name is picked up it signifies the end of the array for that team

    the vlookup on the 2nd bagroom is dodgy but works (basically vlookup true gives one result and false gives other result, so as long as there is only 2 options then it works)
    can fix it to something better but the first bit right is the critical bit
    Attached Files Attached Files

  7. #7
    Forum Contributor Grimace's Avatar
    Join Date
    05-04-2009
    Location
    Gold Coast, Australia
    MS-Off Ver
    Excel 2013 and Office 365 Plus
    Posts
    378

    Re: Extract Specific Data from range dependant on text criteria

    Thanks for that humdingaling ... appears to work perfectly.

    So i can confirm my understanding, if i know that a max number of rows in my data is going to be say 400 rows, i could put the word "end" in row 400 as a default.
    And if I know that the max number of "LC's" is say 60, i could also have them listed with the word "end" in the cell below LC 60.

    This would give me the correct result with possibly some "leftovers" at the end that have the LC # but no data beside it?


    If i cover my maximum number of data rows I could then also set the column ranges rather than have them open ended, which in my experience has caused sheets to slow down significantly.

    Do I have that right ? I like to understand the concepts rather than just ask for the answer

  8. #8
    Forum Contributor Grimace's Avatar
    Join Date
    05-04-2009
    Location
    Gold Coast, Australia
    MS-Off Ver
    Excel 2013 and Office 365 Plus
    Posts
    378

    Re: Extract Specific Data from range dependant on text criteria

    Hi Humdingaling ..

    I have transposed the formula into one of my real sheets, and i believe i may have done something wrong.
    I have attached a real life example. The section you provided for me is now in rows 420 onward.
    I have gone through manually and noted the anomolies to the right of each entry. It appears to be something to do with a count of some sort, as sometimes the correct times are appearing but in the wrong places?

    This is how it will appear in my actual sheets, and the same table will be produced under each of the 7 days as per this sheet.

    This is one of the smaller workgroups, so the other ones i want toi apply it to will have ranges much larger.

    FYI - adjustments i made to the formula are as follows: (in case this is where the error occured)
    - set the ranges to be c3:c250 in lieu of the full columns. This was to reduce processing as well as avoid a circular reference as the formula is in the same column as the reference table
    - changed the reference in the formula to match the correct columns (in this case Columns B and G)

    Sorry to be a pain

    Darren.
    Attached Files Attached Files

  9. #9
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,637

    Re: Extract Specific Data from range dependant on text criteria

    ok the adjustment of the range does case issues
    if you change it to C1 instead of c3 it should resolve most of the problem

    the 2nd bagroom vlookup is not working 100% need to adjust it for 2nd match though im not very good with that so will need to research that
    something to do with index small array formula from what i can tell

    for the "END" section
    on your real example looks clear you wont be using it like i intended which is fine
    just needs you need to have an extra check in the 1st shift to check if LC exist
    extra coding involved

    will fix those in next iteration but first...lunch
    Attached Files Attached Files

  10. #10
    Forum Contributor Grimace's Avatar
    Join Date
    05-04-2009
    Location
    Gold Coast, Australia
    MS-Off Ver
    Excel 2013 and Office 365 Plus
    Posts
    378

    Re: Extract Specific Data from range dependant on text criteria

    I changed the C$3 to C$1 across the board and it appears to be working perfectly.

    Will let you know if that changes, biut for now its awesome.

    Thanks again for your help, hope lunch was good

    Darren.

  11. #11
    Forum Contributor Grimace's Avatar
    Join Date
    05-04-2009
    Location
    Gold Coast, Australia
    MS-Off Ver
    Excel 2013 and Office 365 Plus
    Posts
    378

    Re: Extract Specific Data from range dependant on text criteria

    Sorry ... found a couple of issues

    Works perfectly for the most part, however not picking up any bagroom shifts for LC18, and some of the LC 17 are giving errors as well
    Attached Files Attached Files

  12. #12
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,637

    Re: Extract Specific Data from range dependant on text criteria

    fixed up the lc18 19 20 issue


    im having issues with LC17, even use proper index(small(if(match) formula i can't get it to pick up 2nd result
    rather baffled with it actually

    sorry not the best with arrays

    oh yeah i put in named range LC so the formulas are easier to read
    LC = C1:C250
    Attached Files Attached Files
    Last edited by humdingaling; 07-09-2013 at 02:33 AM.

  13. #13
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,637

    Re: Extract Specific Data from range dependant on text criteria

    ok apparently i was doing it wrong
    here is the corrected version

    the index does not need to be built into boxed array (of LC) because the Small formula gave me the actual row number

    column F&G needs to be CSE formulas (Ctrl+ Shift+Enter) in order to work
    Attached Files Attached Files
    Last edited by humdingaling; 07-09-2013 at 02:47 AM.

  14. #14
    Forum Contributor Grimace's Avatar
    Join Date
    05-04-2009
    Location
    Gold Coast, Australia
    MS-Off Ver
    Excel 2013 and Office 365 Plus
    Posts
    378

    Re: Extract Specific Data from range dependant on text criteria

    Thanks for that

    From what i can see the range has now been changed from eg b$1: i$250 to a named range called "LC"? is that correct?
    The reason i ask is as per the Real example, there will be 1 of these summaries per day for 7 days working across the same sheet. So the columns referred to would be changed on each day.

    If i copy the formula across and just change the named range that should give me the same outcome on the applicable day? I'm also not very good with ranges :-(

    Will give it a go and see what i can come up with.

    Thanks again for all of your help so far

  15. #15
    Forum Contributor Grimace's Avatar
    Join Date
    05-04-2009
    Location
    Gold Coast, Australia
    MS-Off Ver
    Excel 2013 and Office 365 Plus
    Posts
    378

    Re: Extract Specific Data from range dependant on text criteria

    All done and checked ... you are a legend.

    Thanks for spending the time with me, and your patience

  16. #16
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,637

    Re: Extract Specific Data from range dependant on text criteria

    ok i see you predicament
    in this case i think named range may not help because in each column named range needs to refer to the same array so you cant take $ off it

    i made some changes so it is easier to "move" the formula along to next day

    note the table i set up to reference the Cell
    Item, start, finish

    each represent the column of the table

    i moved it along for 2 extra days
    made some changes to "real data" for checking on tuesday
    seems to pick it up
    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)

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