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

[Help101]

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]

Hi.

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

Regards

 

[Help101]

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]

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]

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]

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]

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]

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.

 

[Aladin Akyurek]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][/tr]
[tr][td]

1​

[/td][td][/td][td]

2​

[/td][td]Sorted[/td][td]Count[/td][/tr]

[tr][td]

2​

[/td][td]CC[/td][td]CC[/td][td]A2A[/td][td]

2​

[/td][/tr]

[tr][td]

3​

[/td][td]C[/td][td]A2A[/td][td]CC[/td][td]

3​

[/td][/tr]

[tr][td]

4​

[/td][td]A2A[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

5​

[/td][td]BB1[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

6​

[/td][td]A2A[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

7​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

8​

[/td][td]CC[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

9​

[/td][td]CC[/td][td][/td][td][/td][td][/td][/tr]
[/table]

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:

=ROW(RData)-ROW(INDEX(RData,1,1))+1

3) B1, control+shift+enter (cse), not just enter:

=SUM(IF(FREQUENCY(IF(RData<>"",MATCH(RData,RData,0)),Ivec)>1,1))

4) B2, cse and copy down:

=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:

=OFFSET(Sheet1!$B$2,0,0,Sheet1!$B$1)

6) C2, cse and copied down:

=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:

=COUNTIFS(RData,C2)

The foregoing set up is unaffected by empty/blank cells of the raw data.

 

[Help101]

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!