How can I create a formula that counts the number of duplicates, returns the value in a cell of my choosing, and then deletes the duplicates?

Question

I've got a list that, after I sort it, looks like this:

rough list

I need a formula that will return the number of times each entry is listed and delete all the duplicates. Typically, I simply count the number with my eyes or highlight those that are identical and record the "count" calculated at the bottom of the spreadsheet. Then I go back through and delete all duplicates, which is a bit of a tedious process and I can't help but think that there is an easier way to do this.

I have tried using the filter feature to return entries that don't have a number in the adjacent column, but when I go to remove the duplicates, entries are shifted up so that the numbers I have already typed in by hand get mismatched. Truth be told, I have a feeling that there's a way to have Excel count and record the number of times each entry appears (without even having to sort through it first), but I'm weak on logical formulas and the searches I've done of the world wide web have resulted in related queries, but don't really help me figure out how to do this.

Currently I use a Microsoft 365 version of Excel.

Ultimately, I want the list to look like this:

finished list

but with less effort.

For those who want even more detail, read on for the bigger picture. As someone interested in eating for health, I track my consumption down to the micronutrient to ensure I'm getting adequate amounts and not exceeding any recommended limits. Once a week, I review this data and identify nutrients I'm not getting enough of. I have a master spreadsheet of sorts that lists the foods that are richest in various nutrients. It looks like this:

list of nutrients and the foods richest in them

As you can see, certain foods appear on more than one list, and because I can only eat so much food in a day, I want to concentrate on those that give me more bang for the buck because they appear on more than one list. Typically, I just copy and paste the columns for which deficiencies were identified into a single column and then sort it alphabetically to get a count. I add this detail because I have a feeling that a true SuperUser of Excel wouldn't even have to bother with the clunky copy and paste that I do and could just pull the number from this master spreadsheet of foods by nutrients.

Thanks in advance to anyone who can help me use Excel a bit more efficiently.

Answer 1

My proposal regards only the first part (sorted list of products):

