Creating an automatically updating unique distinct list from a column

[gaggia]

Hey Excel Forum,

I've got a master list of source data that's associated with a couple levels of categories in adjacent columns. My intention is to create a separate sheet for each of the levels of categories where I can display aggregate statistics for my source data contained within these categories, each sheet dealing with a separate level, and for them to update automatically when the master list is updated. In order to do so, I understand that each of these sheets needs to display a unique distinct list of categories within my chosen category level. As an example, my data appears like this:

Please Login or Register  to view this content.

If I were to create a sheet summarizing data for protein, I would want to a list of the various types within (fish, poultry) without repeating duplicates. I named the column with the various proteins in my master list as protein_list. I found a solution online that had me use an array formula as such:

=INDEX(protein_list,MATCH(0,COUNTIF($A$1:A3,protein_list),0))

While this worked, it caused Excel to crash repeatedly and slowed everything down to a crawl. At other times, I get a warning of a circular formula. Also, cells were slow to update, and I needed to force a full cell update every time for it to work. Is there a better way of doing this?

[JohnTopley]

Attach a sample workbook (not image).

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[leelnich]

Hi gaggia- That is a standard formula for listing unique values. Could you please show your Name Definition for protein_list, I'm betting you included the entire column?