Counting All Duplicate Data In One Column

%3CLINGO-SUB%20id%3D%22lingo-sub-1624227%22%20slang%3D%22en-US%22%3ECounting%20All%20Duplicate%20Data%20In%20One%20Column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1624227%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%20I'm%20wondering%20if%20there%20is%20a%20formula%2Ffunction%20or%20alike%20to%20count%20the%20amount%20of%20times%20data%20appears%20in%20a%20column.%20To%20further%20explain%2C%20I%20have%20a%20parts%20catalogue%20I'm%20currently%20working%20on.%20It%20has%20a%20little%20over%2040%2C000%20items%20in%20it%2C%20all%20pre-organised%20into%20categories.%20I'm%20wanting%20to%20count%20the%20total%20number%20of%20said%20categories.%20Naturally%20these%20categories%20occur%20in%20the%20worksheet%20more%20than%20once%2C%20so%20I%20wanted%20to%20run%20a%20report%20without%20having%20to%20input%20each%20individual%20category%20and%20run%20a%20test%20for%20that%20particular%20word%2Ftitle.%20If%20I%20can%2C%20ideally%20I'd%20like%20to%20have%20it%20record%20all%20different%20values%20in%20the%20column%20in%20a%20new%20worksheet%20or%20table%20and%20then%20record%20how%20many%20times%20each%20appears%20in%20the%20column.%20Is%20this%20possible%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%2C%20Phil%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1624227%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1624298%22%20slang%3D%22en-US%22%3ERe%3A%20Counting%20All%20Duplicate%20Data%20In%20One%20Column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1624298%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F777543%22%20target%3D%22_blank%22%3E%40PhilG1293%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20easiest%20way%20is%20insert%20a%20Pivot%20Table%20based%20on%20your%20data%2C%20drag%20the%20Categories%20field%20into%20the%20Row%20area%20and%20drag%20the%20Categories%20field%20again%20into%20the%20Values%20area.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnother%20way%20is%2C%20import%20your%20data%20into%20Power%20Query%20editor%20and%20then%20use%20Group%20By%20feature%20to%20group%20the%20data%20by%20Categories%20with%20Count%20Rows%20operation%20to%20get%20a%20table%20with%20all%20the%20unique%20categories%20in%20one%20column%20along%20with%20their%20counts%20in%20another%20column%20and%20load%20the%20resultant%20table%20back%20into%20the%20sheet.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hi All, I'm wondering if there is a formula/function or alike to count the amount of times data appears in a column. To further explain, I have a parts catalogue I'm currently working on. It has a little over 40,000 items in it, all pre-organised into categories. I'm wanting to count the total number of said categories. Naturally these categories occur in the worksheet more than once, so I wanted to run a report without having to input each individual category and run a test for that particular word/title. If I can, ideally I'd like to have it record all different values in the column in a new worksheet or table and then record how many times each appears in the column. Is this possible?

 

Regards, Phil

1 Reply

@PhilG1293 

The easiest way is insert a Pivot Table based on your data, drag the Categories field into the Row area and drag the Categories field again into the Values area.

 

Another way is, import your data into Power Query editor and then use Group By feature to group the data by Categories with Count Rows operation to get a table with all the unique categories in one column along with their counts in another column and load the resultant table back into the sheet.