Vlookup to find multiple variations of a text string

[othafa]

Hi, first time post here so bear with me!

I am trying to create a worksheet that displays a list of shift types with names of staff allocated that shift over the period of a week.

I can do this for straightforward shifts but occasionally we have to ask staff to double up on shifts and the vlookup formula I am using wont find the double shifts.

The problem is that there are too many possible combinations of double shifts for me to list them all on the worksheet so I need a formula that can find any possible combination and display the staff member who has been allocated it?

Sample File attached.

Thanks

[#2 · Accepted solution]

@othafa 

With the help of PQ and the data model.

[#3]

@Detlef Lewin 

Thanks for this, it certainly does what I want. 

Is there a way to convert sheet 1 into the table (A1:C24) you created in Tabelle1. I ask because the format of sheet1 is fixed as it is?

Thanks.

[#4]

@othafa 

That is the PQ part. Goto to the PQ editor to see the queries and steps.

To load the table into PQ I assigned a name to the table range (-> name manager),

[#5]

Wow, never heard of the PQ editor! Anyway found it and am looking at the steps you took and kind of understand how it works. Just need to apply it to my original sheet now! Once it's set up does it automatically change the pivot table if the source data changes? I'm asking because there was an error in the source data on sheet 1 and when I changed that the pivot table didn't reflect the change.
Thanks again!

[#6]

@othafa 

You need to refresh.

First refresh the PQ table -> right click -> refresh.

Then refresh the pivot table -> right click -> refresh.

[#7]

Ok that's fine, just one last issue I think!

I'm just getting a count of name in the pivot table, cant see how to actually convert that to the name of the staff?

[#8]

@othafa 

I provided a link to an article from Mr Excel in the workbook. Just follow the steps.

[#9]

Ok got it now thanks so much for all your help.
Vest wishes

[#10]

@Detlef Lewin 

Hi, many apologies but I've hit a snag. When I was working on your fix for the problem I was using my home version of excel which is Microsoft Office 2016. Unfortunately the version at work is Office Standard  2010 and reading up on it online the Power Query is not available for this version, only the professional plus 2010.

What this means is that I don't get the option of adding the data to the data model when I create the pivot table.

Back to square one?

[#11]

---

@othafa wrote:

Back to square one?

---

So it seems.

Or you could argue with your boss and your IT that with a more recent version of Excel you will be able to perform this specific task easily.

And besides Excel 2010 is almost 10 years old.

[#12]

@othafa 

A formula solution.  The key element is to perform a wildcard search for anything that contains a shift name, e.g. search for "*LMP*".  There is a catch that the abbreviations for Early and Late are to be found within other shift codes so I converted them to "E0" and "L0" to avoid 'false positives'.

= IFERROR( INDEX( Name, MATCH( shift, INDEX(shiftTable, 0, day), 0 ) ), "" )

The names 'day' and 'shift' are relative references and the innermost INDEX returns the 3 rows for a given day of the table.

As for your other line of exploration, Power Query is available as an Office 2010 add-in free of charge from Microsoft.  Your IT department could install it for you and I would recommend that they evaluate it themselves!

[#13]

@Peter Bartholomew , that's only for this 2010

[#14]

@Sergei Baklan 

For me, what the documentation suggests and what actually happened were not the same thing.  Power Query and Power Pivot both installed despite my not having the correct version of Office.

Now I am using Office 365 insider but that is because I was desperate to evaluate modern Dynamic Arrays (they are superb and now provide the basis of everything I do outside Power Query).

[#15]

@Peter Bartholomew 

Did you compare your solution with my solution?

Your missing some shifts: DO, LMP1.

And your missing multiple names for the same shift on the same day.

And LMP produces false positives because of LMP1 and LMP2.

[#16]

@Detlef Lewin @Peter Bartholomew 

Peter's solution looks good, I'll need to run it in the real schedule obviously to test it.

Not concerned if it doesnt pick up DO as those are days off which we don't need to display on sheet 2 (in Peter's solution I was intending to deselect DO and AL in the filter section of the pivot table using a macro) but would be concerned if it doesnt pick up duplicates at all.

My bad, the LMP should be LMP1 but we only use a number in shift codes if there are two people doing the same shift. In other words there is a CWE but will never be a CWE1 or CWE2. I think Ive just resolved the duplicate issue....the only duplicates we do use are the E and L shifts and we could possibly number them so no need to call them EO and LO.

Ill try the download myself but company admin privaledges may prevent me doing that, if so and I get someone in IT to look at it for me they may just come back and tell me it wont work!

Watch this space and thanks again for the help.

[#17]

Hi, I've now converted the original file to use the formula you gave me but it's not working properly. I'm on a steep learning curve here as I've never worked with arrays before and hardly ever with Index and Match.

Anyway I set up the named ranges for the constants adjusted for the real data and set it going. It correctly gives me the names for the shifts on Monday but just repeats that name for the rest of the week.

I've run the Evaluate Formula feature and as it steps through it everything seems to be going right until the last step when it reverts to the name it gave for Monday.

Any thoughts?

[#18]

---

@Peter Bartholomew wrote:

@Sergei Baklan 

For me, what the documentation suggests and what actually happened were not the same thing.  Power Query and Power Pivot both installed despite my not having the correct version of Office.

Now I am using Office 365 insider but that is because I was desperate to evaluate modern Dynamic Arrays (they are superb and now provide the basis of everything I do outside Power Query).

---

Hi Peter,

Yes, I agree with both.

Documentation could be outdated. That was with "Where is Power Pivot" when expanding of Power Pivot on all SKU was announced first on uservoice, and doc was updated only in a while. Sometimes delay could be quite significant. However, I don't think there were some changes for outdated 2010.

Power Query becomes less important if you work within one notebook. My main scenarios of usage is to pick-up and transform data from different sources (SQL databases, files on SPO and like) to combine in one workbook with its local data. That was I did with Pivot Table connectors long ago, now PQ is the main tool.

@Peter Bartholomew 

[#19]

@othafa 

The catch might be that 'day' is a relative reference to the indices 1,...7 above the table.  To get a fresh value for each column, the array formula is applied to a single column and then copied across to give a set of 7 array formulas rather than one single array formula.

[#20]

Not sure I follow but cant understand why you fix on my example works but it wont work on the real data.

Was wondering if there is an issue with shifts in the source data that don't appear in the left hand column of sheet2?

There are about 50 core shifts and while these might appear in the source data I haven't yet been able to ensure that they all appear in the shift column on sheet 2.

[#21]

Apologies lol. I'm in the UK so was quite late when I read your message. Read it again this morning and made perfect sense. Working perfectly now.
I'm going to need to change some of the shift names as you did with the L & E shift and am checking with my managers if that will have any implications?
One thing I thought of was that since all double shifts have a + between them could we not change the names range "shift" to look for that as a wildcard? I tried "*+"&range&"+*" but that didnt work.
Thanks again for your help.

[#22]

@othafa 

---

@othafa wrote:
 I tried "*+"&range&"+*" but that didn't work.

I think you were within a whisker of getting it to work though.  If you pad both the lookup value and the lookup array with leading and trailing "+"s, you will find the strings you are searching for but "+L+" is not present in "+LMP+".  You will also locate it successfully within "+L+N+", just as you want.

p.s. I seem to have had different versions of the workbook.  The attached uses a relative reference within the formula for 'shift' which allows the worksheet formulas to be entered without CSE.

[#23]

@Detlef Lewin 

I liked the reference to the Bill Jelen podcast.  I have yet to get around to using measures and DAX so perhaps this will encourage me to seek out new problems where I can use it!

[#24]

Sorry you've lost me now. The sheet you sent in your last message has this formula in each cell of the table on sheet 2 {=IFERROR( Name, MATCH( shift, "+"&INDEX(shiftTable, 0, day)&"+", 0 ) ), "")} which looks like an array and which I can only replicate by using CSE which you say in your last paragraph is not necessary. I have amended the shift formula so it's the same as that in the sheet you attached?

When I do that however it now gives me the correct name in the first row but then replicates those names for the remaining rows.

Sorry to still be struggling with this!

[#25]

@othafa 

Oops! My fault.

I clearly changed the worksheet but uploaded it before saving!

The version I am now about to post picks up both the day and the shift code by relative referencing.  Each cell requires a reference to be made to the 'shiftTable' but padded with "+".  If this 'padding' were done in a worksheet formula this would require CSE but by hiding it away in the Named Formula the array operation happens by default.

I apologise for changing strategy mid-stream; it is not a good teaching style!

[#26]

Ok, that works now. Have made the changes and will let managers loose on it later. I ran various tests and couldn't break it but my managers have a knack for finding anomalies!

Thank you so much for your help, hopefully you've heard the last from me.

[#27]

ur **bleep**

[#28]

**bleep**

[#29]

@othafa 

Good luck :-)