You can place your list in an Excel Table, e.g. Table1 and refer to this list as Table1[List].
In columns C and D you get a list of unicates with their counts.
Formula in C2 : =UNIQUE(Table1[List])
Formula in D2 : =COUNTIF(Table1[List],C2#)
As a next step you can sort these two columns as shown in columns F and G.
Formula in F2 : =SORT(HSTACK(C2#,D2#),2,-1)

Answer 2

If you have the following data and using MS365 then you can do the following methods:

Fiber	Calcium	Iron	Magnesium	Zinc	Copper	Manganese	Iodine	Selenium	Fluoride
navy beans	milk	fortified cereals	pumpkin seeds	beef	crab	wheat germ	baked potato	Brazil nuts	black tea
lentils	yogurt	wheat bran flakes	almonds	pumpkin seeds	lobster	sweet potatoes	milk	sunflower seeds	shrimp
pinto beans	parmesan	grits	spinach	sunflower seeds	shiitakes	collard greens	shrimp	tuna	raisins
chickpeas	Swiss	granola	black beans	lentils	white buttons	peas	turkey	salmon	raspberries
lima beans	cottage cheese	shredded wheat	salmon	turkey	portabellas	pine nuts	navy beans	lobster	cooked oatmeal
avocado	spinach	cream of rice	yogurt	eggs	creminis	hazelnuts	eggs	shrimp	beer
pumpkin seeds	collard greens	beef	milk	shiitakes	sweet potatoes	pecans		pork tenderloin	red wine
sesame seeds	turnip greens	ground beef	avocado		turnip greens	oats		beef	hard cider
almonds	broccoli	beef tenderloin	bananas		baked potato	lima beans		lamb	grits
pistachios	black-eyed peas	buffalo sirloin			spinach	chickpeas		chicken	black bean soup
pecans	navy beans	ground bison			sunflower seeds	navy beans		turkey	baked potato
sunflower seeds	salmon	porterhouse			pumpkin seeds	lentils		navy beans	carrots
acorn squash	acorn squash	beef stew meat			Brazil nuts	spinach		pinto beans	spinach
hubbard squash	lobster	pork tenderloin			walnuts	pineapple		lima beans	cantaloupe

---

• Using GROUPBY() function applicable to MS365 --> Office Insiders only.

=LET(
     _Data, A3:J16,
     _SingleCol, TOCOL(_Data,1),
     GROUPBY(_SingleCol,_SingleCol,ROWS,,0,-2))

---

Or, Using BYROW() or MMULT()

=LET(
     _Data, A3:J16,
     _SingleCol, TOCOL(_Data,1),
     _Uniq, UNIQUE(_SingleCol),
     _Counts, MMULT(N(_Uniq=TOROW(_SingleCol)),SEQUENCE(ROWS(_SingleCol))^0),
     HSTACK(_Uniq,_Counts))

---

=LET(
     _Data, A3:J16,
     _SingleCol, TOCOL(_Data,1),
     _Uniq, UNIQUE(_SingleCol),
     _Counts, BYROW(_Uniq,LAMBDA(x,SUM(--(x=_SingleCol)))),
     HSTACK(_Uniq,_Counts))

---

• All the above formulas shown gives the number of counts for each unique records.
• Using TOCOL() function to convert the multiple ranges data into single column array excluding the duplicates.
• Method 1 --> In the first method, used GROUPBY() function which is designed to group, aggregate and sort as per the fields we specify. However, the said function is in Office-Insiders hence until and unless you have enabled the option, this wont work and show as #NAME! error.
• Method 2 --> Also starts with the LET() function, which makes easier to read and reduces the uses of redundant ranges/formulas.
• Like the first method, it first applies the TOCOL() function, then uses UNIQUE() function to return unique values, and lastly uses MMULT() function to return the counts which is the matrix product of the two given arrays.
• Finally, using HSTACK() to combine both the arrays returned using UNIQUE() and MMULT() function to return the desired output.
• Method 3 --> Starts with the same steps as above except it uses BYROW() function to get the matching counts of each items.

---

However, if you find these methods as complicated then also you use the simplest one shown below as :

---

• Firstly, use TOCOL() & UNIQUE() function to return the multiple ranges into one array excluding the empties and duplicates. And place in a cell, lets say in L2

---

=UNIQUE(TOCOL(A3:J16,1))

---

• Finally, enter the following function to get the counts. Remember that i have taken the data range as A3:A16 which you may need to change as per your suit. The following doesn't needs to be filled down, as it will spill. Where L2 is the formula returned from above using TOCOL() and UNIQUE()

---

=COUNTIFS(A3:J16,L2#)

---

If you are still reluctant to follow the above, then you can use POWER QUERY which is available from Excel 2010+ onwards for ,available in Windows Excel 2010+ and Excel 365 (Windows or Mac)

---

To use Power Query follow the steps:

• First convert the source ranges into a table and name it accordingly, for this example I have named it as Table1

---

• Next, open a blank query from Data Tab --> Get & Transform Data --> Get Data --> From Other Sources --> Blank Query

---

• The above lets the Power Query window opens, now from Home Tab --> Advanced Editor --> And paste the following M-Code by removing whatever you see, and press Done

---

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Value"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

---

• Lastly, to import it back to Excel --> Click on Close & Load or Close & Load To --> The first one which clicked shall create a New Sheet with the required output while the latter will prompt a window asking you where to place the result.

---

^{Note: Formula cannot delete duplicates, instead it extract values when placed in a cell following specific conditions. Like I have shown above. You basically need UNIQUE() , TOCOL() & COUNTIF() or COUNTIFS() function to get the desired output}

---

With your Rough List simply place this formula in any blank cell, where List is the name of the Table:

---

=LET(
     _Uniq, UNIQUE(List),
     _Output, SORT(HSTACK(_Uniq, COUNTIF(List,_Uniq)),2,-1),
     VSTACK({"Nutrients","Counts"},_Output))

---

It is easier with GROUPBY() function if applicable:

=GROUPBY(List,List,ROWS,,0,-2)

---