Feb 27, 2023

How can I summarize a list of unused names?

How can I indicate and eliminate names that are not shown on a chart here (see img att) based on a mass list of names on a separate spreadsheet?


Locked
This question is locked and replying has been disabled.
Community content may not be verified or up-to-date. Learn more.
Recommended Answer
Feb 28, 2023
Hi Monchi,  thanks for sharing that sheet.  

And sorry, without a sheet to test it on, I had an error in my formula.  You can try this:

=SORT(UNIQUE(FILTER(FLATTEN(C7:U91),
                    FLATTEN(C7:U91) <>"")))

Then I think you now have to type in some sample names, in Main Chart, and put them on the same rows as in your real sheet.

You don't need to do too many, maybe ten or so, spread around the chart, just like in your real sheet.
Original Poster Monchi marked this as an answer
Helpful?
Recommended Answer
Feb 28, 2023
Hi Monchi,

Yes, I think that with a few formulas we can filter the list of mass names, to report only the names that are not already on the chart, if that is what you are trying to do.

This would be much easier, if you shared a sample sheet, with dummy names.  Please take a look at this post about Best Practices When Asking a Question and share a sample sheet that has the permissions set for “Anyone with link” can EDIT?  This will also guide you in avoiding sharing any sensitive data.  

Or this blank sheet maker​​​​​​​ can create a shareable sheet for you - just add your sample data.

But the approach would be roughly something like the following:

=SORT(UNIQUE(FLATTEN(FILTER(C7:U91,C7:U91<>""))))

That would collect the list of used names in the chart.  
We would then use a COUNTIF to compare that list against the names in your master list, and filter the master list to just the names that weren't produced from the first formula.

But you also have lots of hidden rows in your chart.  Depending on what is in them could affect the process.

And are you manually entering the names in the chart, or are they produced by formulas?

Cheers!
Gill
Original Poster Monchi marked this as an answer
Helpful?
All Replies
Recommended Answer
Feb 28, 2023
Hi Monchi,

Yes, I think that with a few formulas we can filter the list of mass names, to report only the names that are not already on the chart, if that is what you are trying to do.

This would be much easier, if you shared a sample sheet, with dummy names.  Please take a look at this post about Best Practices When Asking a Question and share a sample sheet that has the permissions set for “Anyone with link” can EDIT?  This will also guide you in avoiding sharing any sensitive data.  

Or this blank sheet maker​​​​​​​ can create a shareable sheet for you - just add your sample data.

But the approach would be roughly something like the following:

=SORT(UNIQUE(FLATTEN(FILTER(C7:U91,C7:U91<>""))))

That would collect the list of used names in the chart.  
We would then use a COUNTIF to compare that list against the names in your master list, and filter the master list to just the names that weren't produced from the first formula.

But you also have lots of hidden rows in your chart.  Depending on what is in them could affect the process.

And are you manually entering the names in the chart, or are they produced by formulas?

Cheers!
Gill
Original Poster Monchi marked this as an answer
Mar 1, 2023
The formula worked great to list down all the names from the chart - Thank you.

A bit lost on what formula to use to compare the list (on service & master list) and display only the unused names in the "out of service" section.

(img attached)



Mar 1, 2023
You haven't put any names in the chart, Monchi, so I added four, from the Master List.
See tab GK.Help.

This formula in AH6 lists the names from the Master List that are not anywhere in the chart.

={"Names not used";ARRAYFORMULA(
FILTER('Master Name List'!B5:B,
  COUNTIF(SORT(UNIQUE(FILTER(FLATTEN(C7:AG48),
                             FLATTEN(C7:AG48) <>"",
                             FLATTEN(C7:AG48) <>" DISPATCH",
                      ISTEXT(FLATTEN(C7:AG48))))),
           'Master Name List'!B5:B)=0,
           'Master Name List'!B5:B<>""))}

Is that what you want?  

Or do you want the names highlighted on the Master List if they don't appear in the chart?
That is almost the same formula, but just done as a conditional fromatting rule.

Let me know.
Mar 1, 2023
Works perfect.

Thank you so much. Many kudos.
Mar 1, 2023
Glad I could help, Monchi!

Stock paragraph:
To improve the Forum quality, when you've received a response that answers your question, please observe these forum courtesies;
 • Leave your demo sheet shared as part of this solution's archive,
 • Click Recommend on the post that best addressed your question, and
 • Post again soon!

Best,
Gill
false
16873715448718462569
true
Search Help Center
true
true
true
true
true
35
Search
Clear search
Close search
Main menu
false