Pivot Table Count Unique or Similar Values

%3CLINGO-SUB%20id%3D%22lingo-sub-2459897%22%20slang%3D%22en-US%22%3EPivot%20Table%20Count%20Unique%20or%20Similar%20Values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2459897%22%20slang%3D%22en-US%22%3EHi%2C%20I%20was%20wondering%20if%20there%E2%80%99s%20a%20way%20to%20create%20a%20pivot%20table%20that%20counts%20unique%20values.%20I%20have%20tried%20adding%20to%20data%20model%20and%20selecting%20distinct%20count.%20This%20works%2C%20but%20then%20I%20run%20into%20a%20problem%20where%20two%20values%20are%20counted%20separately%20because%20they%20are%20written%20slightly%20different%2C%20but%20should%20be%20categorized%20together%20as%20one%20count.%20Normally%20I%20would%20try%20to%20group%20them%20together%20but%20then%20the%20group%20count%20would%20be%202%20instead%20of%201.%3CBR%20%2F%3E%3CBR%20%2F%3EExample%3A%3CBR%20%2F%3EGL109%3CBR%20%2F%3EGL109-title%3CBR%20%2F%3E%3CBR%20%2F%3EThese%20two%20values%20are%20actually%20the%20same%2C%20however%20they%20are%20written%20differently%20so%20excel%20counts%20them%20as%202.%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2459897%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2460637%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20Table%20Count%20Unique%20or%20Similar%20Values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2460637%22%20slang%3D%22en-US%22%3EHi%2C%3CBR%20%2F%3E%3CBR%20%2F%3ETry%20adding%20another%20column%20that%20makes%20GL109%20and%20GL109-title%20the%20same.%3CBR%20%2F%3Ee.g.%20%3DLEFT(cellwithGL109-title%2C%205)%3CBR%20%2F%3E%3DGL109%3C%2FLINGO-BODY%3E
Occasional Visitor
Hi, I was wondering if there’s a way to create a pivot table that counts unique values. I have tried adding to data model and selecting distinct count. This works, but then I run into a problem where two values are counted separately because they are written slightly different, but should be categorized together as one count. Normally I would try to group them together but then the group count would be 2 instead of 1.

Example:
GL109
GL109-title

These two values are actually the same, however they are written differently so excel counts them as 2.
2 Replies
Hi,

Try adding another column that makes GL109 and GL109-title the same.
e.g. =LEFT(cellwithGL109-title, 5)
=GL109

@Mchen95 

 

It's hard to give anything but general advice given the very sparse description of your situation. 

 

One thing you should do, if you haven't already, is to look into the still relatively new UNIQUE function, which can extract from a long list the actual unique values, which can then be counted.

 

IF the aberrations are always following the variations in the example you gave, you could always look just at the first five characters, making use of the LEFT function to strip off the GL109 from "GL109--anything else"  But if that was just an example and not representative of your actual data, you'll need to do something else to clean up the aberrations.

 

Here's a helpful video on UNIQUEhttps://www.youtube.com/watch?v=9I9DtFOVPIg

 

Learn everything about our brand-new Dynamic Arrays and how you can use them to build advanced spreadsheets. Arrays (CSE) have long been present in Excel, bu...