I have a sheet where I am counting the results of individual sheets, and then comparing with a new data source which is refreshed weekly. Using "TEXT.CONTAINS" I was able to indicate TRUE or FALSE on each of my QMS Entities. Now my numbers do not match, and I know exactly why. The Stakeholders wanted counts by each of the QMS Entities. Well, MOST of the Documents correspond with more than one QMS Entity. I would like to be able to COUNT my "TRUE" response". The table name is BLUE and the Field names (QMS Entities) are GREEN. The colored portions are the exact names.
Table: RefreshedMaster
QMS1: Israel, Caesarea
QMS2: CA, Irvine [CSF]
QMS3: TX, Fort Worth
QMS4: CO, Louisville
QMS5: MA, Littleton
QMS6: France, Lyon
Easier to provide a solution if you upload the worksheet instead of picture which cannot be manipulated. I think without actually applying to data that if you unpivot the data and then group by may be a starter.
I tried to do that, however, everything (multiple sheets) are all part of everything. So when I attempted to do that with the existing queries, everything broke as I attempted to remove pieces that were not relevant to my question.
Perhaps for the sake of this question, I will remove the queries, and just provide a sample workbook with the data values. Let me go create that.
Okay, I have attached a sample workbook. The new column would be in Column V, and I planned on naming it "Multiple Entities". This new column will COUNT the number of TRUE instances in Columns M - R. And it must be a Power Query formula, not an Excel formula, as this file is refreshed weekly. And just as FYI, these columns (M-R) were captured from column K using "TEXT.CONTAINS" in Power Query
I was attempting to figure this one out on my own. And while there is likely more more than one solution, I figured out this approach. I created 6 new conditional columns. Each of one of the 6 QMS Entities. I then created a calculated field using Standard > Addition to add up these 6 columns. I am sure there is a formula that I could have used rather than all those extra columns (I removed my count columns from the final version), but I did not know how. If there is a way, I would love to know to either update this or for a future project.
It would seem to me that it would be simpler to just parse the Applicable QMS Entities column into new rows split on the semicolon, so that you get a row for each entity. You can then summarize those in whatever way you need (eg a pivot table).
@Velouria,
The thing is that the Applicable QMS Entities contains multiple irrelevant QMS Entities. For example, one randomly chosen document has:
CA, Irvine [CSF];CA, Irvine [NV];CA, Northridge;CO, Boulder [SI];CO, Louisville;CT, North Haven [SI];Dominican Republic, San Isidro;FL, Jacksonville;FL, Miami Lakes;France, Trevoux;MA, Littleton;MN, Mounds View [CAS];MN, Mounds View [CRM];MN, Mounds View [CS];MN, Plymouth [PVH];MN, Plymouth [SI];Puerto Rico, Ponce;TX, Fort Worth;MN, Mounds View [SHA];CA, San Juan Capistrano
And for that document, ONLY
CA, Irvine [CSF]; TX, Fort Worth; CO, Louisville; MA, Littleton
are actually relevant. Unless there is a way to do a Concatenation based on TRUE as a value, then add "; " to separate each one. And Ignore blanks, and not have "; " except after TRUE responses. I could then parse the data like you suggested, which would be of the. I attempted to do this, however I was unable to get it to work properly.
I don't see the problem. Once you have it split into entities, you can filter out whatever you are interested in.
