Creating a List of Duplicate Values (ignoring blanks)

[roseriver]

Hi,
I am trying to create a list of the duplicate values in a list so that when we are ordering it says we need 50 apples or something similar.
To create this list I have used this method:
for the first row
=IFERROR(INDEX(tbl[ITEMS],MATCH(0,COUNTIF(G26:$G26,tbl[ITEMS])+IF(COUNTIF(tbl[ITEMS],tbl[ITEMS])>1,0,1),0)),"")
for the rest of the rows
=IFERROR(INDEX(tbl[ITEMS],MATCH(0,COUNTIF(G$26:$G27tbl[ITEMS])+IF(COUNTIF(tbl[ITEMS],tbl[ITEMS])>1,0,1),0)),"")

it looks up the values in the column items in my table and then displays a list from G27:G40.


I would like to know how I can make this formula ignore blanks because currently, it is retuning that I have 100 x blanks as an example.

I would appreciate any help or ideas. Thank you.

 

[DRSteele]

I would like to be able to help you with that formula, but I thought I would publish a solution that uses Excel's new functions and dynamic arrays so that users with Excel365 Insider (and soon, everyone) will be able to do this operation.

Book1
	C	D	E	F	G	H
4	item		unique	unique, no blank cells	duplicates, blank cells excluded	just once, blank cells excluded
5	12		12	12	12	13
6	12		13	13	12	14
7	13		14	14	17	15
8	14		15	15	17	16
9	15		16	16	17	Easter
10	16		17	17	17	18
11	17		Christmas	Christmas	Christmas	19
12	17		Easter	Easter	Christmas	-100
13	17		0	18	20
14	17		18	19	20
15	Christmas		19	20
16	Easter		20	-100
17	Christmas		-100
18
19
20
21
22	18
23	19
24	20
25	20
26	-100
Sheet63

Cell Formulas
Range	Formula
E5	E5	=UNIQUE(C5:C26)
F5	F5	=UNIQUE(FILTER(C5:C26,NOT(ISBLANK(C5:C26))))
G5	G5	=FILTER(C5:C26,COUNTIFS(C5:C26,C5:C26)>1)
H5	H5	=UNIQUE(C5:C26,,1)

 

[Peter_SSs]

Could you give us a small set of dummy sample data for the 'ITEMS' column (including some blanks) and the corresponding expected results?