Maggie Barr
Board Regular
- Joined
- Jan 28, 2014
- Messages
- 188
I am trying to identify parts of text/numeric within a string of data in a cell and if it is found return a new row (new sheet) with identifiers from multiple columns in new sheet as described below.
I am using a PC with Excel 2010. I approach this request for help with a humble, hanging head. I know I can do this manually with a variety of steps to make it a little faster, but I just feel/know that there must be an excel GURU out there that may be able to help me. I have a feeling it would be a rather lengthy VBA code that I yet to have the skills to even pretend to approach. Even if you can’t solve my puzzle any advice would be greatly appreciated.
Here is the puzzle:
In column A I have plant species names, and in columns B through N are townships (1-13), and within those cells are vouchers, observations, and references, sometimes one, sometimes multiple all separated/enclosed by().
A portion of the sheet would look like this:
<tbody>
</tbody>
What I would like to do is produce a table where species and township are repeated in columns A and B for the presence of each of the following REFERENCES (ignore the other stuff) within a cell: The trick here is that I need it to search for a key ‘part’ (Name and Year – see below) of a reference within () because sometimes there are other details within the “(reference)” and return the Reference ID (Column A in table below). Please note it cannot check only for name because some authors have more than one year of publication (Dibble has three), and there are separate publications from the same year. What is important is that each cell needs to be checked for all authors (I think as a separate command) so it will include all references within the cell for output (see below).
Authors table:
<tbody>
</tbody>
SOOO, the resulting output table for species one of the first table would look like this:
<tbody>
</tbody>
Well, hope that this thread triggers some interest. As I said, I know multiple steps to sort of get there, but in the end it is a lot of manual labor.
Thank you in advance for taking the time to read this thread and perhaps respond.
Best,
Maggie
I am using a PC with Excel 2010. I approach this request for help with a humble, hanging head. I know I can do this manually with a variety of steps to make it a little faster, but I just feel/know that there must be an excel GURU out there that may be able to help me. I have a feeling it would be a rather lengthy VBA code that I yet to have the skills to even pretend to approach. Even if you can’t solve my puzzle any advice would be greatly appreciated.
Here is the puzzle:
In column A I have plant species names, and in columns B through N are townships (1-13), and within those cells are vouchers, observations, and references, sometimes one, sometimes multiple all separated/enclosed by().
A portion of the sheet would look like this:
| Species | Township1 | Township2 | Township3 |
| Abies balsamea | X (BFP 2013) (BFP 2012) (Lortie et al. 1996) (Hudson 1985) | X (BFP 2013) (Burns 1982 [1,2,3,4,S,M,H,C]) (Rooney 1984 [5,6,11,S,M,H,C]) | X (BFP 2013) (BFP 2012) (Lortie et al. 1996) (Hudson 1985) (May & Davis 1978) |
| Acer pensylvanicum | X (BFP 2013) (Lortie et al. 1996) (Burns 1982 [Horse Mtn. Rare Plant Station-one of 3 known Maine stations for plant]) | X (BFP 2013) (BFP 2012) (Lortie et al. 1996) (Hudson 1985) | X (BFP 2013) (Burns 1982 [1,2,3,4,S,M,H])(Rooney 1984 [5,6,11,S,M,H]) |
| Acer rubrum | X (BFP 2013) | X (BFP 2013) (BFP 2012) (Lortie et al. 1996) (Hudson 1985) | X (BFP 2013) (Lortie et al. 1996) (Burns 1982 [1,2,3,4,S,M,C])(Rooney 1984 [5,6,11,S,M,H]) |
<tbody>
</tbody>
What I would like to do is produce a table where species and township are repeated in columns A and B for the presence of each of the following REFERENCES (ignore the other stuff) within a cell: The trick here is that I need it to search for a key ‘part’ (Name and Year – see below) of a reference within () because sometimes there are other details within the “(reference)” and return the Reference ID (Column A in table below). Please note it cannot check only for name because some authors have more than one year of publication (Dibble has three), and there are separate publications from the same year. What is important is that each cell needs to be checked for all authors (I think as a separate command) so it will include all references within the cell for output (see below).
Authors table:
| 14 | Burns 1982 |
| 6 | Clark 1998 |
| 9 | Cogbill 1990 |
| 1 | DeWolf 2012 |
| 8 | Dibble 1994 |
| 3 | Dibble 2007 |
| 10 | Dibble 1990 |
| 17 | Fernald 1901 |
| 18 | Hansen 1938 |
| 13 | Hansen 1983 |
| 11 | Hudson 1985 |
| 7 | Lortie 1996 |
| 15 | May 1978 |
| 12 | Rooney 1984 |
| 5 | Small 2004 |
| 16 | Stebbins 1927 |
| 4 | Weber 2005 |
| 2 | Weihrauch 2010 |
<tbody>
</tbody>
SOOO, the resulting output table for species one of the first table would look like this:
| Species | Township | Reference number |
| Abies balsamea | 1 | 7 |
| Abies balsamea | 1 | 11 |
| Abies balsamea | 2 | 14 |
| Abies balsamea | 2 | 12 |
| Abies balsamea | 3 | 7 |
| Abies balsamea | 3 | 11 |
| Abies balsamea | 3 | 15 |
<tbody>
</tbody>
Well, hope that this thread triggers some interest. As I said, I know multiple steps to sort of get there, but in the end it is a lot of manual labor.
Thank you in advance for taking the time to read this thread and perhaps respond.
Best,
Maggie
