XLOOKUP/VLOOKUP based on Pivot Table Response

[rachel06]

Hello!

I have a spreadsheet I use to track clients. One thing I have to do with this is figure out a specific list to put them on based on what their contract says and cross-reference to our current setup.

I've attached a sample of what I'm looking at. Column A is what the pivot table actually results in, and column B us what I manually added and what I'm trying to automate based on the results.

Is there a way to say like "If a client has these 4, it's this," "if it has these 3, it's this" etc? Or like a calculated field in the pivot with an XLOOUP? I've tried a few things but getting errors.

Thanks so much!!

 

[Kaper]

Are these items always 3 or 4, or could be also 2, 5, 0?
Are they always listed in the same sequence (for first client Device_Excl, Otc, ... or could be the same 4 but different sequence, like Otc Pump, Device_Excl, Otc, ...
How many different combinations (column B possible entries) you have?

 

[rachel06]

Are these items always 3 or 4, or could be also 2, 5, 0?
Are they always listed in the same sequence (for first client Device_Excl, Otc, ... or could be the same 4 but different sequence, like Otc Pump, Device_Excl, Otc, ...
How many different combinations (column B possible entries) you have?

I believe they are always in the same sequence, otherwise I'm sure I could set the pivot to sort them? There are between 2-4. The ones I provided are the most common, though I should have also provided DEVICE_EXCL, OTC OTHERS, OTC SUPPLY as that one is fairly common too.

I was hoping I could somehow set up a table to do an xlookup from and build onto what I needed from there, but maybe it's not going to be that easy!

 

[Kaper]

The table for XLOOKUP etc. will be (I think) complicated, as there is different number of items per client.
I can see 3 possible solutions.

The easiest (but most tedious) will be a long formula with severel nested IFs. Example for B1 and copy down and with recognition of first two cases only is:

=IF(ISERROR(--A1),"",IF(AND(A2="DEVICE_EXCL",A3="OTC OTHER",A4="OTC PUMP",A5="OTC SUPPLY"),"OTC_STR_CP",IF(AND(A2="DEVICE_EXCL",A3="OTC INSULIN",A4="OTC OTHER",A5="OTC SUPPLY"),"OTC_STR_PMP","not recognized")))

so =IF(ISERROR(--A1),"", ..... last ) checks if in column A is number (possibly a text, but converteable to number) then we test first case:
IF(AND(A2="DEVICE_EXCL",A3="OTC OTHER",A4="OTC PUMP",A5="OTC SUPPLY"),"OTC_STR_CP", ... one before last )
then the second case IF(AND(A2="DEVICE_EXCL",A3="OTC INSULIN",A4="OTC OTHER",A5="OTC SUPPLY"),"OTC_STR_PMP","not recognized")
of course in real life instead of "not recognized" there would be third case and so on.

the second possibility would be to construct such a test in VBA - could be a bit easier, because for each cell i'd check if it's number, then bould a string from all non-numbers below, down to next number, and then xlookup in VBA such combination in reference table (one could try similar approach in formulas, but i dont think it would be that easy as in VBA).

the third one is preprocessing data with PowerQuery and recognize what type each client is. then either produce final pivot with power Query, or do it standard way, and use xLOOKUP/VLOOKUP to find each client in Power Query produced reference table.

 

[rachel06]

The table for XLOOKUP etc. will be (I think) complicated, as there is different number of items per client.
I can see 3 possible solutions.

The easiest (but most tedious) will be a long formula with severel nested IFs. Example for B1 and copy down and with recognition of first two cases only is:

=IF(ISERROR(--A1),"",IF(AND(A2="DEVICE_EXCL",A3="OTC OTHER",A4="OTC PUMP",A5="OTC SUPPLY"),"OTC_STR_CP",IF(AND(A2="DEVICE_EXCL",A3="OTC INSULIN",A4="OTC OTHER",A5="OTC SUPPLY"),"OTC_STR_PMP","not recognized")))

so =IF(ISERROR(--A1),"", ..... last ) checks if in column A is number (possibly a text, but converteable to number) then we test first case:
IF(AND(A2="DEVICE_EXCL",A3="OTC OTHER",A4="OTC PUMP",A5="OTC SUPPLY"),"OTC_STR_CP", ... one before last )
then the second case IF(AND(A2="DEVICE_EXCL",A3="OTC INSULIN",A4="OTC OTHER",A5="OTC SUPPLY"),"OTC_STR_PMP","not recognized")
of course in real life instead of "not recognized" there would be third case and so on.

the second possibility would be to construct such a test in VBA - could be a bit easier, because for each cell i'd check if it's number, then bould a string from all non-numbers below, down to next number, and then xlookup in VBA such combination in reference table (one could try similar approach in formulas, but i dont think it would be that easy as in VBA).

the third one is preprocessing data with PowerQuery and recognize what type each client is. then either produce final pivot with power Query, or do it standard way, and use xLOOKUP/VLOOKUP to find each client in Power Query produced reference table.

I'll do some looking into these! Another thing I thought of.. is there a way to restructure the pivot table to have the 2-5 results in a row concatenated together in one cell instead of one in each row? I could probably do a vlookup then, if that makes sense?

 

[rachel06]

Here's something close to what I'm looking at. I could probably make a formula off of this to append the columns together and just vlookup off a master list, but is there a way to have the name of the column as the values instead of number? It doesn't matter if it's 1 or 1000,

 

[rachel06]

Here's something close to what I'm looking at. I could probably make a formula off of this to append the columns together and just vlookup off a master list, but is there a way to have the name of the column as the values instead of number? It doesn't matter if it's 1 or 1000,

I got something to work here.

=IF(B5<>0,$B$4,"")&IF(C5<>0,$C$4,"")&IF(D5<>0,$D$4,"")&IF(E5<>0,$E$4,"")&IF(F5<>0,$F$4,"")

Basically, if there is a figure in the values field, it's going to bring in the header name. If it isn't, it won't, Then I'll have those values concatenated together, and I have a vlookup to pull in the code I need. It's a little messy but it seems to work!