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 in Others by Kithuzzz
• 28,900 points
44 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 by narikkadan
• 53,520 points

Related Questions In Others

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
• 22,960 points
536 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
• 53,520 points
151 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
• 53,520 points
292 views
0 votes
1 answer

How to remove borders from cells in a range in Excel using VB.net?

range.Borders(Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Excel.XlLineStyle.xlLineStyleNone range.Borders(Excel.XlBordersIndex.xlEdgeRight).LineStyle = Excel.XlLineStyle.xlLineStyleNone range.Borders(Excel.XlBordersIndex.xlEdgeTop).LineStyle = Excel.XlLineStyle.xlLineStyleNone range.Borders(Excel.XlBordersIndex.xlEdgeBottom).LineStyle ...READ MORE

answered Jan 5 in Others by narikkadan
• 53,520 points
86 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
533 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,100 points
482 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,100 points
3,503 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
3,016 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
• 53,520 points
168 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