Excel - extract/flag data (Pivot table? Data Filter? Formula?) 1

[Livia8]

Hi there,

I know that there's probably a straight forward way of doing this, but I can't get my head around it.

I've a list of usernames with the list of responsibilities associated to their profile, e.g.

ALL RESPONSIBILITIES
GREEN APPLES ADMIN
GREEN APPLES CASH ENTRY
GREEN APPLES CASH ENQUIRY
GREEN APPLES OTHER ENTRY
GREEN APPLES OTHER ENQUIRY
GREEN APPLES PAYABLES ENTRY
GREEN APPLES PAYABLES ENQUIRY
GREEN APPLES PURCHASING ENTRY
GREEN APPLES PURCHASING ENQUIRY
GREEN APPLES REPORTING
GREEN APPLES
GREEN PEARS ADMIN
GREEN PEARS CASH ENTRY
GREEN PEARS CASH ENQUIRY
GREEN PEARS GL ENTRY
GREEN PEARS GL ENQUIRY
GREEN PEARS PAYABLES ENTRY
GREEN PEARS PAYABLES ENQUIRY
GREEN PEARS PURCHASING ENTRY
GREEN PEARS PURCHASING ENQUIRY
GREEN PEARS REPORTING
RED APPLES ADMIN
RED APPLES CASH ENTRY
RED APPLES CASH ENQUIRY
RED APPLES OTHER ENTRY
RED APPLES OTHER ENQUIRY
RED APPLES PAYABLES ENTRY
RED APPLES PAYABLES ENQUIRY
RED APPLES PURCHASING ENTRY
RED APPLES PURCHASING ENQUIRY
RED APPLES REPORTING
YELLOW PEARS ADMIN
YELLOW PEARS CASH ENTRY
YELLOW PEARS CASH ENQUIRY
YELLOW PEARS GL ENTRY
YELLOW PEARS GL ENQUIRY
YELLOW PEARS PAYABLES ENTRY
YELLOW PEARS PAYABLES ENQUIRY
YELLOW PEARS PURCHASING ENTRY
YELLOW PEARS PURCHASING ENQUIRY
YELLOW PEARS REPORTING

ALL USERNAMES
APOULAIN
ERODRIGUEZ
FSCHMIDT
JSMITH
MJONES
MREILLY
MROSSI
PMURPHY
SKUMAR

REPORT TO BE FILTERED OUT/FLAGGED:
USERNAME RESPONSIBILITIES
MJONES GREEN APPLES ADMIN
MJONES GREEN APPLES CASH ENTRY
MJONES GREEN APPLES CASH ENQUIRY
MJONES GREEN APPLES OTHER ENTRY
MJONES GREEN APPLES OTHER ENQUIRY
MJONES GREEN APPLES PAYABLES ENTRY
MJONES GREEN APPLES PAYABLES ENQUIRY
MJONES GREEN APPLES PURCHASING ENTRY
JSMITH GREEN APPLES PURCHASING ENQUIRY
JSMITH GREEN APPLES REPORTING
JSMITH GREEN APPLES
JSMITH GREEN PEARS ADMIN
MREILLY GREEN PEARS CASH ENTRY
MREILLY GREEN PEARS CASH ENQUIRY
MREILLY GREEN PEARS GL ENTRY
MREILLY GREEN PEARS GL ENQUIRY
MREILLY GREEN PEARS PAYABLES ENTRY
MREILLY GREEN PEARS PAYABLES ENQUIRY
MREILLY GREEN PEARS PURCHASING ENTRY
MREILLY GREEN PEARS PURCHASING ENQUIRY
MREILLY GREEN PEARS REPORTING
PMURPHY YELLOW PEARS PURCHASING ENQUIRY
SKUMAR RED APPLES CASH ENTRY
SKUMAR RED APPLES CASH ENQUIRY
SKUMAR RED APPLES OTHER ENTRY
APOULAIN RED APPLES OTHER ENQUIRY
APOULAIN RED APPLES PAYABLES ENTRY
APOULAIN RED APPLES PAYABLES ENQUIRY
APOULAIN RED APPLES PURCHASING ENTRY
APOULAIN RED APPLES PURCHASING ENQUIRY
APOULAIN RED APPLES REPORTING
APOULAIN YELLOW PEARS ADMIN
APOULAIN YELLOW PEARS CASH ENTRY
FSCHMIDT YELLOW PEARS CASH ENQUIRY
FSCHMIDT YELLOW PEARS GL ENTRY
FSCHMIDT YELLOW PEARS GL ENQUIRY
FSCHMIDT YELLOW PEARS PAYABLES ENTRY
FSCHMIDT YELLOW PEARS PAYABLES ENQUIRY
MROSSI YELLOW PEARS PURCHASING ENTRY
MROSSI YELLOW PEARS PURCHASING ENQUIRY
MROSSI YELLOW PEARS REPORTING
MROSSI GREEN APPLES OTHER ENQUIRY
MROSSI GREEN APPLES PAYABLES ENTRY
ERODRIGUEZ GREEN APPLES PAYABLES ENQUIRY

What I'm trying to get is a list of users who have only "enquiry" responsibilities, not "enquiry" and something else. (i.e., only ERODRIGUES and PMURPHY from my list abroad would make it in my extract.

I've tried with a few pivot tables - close, but no cigar. What's the best way of doing this?

Thanks.

 

[combo]

With pivot table:

You need some helper columns if your report is not in three columns alike: ERODRIGUEZ|GREEN APPLES PAYABLES|ENQUIRY.
Assuming all is in one column A:
- USERNAME helper column formula: =LEFT(A2,FIND(" ",A2)-1)
- ENQUIRY helper column, formula: =NOT(ISERROR(FIND("ENQUIRY",A2)))

