Author Topic: A new excel question (Read 3107 times)

zpyder · **on:** December 04, 2014, 19:34:58 PM

A new excel question

on: December 04, 2014, 19:34:58 PM

I'm assuming I need to use a vlookup or something, but I've never really got my head around lookup tables or pivot tables.

Basically I have a MASSIVE spreadsheet containing all known bird species. Each species is listed 6 times (individual specimens), one per row. As we process each specimen, the relevant information goes in its row. When I say massive, I mean there are 60 000 rows of data, slowly getting filled in...

What I want to do is have a separate worksheet or table that will go through the main sheet, look for rows that have been "completed", and then compile list of completed species. This would mean stripping out 5 of the 6 rows for that species.

hopefully the above makes sense...

Last Edit: December 04, 2014, 19:43:00 PM by zpyder #187;

zpyder · **Reply #1 on:** December 04, 2014, 19:41:50 PM

Re: A new excel question

Reply #1 on: December 04, 2014, 19:41:50 PM

Species	ID	Processed
a	1	y
a	2	y
a	3	y
b	1
b	2
b	3
c	1	y
c	2	y
c	3

In the example above, the summary sheet would read:

Species	Count
a	3
c	2

Should be a bit more clear now!?!? Not interested in species/rows that haven't got data, and I don't need a summary of every column, just a count of rows for each species that contains data.

Last Edit: December 04, 2014, 19:44:54 PM by zpyder #187;

M3ta7h3ad · **Reply #2 on:** December 04, 2014, 23:05:32 PM

Re: A new excel question

Reply #2 on: December 04, 2014, 23:05:32 PM

What you need is a database dude. Excel isn't a replacement it gets slow and sh*t as it gets bigger.

Could do what you ask though using a macro. Do each species have the same name in a count group?

A pivot table may do the job.

Last Edit: December 04, 2014, 23:09:02 PM by M3ta7h3ad #187;

XEntity · **Reply #3 on:** December 05, 2014, 09:45:06 AM

Re: A new excel question

Reply #3 on: December 05, 2014, 09:45:06 AM

Basically your second column is redundant..

Select the whole table... Then press insert pivot..

Put Species under "rows"
Put Processed under "values" and should automatically put this as a count, it will then count the cells with data (i.e. a Y present)

This will show every entry in the first column though.. just a zero against B..

Metal is right though, you should be really looking at a DB, use the Access wizard it's fairly easy, although to get this report it would still provide the same without some tweaking..

Last Edit: December 05, 2014, 09:54:17 AM by XEntity #187;

zpyder · **Reply #4 on:** December 05, 2014, 10:14:37 AM

Re: A new excel question

Reply #4 on: December 05, 2014, 10:14:37 AM

The reason it is in excel is the data needs to be easily parsed into CSV files so that it can be read through an analytical program suite called "R".

Will try pivot tables later though.

XEntity · **Reply #5 on:** December 06, 2014, 23:50:15 PM

Re: A new excel question

Reply #5 on: December 06, 2014, 23:50:15 PM

You can still export from the db in to csv though ;-)

Clock'd 0Ne · **Reply #6 on:** December 07, 2014, 13:46:05 PM

Re: A new excel question

Reply #6 on: December 07, 2014, 13:46:05 PM

Sounds like the ideal candidate for a small node.js webapp, I can't imagine this is going to get any easier to maintain in Excel either.

Serious · **Reply #7 on:** December 08, 2014, 06:25:07 AM

Re: A new excel question

Reply #7 on: December 08, 2014, 06:25:07 AM

Neither can I, how about converting A COPY to DB then using it for this? Can be discarded later.

M3ta7h3ad · **Reply #8 on:** December 08, 2014, 10:33:21 AM

Re: A new excel question

Reply #8 on: December 08, 2014, 10:33:21 AM

Spreadsheets are for calculations.

Databases are for storing data.

You know from the data ribbon bar you can easily link excel to a database if you really want to access it using excel.

That way your spreadsheets stay manageable sizes and don't have a performance hit and your database can be normalised to minimise the size on disk it takes as well as indexed properly to speed up your workflow.

zpyder · **Reply #9 on:** December 08, 2014, 16:50:24 PM

Re: A new excel question

Reply #9 on: December 08, 2014, 16:50:24 PM

Could do any of the suggestions I guess. But the main obstacle is that the spreadsheet is something the natural history museum gave us to work from. Part of the conditions of having access to these specimens is that we do the data entry for the specimens as we go. Chances are by the time something is developed that the museum is happy with, the project will be over!

Specimens are very cool, there are some that were collected by Alfred Russell Wallace (one of the founding fathers of the theory of evolution by natural selection...)

News:

Author Topic: A new excel question (Read 3107 times)