Creating a List of Duplicate Values (ignoring blanks)

roseriver

New Member
Joined
Jan 2, 2020
Messages
21
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,359
Office Version
  1. 365
Platform
  1. Windows
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
CDEFGH
4itemuniqueunique, no blank cellsduplicates, blank cells excludedjust once, blank cells excluded
51212121213
61213131214
71314141715
81415151716
915161617Easter
101617171718
1117ChristmasChristmasChristmas19
1217EasterEasterChristmas-100
131701820
1417181920
15Christmas1920
16Easter20-100
17Christmas-100
18
19
20
21
2218
2319
2420
2520
26-100
Sheet63
Cell Formulas
RangeFormula
E5E5=UNIQUE(C5:C26)
F5F5=UNIQUE(FILTER(C5:C26,NOT(ISBLANK(C5:C26))))
G5G5=FILTER(C5:C26,COUNTIFS(C5:C26,C5:C26)>1)
H5H5=UNIQUE(C5:C26,,1)
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
Could you give us a small set of dummy sample data for the 'ITEMS' column (including some blanks) and the corresponding expected results?
 

Watch MrExcel Video

Forum statistics

Threads
1,128,172
Messages
5,629,134
Members
416,365
Latest member
dof

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top