For single column input, the three column table input for pivot table:
[pre]USERNAME RESPONSIBILITIES USERNAME ENQUIRY
MJONES GREEN APPLES ADMIN MJONES FALSE
MJONES GREEN APPLES CASH ENTRY MJONES FAlSE
MJONES GREEN APPLES CASH ENQUIRY MJONES TRUE[/pre]

Next, pivot table with USERNAME in rows, ENQUIRY in columns, USERNAME RESPONSIBILITIES as data with count as aggregation (default).
When any of TRUE/FALSE cells, selected, it is possible to add calculated item to ENQUIRY, I named it FALSE_1, formula: =FALSE +TRUE -TRUE
After this, pivot table without totals looks like:

The calculated item is necessary to get empty fields.

After selecting FALSE_1 only (counts) and applying values filter to USRERNAME (Count of USERNAME RESPONSIBILITIES =0):

combo

 

[Livia8]

Thanks Combo, that's very useful - at the moment I've two columns, the first with the usernames and the second with the full responsibility name, e.g. GREEN APPLES CASH ENQUIRY, but I can split the latter to have ENQUIRY in a separate column.

Just wondering, if a user had both GREEN APPLES CASH ENQUIRY and, let's say, YELLOW PEARS GL ENQUIRY, that would also be picked up with with the others with only one enquiry and nothing else, wouldn't it?

Thanks.

 

[Livia8]

Actually, I might ask the same question in the SQL forum, I wonder if I can do this with a SELECT, would it make it easier to alter the parameters?

 

[combo]

The above logic returns zero FALSEs, i.e. persons with ENQUIRY only. No problem to add test row to the data, refresh table and check the result. For me TRUE/FALSE column processes properly, in case of problems other formula with text output can be used.

combo

 

[Livia8]

Thanks Combo, I'm certain this works well, my only concern is that for now I've been asked to find only "ENQUIRY" users, but I wouldn't be surprised if, for instance, I was asked then to find "ADMIN"+"ENQUIRY" users, as well as "ENQUIRY" only. Wouldn't that complicate things?

 

[combo]

I would rather switch to Power Query (excel 2016+ queries). With two input excel tables: report data and single column TO FIND criteria table all can be processed by Power Query engine and returned to output table.

Things may complicate if you plan to exclude criteria sets, maybe a good starting point would be criteria table:
[pre]CRITERIA ID TO FIND
1 ENQUIRY
2 ADMIN
2 ENQUIRY[/pre]
with 'OR' for different CRITERIA ID and 'AND' within CRITERIA ID, and find logic to extract USERNAME + CRITERIA ID.


combo

 

[Livia8]

Many thanks Combo. Power Query is outside of my comfort zone (I've never used it before), but no time like the present to learn something new!

 

[Livia8]

Combo, I was just thinking - if I were to add another column, filter the report by responsibility name containg "ENQUIRY" OR (e.g.) "ADMINISTRATOR" and/or whatever else they decide, and add a flag in the extra column, I could then apply your pivot table to that column. I probably wouldn't get a breakdown by responsibility (or maybe I would? I'll have to try it), but at least I'd get an overall total. What do you think, would that work? Thanks.

 

[combo]

Pivot table has limitations. It is best for summarizing data. You can try and check the results. Anyway, it may be hard to extend its functionality.

It is not clear for me what you meant by "I was asked then to find "ADMIN"+"ENQUIRY" users, as well as "ENQUIRY" only". Initially you planned to extract users with "ENQUIRY" only in functionalities (1). In the latter case, for "ADMIN"+"ENQUIRY" users, either "ADMIN" or "ENQUIRY" only (2)? And for ""ADMIN"+"ENQUIRY" users, as well as "ENQUIRY" only" - any case of 1 or 2? Or get a list for 1, get another list for 2, and maybe build criteria 3 and search for the output?

combo

 

[Livia8]

Thanks Combo. The first request I got was to identify only users who only had "enquiry" responsibilities, so just one parameter. Then I was asked to add another parameter, e.g. "reporting", so now I have to identify uses who have only "enquiry" responsibilities, only "reporting" responsibilities or only "enquiry" AND "reporting" responsibilities, but nothing else.

I've colour coded the data: I need to get only the info in green. The lines in orange have to be left out of the count because the users also have responsibilities outside my parameters.

 

[Livia8]

I may have had a lightbulb moment - if I extract from the main report two lists, one with ONLY users with my parameters, and another with ONLY users without my parameters, I can then do a VLOOKUP and exclude the names on the the first list that match those on the second. I'll give it a shot and report back.

 

[combo]

I did a quick Power Query query in excel, the criteria table has a set of criteria, if a user has entries only from a given set, he is returned in an Output table together with the criteria ID that he match (if the logic behingd is ok).
All to do is to fill two first tables and refresh the third. For more than one entry in criteria I put the logic: if all of responsibilities contains any of TO_FIND in criteria, the user is returned. So for "ENQUIRY"+"REPORTING" if someone has one entry with "REPORTING", it is returned too. It is not the case you described, I had worked it out before I saw your post.

I made some comments and descriptions in the query, also renamed most of the steps recorded. You can edit the query and check. Power query is in excel since 2016 version, for excel 2013 it is a separate MS add-in and may be downloaded and installed, not available in earlier excel versions.



combo

 

[Livia8]

Thanks Combo - sorry for not replying sooner - I won't get a chance to go back to work on this before tomorrow at the earliest, but I will try it and report back. I do appreciate all the help.