Excel TextSplit a range and COUNTIF - with wildcards [closed]

Question

Closed. This question needs debugging details. It is not currently accepting answers.

Edit the question to include desired behavior, a specific problem or error, and the shortest code necessary to reproduce the problem. This will help others answer the question.

Closed 10 months ago.

Improve this question

My question builds on this previous post: Excel TextSplit a range and countif

I have a very similar use case, except that the values I'm trying to match may have numbers at the end, like A1, A2, CR3, etc. Solution screenshot posted for previous problem

I want them to be added up such that all A* are counted together, all CR* are counted together, etc.

I'm using the solution provided by @VBasic2008 , and I tried adding the * in the Code list, but it doesn't work. How do I enable this for wildcards? Thanks in advance!

Post sample data as text table and show your desired output. What your are trying to achieve by using wild card? — Harun24hr
– Harun24hr, Commented Feb 26 at 8:10

Michal · Accepted Answer · 2025-02-26 08:12:36Z

1

I would suggest a better tool like for example Power Query, one which is better suited for manipulating tables and lists:

let
    // Load source table
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Filter = Table.SelectRows(Source, each ([Codes] <> null)),

    // Split "Codes" column into a list
    ExpandedCodes = Table.TransformColumns(Filter, {"Codes", each Text.Split(_, ","), type list}),

    // Expand "Codes" column into multiple rows
    ExpandRows = Table.ExpandListColumn(ExpandedCodes, "Codes"),

    // Remove empty values in "Codes" column
    RemoveBlanks = Table.SelectRows(ExpandRows, each [Codes] <> ""),

    // Rename column for clarity
    RenamedColumns = Table.RenameColumns(RemoveBlanks, {{"Codes", "Code"}}),

    // Group by Code and count occurrences
    GroupedTable = Table.Group(RenamedColumns, {"Code"}, {{"Total", each Table.RowCount(_), Int64.Type}})
    
in
    GroupedTable

Edit: you could also easily do it with Python:

import pandas as pd

# Hardcoded input data
data = {
    "Name": ["James", "Beth", "Holly", "Jimmy", "Peter", "Charlie", "Sue", "", "Sofie"],
    "Codes": ["1,2", "2,3,4", "6,8,9", "2,A,CR", "", "1,3,5", "", "", "1,CR"]
}

# Convert to DataFrame
df = pd.DataFrame(data)

# Split "Codes" column into multiple rows
df_exploded = df.assign(Codes=df['Codes'].str.split(',')).explode('Codes')

# Remove empty values
df_exploded = df_exploded[df_exploded['Codes'].notna() & (df_exploded['Codes'] != "")]

# Rename "Codes" column to "Code"
df_exploded.rename(columns={'Codes': 'Code'}, inplace=True)

# Count occurrences of each unique "Code"
df_grouped = df_exploded.groupby("Code").size().reset_index(name="Total")

# Display the final table
print(df_grouped)

answered Feb 26 at 8:12

Michal

7,6894 gold badges30 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Harun24hr Feb 26 at 9:08

I assume OP have different data like 1,CR2,2,A,CR3 but he want to count CR using wild card match.

Michal Feb 26 at 9:36

@Harun24hr - well, I actually clicked on the link with the screenshot so I didn't have to assume anything

Harun24hr Feb 26 at 9:56

@OP's I assume from OP's description and screenshot name describe that, it was for previous problem. however, lets see what OP says.

Harun24hr · Accepted Answer · 2025-02-26 09:30:23Z

1

Give a try to the following formula-

=LET(
x,TEXTSPLIT(TEXTJOIN(",",1,B2:B9),,","),
y,TOCOL(REGEXEXTRACT(x,"[^\d]+"),3),
z,VSTACK(y,FILTER(x,ISNUMBER(--x))),
GROUPBY(z,z,COUNTA,0,0))

Input Data:

Name	Codes
James	1,2
Beth	2,3,4
Charlie	1,3,5
Holly	6,8,9
Sofie	1,CR3
Jimmy	2,A1,CR2
Excel	2,A2,CR4
M365	2,A3,CR5

Output Result:

Name	Codes
1	3
2	5
3	2
4	1
5	1
6	1
8	1
9	1
A	3
CR	4

answered Feb 26 at 9:30

Harun24hr

38.1k6 gold badges24 silver badges47 bronze badges

Collectives™ on Stack Overflow

Excel TextSplit a range and COUNTIF - with wildcards [closed]

2 Answers 2

3 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Linked

Related