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

Any ideas on this one please ??

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.

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

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

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

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

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.

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

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.

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

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

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

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

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

Thanks for spending the time with me, and your patience

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