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
 

XOR LX

Well-known Member
Joined
Jul 2, 2012
Messages
4,517
Hi.

May I ask your reasons for specifying a non-array formula?

Regards
 

Help101

New Member
Joined
Apr 24, 2014
Messages
30
Hi XOR LX,

I now COULD do with an array formula! I was trying to edit my post but I could not find the Edit button mentioned in the FAQ.

I was was of the wrong impression that the CSE sequence needed in an array formula would have to be done every time the data is changed. I didn't realize at that time that CSE only needs to be done once and that the results dynamically change with the data entered.

So please, if you have an array formula for this challenge, share it with me. Thanks.

PS: This is the original post and I have requested any moderator to delete a duplicate thread which I inadvertently posted and consolidate replies into one thread.

Cheers!
 

GerryZ

Well-known Member
Joined
Jul 4, 2014
Messages
1,206
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
Hello!
I like this question but I don't understand all of the issue It was nice to see a spreadsheet sample instead to use 1000s of words and formula
Can you post a screenshot with just few rows and colunms with the expected outcome so we can understand better?
I suggest you to post a nice HTML screen-shot with Mr.Excel HTML Maker, if you don’t know how to istall and how to use it watch the video linked in my signature
help us to help you
Thank you in advance!
 

Help101

New Member
Joined
Apr 24, 2014
Messages
30
Thanks GerryZ. Unfortunately, I cannot make changes to our work PC/laptops. I did watch the video you created and shared above. The table I typed in my original post was done on a smart phone. It does not contain any formulas. What I need is simple.

1) Extract values that appear more than once
2) Arrange the extracted values in order
3) Count how many times the repeating values appear

Thanks.
 

XOR LX

Well-known Member
Joined
Jul 2, 2012
Messages
4,517
One way would be to put the the following (non-array) formula in B1:

=SUMPRODUCT(N(FREQUENCY(MATCH(A1:A7,A1:A7,0),ROW(A1:A7)-MIN(ROW(A1:A7))+1)>1))

which will give the expected number of returns and so can be referenced in the main formula so as to avoid a potentially resource-heavy IFERROR set-up.

Then the following array formula** in your first cell of choice:

=IF(ROWS($1:1)>$B$1,"",INDEX(A$1:A$7,MATCH(SMALL(IF(FREQUENCY(MATCH(A$1:A$7,A$1:A$7,0),ROW(A$1:A$7)-MIN(ROW(A$1:A$7))+1)>1,COUNTIF(A$1:A$7,"<="&A$1:A$7)),ROWS(($1:1))),COUNTIF(A$1:A$7,"<="&A$1:A$7),0)))

Copy this formula down (though not the one in B1, which is a one-off) until you start to get blanks for the results.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
 

XOR LX

Well-known Member
Joined
Jul 2, 2012
Messages
4,517
N.B. Your example did not include any blank cells within column A so I did not account for this possibility within my formulas.

Regards
 

Help101

New Member
Joined
Apr 24, 2014
Messages
30
Thanks XOR LX.

Yes, there are no blank cells in Column A. The important thing is that there are no blanks in the column where the values are extracted and sorted in order (alphanumeric).

I will tell you immediately as soon as I have tested your formulas.
 
Last edited:

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,138
Row\Col
A​
B​
C​
D​
1​
2​
SortedCount
2​
CCCCA2A
2​
3​
CA2ACC
3​
4​
A2A
5​
BB1
6​
A2A
7​
8​
CC
9​
CC

1) A2:A9 is defined in Name Manager as RData (If need be, the definition can also be dynamic.)

2) Ivec is defined in Name Manager as referring to:
Rich (BB code):

=ROW(RData)-ROW(INDEX(RData,1,1))+1
3) B1, control+shift+enter (cse), not just enter:
Rich (BB code):

=SUM(IF(FREQUENCY(IF(RData<>"",MATCH(RData,RData,0)),Ivec)>1,1))
4) B2, cse and copy down:
Rich (BB code):

=IF(ROWS($B$2:B2)<=$B$1,INDEX(RData,SMALL(IF(FREQUENCY(IF(RData<>"",
    MATCH(RData,RData,0)),Ivec)>1,Ivec),ROWS(B$2:B2))),"")
5) Data is defined in Name Manager as referring to:
Rich (BB code):

=OFFSET(Sheet1!$B$2,0,0,Sheet1!$B$1)
6) C2, cse and copied down:
Rich (BB code):

=IF(ROWS($C$2:C2)<=$B$1,
   INDEX(Data,MATCH(0,COUNTIFS(Data,"<"&Data)-SUM(COUNTIFS(Data,"="&C$1:C1)),0)),"")
7) D2, copied down:
Rich (BB code):

=COUNTIFS(RData,C2)
The foregoing set up is unaffected by empty/blank cells of the raw data.
 
Last edited:

Help101

New Member
Joined
Apr 24, 2014
Messages
30
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.

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?

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.)"?

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?

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?

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

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?

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?

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)".

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!
 

Forum statistics

Threads
1,082,259
Messages
5,364,100
Members
400,779
Latest member
lumers

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top