Excel How to analyze data in a table that contains multivalue cells

0 votes

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
3 Histeriade
4 Dermestidae, Histeridae
5 Cleridae, Dichotumius
485 Histeriade
486 Dermestidae, Histeridae
487 Dermestidae, Cleridae
488 Histeriade

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;

Instead of this:

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

I want this:

# of sample insect (family)
1 Dermestidae
1 Histeridae
2 Histeridae
2 Dichotumius
3 Histeriade
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 by Kithuzzz
• 38,000 points
437 views

1 answer to this question.

0 votes

 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.

answered Jan 10, 2023 by narikkadan
• 63,600 points

Related Questions In Others

0 votes
1 answer

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

answered Mar 26, 2023 in Others by Kithuzzz
• 38,000 points
908 views
0 votes
1 answer

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

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

answered Feb 10, 2022 in Others by gaurav
• 23,260 points
7,982 views
0 votes
1 answer

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

answered Oct 24, 2022 in Others by narikkadan
• 63,600 points
798 views
0 votes
1 answer

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

answered Nov 17, 2022 in Others by narikkadan
• 63,600 points
3,804 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

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

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
1,260 views
0 votes
1 answer

ImportError: openpyxl is required for loading excel format files

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

answered Oct 3, 2018 in Python by Priyaj
• 58,020 points
1,124 views
0 votes
1 answer

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

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

answered Oct 15, 2018 in RPA by Priyaj
• 58,020 points
4,395 views
0 votes
1 answer

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

try this. let TableA = ...READ MORE

answered Oct 22, 2018 in Power BI by Annie97
• 2,160 points
4,288 views
0 votes
1 answer
0 votes
1 answer

In Excel, how to find a average from selected cells

If one has the dynamic array formula ...READ MORE

answered Oct 9, 2022 in Others by narikkadan
• 63,600 points
774 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP