Currently, I'm working on a scientific project about insects, and I've been keeping a journal of the insects I've found so far. I now understand that listing the names of every insect I have been finding with each observation was a mistake. I am not authorized to share too much information because it is private, but I will add a representative sample of my situation to the table below:

# of sample insect (family)
1 Dermestidae, Histeridae
2 Histeridae, Dichotumius
4 Dermestidae, Histeridae
5 Cleridae, Dichotumius
486 Dermestidae, Histeridae
487 Dermestidae, Cleridae

Something like the above table. In my actual table, I have cells with 5 or 6 diferent insects. The thing is:

• How can I search for all the different values? I mean, I want to create a table that contains all the different values and how many of them are... Something like the following table:
Insect (family) Count
Cleridae 54
Histeridae 154
Dermestidae 34

(There are at least 100 different insects and some of them just appear once, so it is impossible for me to search all the different names manually.

Furthermore, I was thinking about converting my table to a long structure. Something like the following;

# of sample insect (family)
1 Dermestidae, Histeridae
2 Histeridae, Dichotumius
4 Dermestidae, Histeridae
5 Cleridae, Dichotumius

I want this:

# of sample insect (family)
1 Dermestidae
1 Histeridae
2 Histeridae
2 Dichotumius
4 Dermestidae
4 Histeridae
5 Cleridae
5 Dichotumius

I was thinking that this arrangement should be better than the one that I have now. I hope someone can help me with this issue.

Jan 10, 2023 in Others 245 views

## 1 answer to this question.

The below formula will create a unique list of the insect families (where the insect families are in range B2:B100)

```=UNIQUE(TEXTSPLIT(TEXTJOIN(", ",TRUE,B2:B100),"|",", ",TRUE))
```

You will then be able to use a COUNTIF() formula to find how many tests contain each family.

• 63,700 points

## Select data that meet criteria from a table, adding it to a combobox in userform VBA Excel

Fill Combo Box With Matches Sub GetSourceAcc() ...READ MORE

## How to import data from a HTML table on a website to excel?

Hello  To import any HTML file in excel there ...READ MORE

## How can I use a command button in excel to set the value of multiple cells in one click?

Try this: Private Scan As Integer Private Sub CommandButton1_Click() ...READ MORE

## How to find a value in an excel column by vba code Cells.Find

Just use: Dim Cell As Range Columns("B:B").Select Set cell = ...READ MORE

## Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

Forget the PsychoPy complications for the time ...READ MORE

## In Blue Prism how to split excel column data into TWO columns

This is how I am doing it. Dim ...READ MORE

## Excel Power Query: Using List.MatchAny on a column value

try this. let TableA = ...READ MORE