Listing recurring values in a column alphabetically and automatically AND counting the occurrence of each value

Help101

New Member
Joined
Apr 24, 2014
Messages
30
Is there a non-array formula and not using VBA script that would automatically pick recurring values in Column A, list them alphabetically (or alphanumerically) in Column B, and count how many times these recurring values appeared in Column A and list these counts in Column C? Thanks.

..........A..........B..........C
1........CC.......A2A......2
2........C..........CC.......3
3........A2A
4........BB1
5........A2A
6........CC
7........CC
 
Hi Aladin,

Thank you very much for such concise formulas. It took me a while to reply because I have been testing your formulas. I got them to work on the simple data set above. However, I got error messages when I expanded and tested these on an actual and very large data set. The error messages appear when I edit the dynamic data set or when I reposition the table or I insert rows or columns to the worksheet.

No idea what you mean by "very large data set" and correlative "error messages".

These are my follow-up questions:

1) Does the data source have to be in the same worksheet as the formulas or can I refer to the data source in another worksheet by naming the range RData in the Name Manager?

Data and formulas do not need to be on the same sheet.

2) What did you mean by the underlined portion in your statement "1) A2:A9 is defined in Name Manager as RData (If need be, the definition can also be dynamic.)"?


RData definition is static, i.e., it does not expand or shrink when items/records are added or deleted.

3) I noticed that when using the Name Manager to delineate the source data, absolute reference ($) signs are added to the range reference. Is this ok?

Yes.

4) Your formula for C2, =IF(ROWS($C$2:C2)<=$B$1,INDEX(Data,MATCH(0,COUNTIFS(Data,"<"&Data)-SUM(COUNTIFS(Data,"="&C$1:C1)),0)),""), refers to the range C$1:C1 which in the above table is occupied by the heading "Sorted". Is this ok?

Yes.

5) Do I have to CSE, where required, every time there is a change in the data set?

Yes.

6) I noticed that I had to be careful with the =OFFSET(Sheet1!$B$2,0,0,Sheet1!$B$1) part because I do have to use other worksheets for other data sets in the same workbook. Instead of using just one formula, I created several of these and labeled these appropriately such that the appropriate component where the "Sheet1!" part of your original formula is used. Is this ok?

This formula is the definition of Data with Scope set to Workbook in the Name Manager. So, there can be just one such definition with that scope.

7) Do your formulas work only if the table and formula are positioned as you showed above? What happens if additional rows or columns are added? Would the formula automatically adjust?
RData needs to be dynamic, not static.

If the A range consists solely of text, change the definition to:

=Sheet1!$A$2:INDEX(Sheet1!$A:$A,MATCH(REPT("z",255),Sheet1!$A:$A))

For the count formula, I modified it a bit so that it does not return a count for the number of blanks cells after the last count. I used "=if(B2="","",COUNTIFS(RData,C2)".

That's okay.

Thanks again. I appreciate your help very much. I am not an expert and I am learning as I go along. I just learned how to use the Name Manager because you mentioned it earlier.

Cheers!

You are welcome.

See: https://dl.dropboxusercontent.com/u...ND counting the occurrence of each value.xlsx
 
Upvote 0

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
7) Do your formulas work only if the table and formula are positioned as you showed above? What happens if additional rows or columns are added? Would the formula automatically adjust?
RData needs to be dynamic, not static.

If the A range consists solely of text, change the definition to:

=Sheet1!$A$2:INDEX(Sheet1!$A:$A,MATCH(REPT("z",255),Sheet1!$A:$A))

Thanks Aladin,

My data set is alphanumeric. Could you please show me the RData formula/definition that is for both dynamic and alphanumeric data? I tried your revised formula above and it did not work. As you said, it is solely for text data.

Thanks again.
 
Upvote 0
7) Do your formulas work only if the table and formula are positioned as you showed above? What happens if additional rows or columns are added? Would the formula automatically adjust?

Thanks Aladin,

My data set is alphanumeric. Could you please show me the RData formula/definition that is for both dynamic and alphanumeric data? I tried your revised formula above and it did not work. As you said, it is solely for text data.

Thanks again.

See: https://dl.dropboxusercontent.com/u...ND counting the occurrence of each value.xlsx
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,148
Members
448,552
Latest member
WORKINGWITHNOLEADER

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
Back
Top