3

I have a column in Excel that contains a comma delimited list:

Header
1, 61
61
1, 61, 161
5, 55

I would like to extract this data so that I can count the occurrences of each item so that I would have the following results:

Count of Items
1    |    2
5    |    1
55   |    1
61   |    3
161  |    1

I tried countif with "*" but its a mess because in this case I have prefixes or suffixes (1,61,161)

PLEASE HELP!

0

3 Answers 3

4

Option 1:

I would like to suggest UDF (User Defined Function) which not only counts occurrence of comma separated numbers but counts text also.

enter image description here

How it works:

  • Press Alt+F11 to get VB editor then Copy & Paste this code as Module.

    Option Explicit
    
    Function CountOccurrence(SearchRange As Range, Phrase As String) As Long
    
    Dim RE As Object, MC As Object
    Dim sPat As String
    Dim V As Variant
    Dim I As Long, J As Long
    
    V = SearchRange
    
    Set RE = CreateObject("vbscript.regexp")
    With RE
    .Global = True
    .MultiLine = True
    .ignorecase = True
    .Pattern = "(?:^|,\s*)" & Phrase & "(?:\s*,|$)"
    End With
    
    For I = 1 To UBound(V, 1)
    If RE.test(V(I, 1)) Then J = J + 1
    Next I
    
    CountOccurrence = J
    
    End Function
    
  • Enter Criteria in Range H18:H26, then enter this Formula in Cell I18 & fill it down.

=CountOccurrence($G$18:$G$24,H18)

Option 2:

Enter this Formula in Cell I18 & fill it down.

=SUMPRODUCT(--ISNUMBER(FIND(H18,$G$18:$G$24)))

Adjust cell references as needed.

1
  • 1
    =SUMPRODUCT(--ISNUMBER(FIND(H18,$G$18:$G$24))) with this formula is confuses the 5 with 55 the same problem Jun 28, 2019 at 10:40
1
  1. First you need to split your numbers into separate cells:
    • select your data
    • on data tab select "split to columns"
    • select "delimited", next
    • select data is separated by "comma", finish
  2. now you can use COUNTIF, e.g.
    =COUNTIF($A$2:$C$5,E2)

enter image description here

7
  • How do you get the list in column E? :-)
    – fixer1234
    Jun 28, 2019 at 8:17
  • @fixer1234: typed it:), as I understand it was not part of the question to generate unique list of items. Jun 28, 2019 at 8:20
  • Many thanks for your promote answer, is there any other way without splitting them? Jun 28, 2019 at 8:36
  • @MuhannadHaddad, 1. is your issue with splitting the data into separate columns only that you lose the original data? If so, that doesn't need to be an issue. You can either work from a copy of the data or tell it to split in another output location. This is a practical solution; any other approach gets very complicated. 2. Do you have a method of generating the list of unique values to use with this solution? If the source data is substantial and it would be a job to reliably create that list, there's another approach to create the list and counts at the same time, but it's some extra steps.
    – fixer1234
    Jun 28, 2019 at 9:22
  • No splitting the data isnt a problem, the thing is with splitting i have to redo the split each time i change something in the original table Jun 28, 2019 at 9:27
1

It looks like you already have a solution, but I'll toss in a non-VBA solution that handles dynamic data. It uses some helper columns that you can prepopulate to an arbitrarily large range. When there is no associated data, the cells will be blank. Some of helper columns could be eliminated; they're included to minimize repetition, but all of the helper columns can be hidden.

enter image description here

Your data is in column A. Column B determines the number of values in each entry based on the number of commas. The formula in B3 is:

=IF(ISBLANK(A3),"",LEN(A3)-LEN(SUBSTITUTE(A3,",",""))+1)

Column C is just the cumulative component count from column B. C2 is entered as 0. The formula in C3 is:

=IF(ISBLANK(A3),"",SUM(B3:B$3))

Populate columns B and C to as many rows as you might ever have data for. You can always extend those columns if needed.

Column E is just for convenience. It provides an index for the parsed values. You could hard-code the 1 and then add 1 to it for each successive row. For no good reason, I based it on the row number (the -2 in the formula below is to adjust the values to start at 1). Cells beyond the total number of values get a blank. My formula in E3:

=IF(ROW()-2>MAX($C$2:$C$10),"",ROW()-2)

Column F is just to avoid formula repetition. It pulls the relevant Column A entry from which the current component will be parsed. The formula in F3 is:

=IFERROR(OFFSET($A$2,MATCH(E3-1,$C$2:$C$10,1),0),"")

It finds the appropriate entry by comparing the component number in column E with the cumulative component count in column C.

Column G is the parsed component values, all in a single contiguous column that is easy to work with. The formula in G3 is:

=IFERROR(TRIM(MID(SUBSTITUTE(F3,",",REPT(" ",LEN(F3))),(E3-INDEX($C$2:$C$10,MATCH(E3-1,$C$2:$C$10,1))-1)*LEN(F3)+1,LEN(F3))),"")

This determines which element to parse from the column F entry by subtracting the cumulative element count of the last "completed" input record from the current element number.

Columns E through G should be propagated to enough rows to cover the anticipated number of component values (at least several times the number of data rows). Note that all the formulas above that refer to the range $C$2:$C$10 should be adjusted to include the full range of your data.

Now that you have all of the parsed elements in a nice column, there are a variety of ways to aggregate them and get the counts. I used a pivot table, which also gives you the list of unique values at the same time.

Select the full pre-populated range of column G for the pivot table. Use that field for the rows window and the values window (select count as the aggregation). The range will include blanks in the unused rows, so use the built-in filter to deselect blanks.

When the data changes, just refresh the pivot table and verify that any new element values are selected in the filter.

0

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .