I am trying to create a master list (Column B) of categories from a column (A) that has many multiples of each categories.
https://docs.google.com/spreadsheets/d/162Yb87mLHDEOhvtiyHK7KtdDYIfJ9sHtHlA0_4CO0zY/edit?usp=sharing
The details depend, of course, on some assurances you can probably give, butdidn’t: Your example has contiguous ranges only of equal category content e.g, but you didn’ state this as assured. …
The by far most efficient way surely isi the creation of a pivot table (DataPilotTable) with the ‘Category’ as the only RowField and with no DataField at all.
However, you need to trigger the refresh manually when needed, or to organise a kind of AutoCalculate bevaiour for the PT by user code.
The task can also be done by siome user code I can provide. Disadvantages of relying on user code generally and of array function needing to lock an output range specofically must be accepted.
Finally the task can be done introducing some helper columns and accepting rather poor efficiency due to the fact that many cells will need to lool up ranges (the larger the more you go down in rows).
The first two solutions are demonstrated in Sheet1, the third one in Sheet2 of this attached SSD.
(Don’t use google sheets. If they succeed they will impose new insane compatibility requirements on free and open software. Stay aware of the fact that only the supporters of free and open software are acting on your interests.)
D’oh! I have duplicate files on my categories - LIbreOffice Calc and Google Sheets - and I “shared” my Sheets instead of attaching my Calc SS. I apologize.
Your formula in Sheet 2 worked. I had to rename some of my categories because they contained +, $. Long ago during my career as Inventory Manager I used a formula which I thought was simpler but after eleven years of retirement I don’t remember much.
I will heed your advise about not using Google Sheets.
Thanks so much for the assistance.