See Bottom "Terry North" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Dennis Marks <denmarks <at> yahoo.com> writes: >> .... >> >I have a spreadsheet with multiple records (lines). Within each record >> >there is a cell containing 1 or more strings separated by commas. Is >> >there >> >a way to search for a string and return the line number of the record >> >that >> >contains the cell with the string. For example: >> > LINE CELL1 CELL2 CELL3 CELL4 CELL5 >> > 0001 AAAA BBBBB CCCC a, b, c, d, EEEEE >> > 0002 AAAA BBBBB CCCC e, f, g, h, EEEEE >> > >> > Search the column with cell4 and find the line with the f in it. Line 2 >> > would be returned. I know that I can search for e,f,g,h and find it but >> > I >> > want to only search for a single item. >> > > I don't follow your example but I think the MATCH function might be what > you're > after. To illustrate, I have a vertical list with 14 text codes and a > heading, > a total of 15 rows. In the 10th row, the code "HZN". To get an exact > match, I > use the parameter 1: =MATCH("HZN";_TableCodes;1) which yields 10. If I > change > the parameter to 0 and look for a near match: =MATCH("HSN";_TableCodes;0) > I get > 9 although the code in the previous row is FML. You work it out, I can't. > If I > want an exact match and try =MATCH("HSN";_TableCodes;0), I get #N/A.
I have multiple items in a single cell. I'm playing around with train schedules. A single train can have between 2 and 50 train numbers assigned to it. I don't want to have to repeat the data 50 times so I place all train numbers for a single train in a single cell delimited by commas. So I have to do a search within a cell and then a vertical search. Example TRAIN1 has numbers 1,2,3,4,5,6,7 TRAIN2 has numbers 9,10,12 I don't want to repeat TRAIN1 7 times and TRAIN2 3 times. Now find train 10 and return data for TRAIN2 I am able to do it by adding another cell to each line with an indicator if the number was located on that line and then I do a search of the added cells. I would like to do it with a single compound formula without adding the new cell. --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
