SOLVED
Home

Creating a function for identifying repeated data?

%3CLINGO-SUB%20id%3D%22lingo-sub-472373%22%20slang%3D%22en-US%22%3ECreating%20a%20function%20for%20identifying%20repeated%20data%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-472373%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20new%20to%20creating%20custom%20functions%2C%20and%20I%20was%20wondering%20if%20there%20was%20a%20way%20to%20create%20a%20Function%20that%20could%20Identify%20how%20many%20times%20a%20single%20data%20value%20repeats%20itself%20in%20a%20column%2C%20by%20counting%20how%20many%20times%20the%20value%20shows%20up%20in%20a%20Column%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExample%3A%3C%2FP%3E%3CP%3ESay%20I%20have%20a%20column%20with%20a%20Random%20number%20of%20Apples.%20Peaches%2C%20and%20Bananas%2C%20but%20I%20need%20to%20know%20how%20many%20of%20each%20I%20have%2C%20so%20I%20put%20in%20my%20formula%20in%20the%20neighboring%20column%2C%20and%20it%20counts%20how%20many%20times%20it%20finds%20the%20values%20in%20the%20said%20neighboring%20column.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eit%20would%20look%20something%20like%20once%20finished%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20238px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F109487i13697E40AF04BE98%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Capture.PNG%22%20title%3D%22Capture.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20let%20me%20know%20if%20anyone%20has%20an%20idea%20of%20how%20to%20create%20this.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%2C%3C%2FP%3E%3CP%3ESimon%20Pease%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-472373%22%20slang%3D%22en-US%22%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-472511%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20function%20for%20identifying%20repeated%20data%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-472511%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F323493%22%20target%3D%22_blank%22%3E%40Simonpease%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUntested.%3C%2FP%3E%3CPRE%3E%3DCOUNTIF(B%242%3AB2%3BB2)%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-472512%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20function%20for%20identifying%20repeated%20data%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-472512%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDid%20not%20work%20sadly%20%3A(%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-472685%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20function%20for%20identifying%20repeated%20data%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-472685%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F323493%22%20target%3D%22_blank%22%3E%40Simonpease%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20amended%20the%20formula.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-472700%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20function%20for%20identifying%20repeated%20data%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-472700%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESweet%20it%20worked%20this%20time.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Simonpease
New Contributor

Hello,

 

I am new to creating custom functions, and I was wondering if there was a way to create a Function that could Identify how many times a single data value repeats itself in a column, by counting how many times the value shows up in a Column?

 

Example:

Say I have a column with a Random number of Apples. Peaches, and Bananas, but I need to know how many of each I have, so I put in my formula in the neighboring column, and it counts how many times it finds the values in the said neighboring column.

 

it would look something like once finished:

Capture.PNG

 

Please let me know if anyone has an idea of how to create this.

 

Thank you,

Simon Pease

4 Replies
Solution

@Simonpease 

 

Untested.

=COUNTIF(B$2:B2,B2)

 

EDIT: Replaced semicolon with comma. 

@Detlef Lewin 

Did not work sadly :(

@Simonpease 

I amended the formula.

 

@Detlef Lewin 

Sweet it worked this time.

 

Thank you!

Related Conversations
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
202 Replies