Excel 2003. I have a dataset of ~5000 entries, 20 columns. Laboratory animal data. Database was designed in during the dinosour age
Multiple animals and multiple tests frequently entered as one "case" (e.g. row of data).
I was told I should look at text mining software, but I think I should be able to do what I need in Excel....I just don't know how to do it yet.
Many problems to work on, but let's just start with one....
Many data fields are inconsistently filled out, or not at all. But, I might be able to identify the cases I want from information from multiple columns. For example, if I want to identify all the cases of abortions....there *might* be "abortion" under the "diagnosis" field, or not; there might be "fetus" under "submission type", or not; there might be a negative number in the "age" field - or not. etc etc.
I am looking for a way to identify and extract, to a new worksheet, cases which meet at least one of the descriptions I am looking for. If they could at the same time be deleted from the original worksheet that would be a bonus.
thanks in advance to anyone who can help.
It sounds as though you might be able to do this with an Advanced Filter.
You'd need to set up the "and/or" conditions somewhere and copy filtered records to a new location.
Upload a sample workbook so the theory can be tested.
Regards
A sample file (99 entries, some data removed for confidentiality) is attached.
Thanks!
OK, I couldn't work out which columns you wanted to use in the sample provided.
So, as an example, I've selected Specimen_Type = Fetus, OR *** = Female, OR *** = Castrated Male.
You need to set up the selection criteria as in the example on Sheet 2 and copy the headings to Sheet 3, where we want to output the selected rows. You MUST have Sheet 3 selected when you start the filter process.
Then you choose Advanced Filter, copy to new location, selection criteria (the cells on Sheet 2) and Source data the cells on Sheet1.
See the example attached.
Regards
I only just had time to look at this again.
I got the advanced filter working as per your example without problem.
However, it would seem the filter criteria must match cell contents exactly. For example, if I filter for "Brucella abortus" under the heading "FEE_CODE", (that column is in the original dataset, not the sample set I posted), the filter only copies the first line out of the target sheet.
But, if I filter for "B600^ Brucella abortus - Buffered plate agglutination test^ 4^ $20.00{B601^ BPAT - Set-up Fee^ 1^ $25.00" then it copies records with an exact match - not useful to me, because there are a number of variations to this record which would not be picked up.
In the example above, I need a way to pick out the words "Brucella abortus" regardless of what other text is in that cell.
In the sample data set I posted last time, this would be like looking for "abortion" under the headings PATH_DX or FULL_HX, which are comment fields.
This seems like essentially text mining, can Excel do this?
Thanks!
Use *Brucella abortus*
Regards
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks