Excel Formula Help
Discussion
Right, I'm building a table in excel that identifies the attendees at various races over a decade for some historic racing research.
The top two rows identify the date and location of the race
The first few columns identify the driver and car (and specific chassis)
Now in the heart of the table, most cells are blank (reasonably, as I have some 200 races and 200 driver/chassis combinatons from the first five years). But in cells where a driver/chassis combinations attended a particular race, there is a character in the cell. This might be:
A number: for the finishing position
A character(s): For others (R=Retired, NS= Did not start)
Another character (a black blob, like a bullet point): Meaning we know they were there, but not the result.
What I want to do is add a couple of columns of formulae, to identify first sighting and last sighting for each driver/chassis. So for each row, it needs to find the leftmost (and rightmost) non-blank cell in that row, and display the date from Row 1.
So can Excel do this? If it helps, I can restrict the range (e.g. Column 5 to a nominal Column 1,000).
Thanks in advance.
The top two rows identify the date and location of the race
The first few columns identify the driver and car (and specific chassis)
Now in the heart of the table, most cells are blank (reasonably, as I have some 200 races and 200 driver/chassis combinatons from the first five years). But in cells where a driver/chassis combinations attended a particular race, there is a character in the cell. This might be:
A number: for the finishing position
A character(s): For others (R=Retired, NS= Did not start)
Another character (a black blob, like a bullet point): Meaning we know they were there, but not the result.
What I want to do is add a couple of columns of formulae, to identify first sighting and last sighting for each driver/chassis. So for each row, it needs to find the leftmost (and rightmost) non-blank cell in that row, and display the date from Row 1.
So can Excel do this? If it helps, I can restrict the range (e.g. Column 5 to a nominal Column 1,000).
Thanks in advance.
Congratulations on the most obtuse spreadsheet design!
I'd suggest to build a table instead which holds driver, chassis, location, date, result in columns. Then use pivottable to create the spredsheet you want. Driver and chassis as rows, location and date as columns, max of result as data. The non numerics will show as a zero. Turn subtotals off for driver and location, and grand totals.
To answer the question of when a driver or chassis was first/last seen drag driver, chassis and location to "page fields", and sort date either ascending or descending after picking your driver, chassis and location from the page field dropdowns.
By dragging the fields around you will be able to extract all sorts of info from your "database".
With your present design you have no option but to write VBA scripts.
I'd suggest to build a table instead which holds driver, chassis, location, date, result in columns. Then use pivottable to create the spredsheet you want. Driver and chassis as rows, location and date as columns, max of result as data. The non numerics will show as a zero. Turn subtotals off for driver and location, and grand totals.
To answer the question of when a driver or chassis was first/last seen drag driver, chassis and location to "page fields", and sort date either ascending or descending after picking your driver, chassis and location from the page field dropdowns.
By dragging the fields around you will be able to extract all sorts of info from your "database".
With your present design you have no option but to write VBA scripts.
Doh! That'll teach me to post on techie subject after witching hour.
I think you may need to use a combination of HLOOKUP and MATCH.
Assuming a table with columns A:L something like this would get the date corresponding to the first non-null on row 10:
=OFFSET($A$1,1,MATCH(HLOOKUP("x",B10:L10,1,TRUE),B10:L10,0)+1,1,1)
The HLOOKUP for "x" is expected to fail ("x" wasn't one the values you specified you were using). The TRUE following it says return the first value greater than that. It won't work if you enter your results in lowercase though. The B10:L10 range will vary depending on what columns you are using.
This works for the non numeric results, but not for the numbers. You could have a second worksheet which converts your table into text values (and makes them uppercase) and do the lookup on that instead to make it work, unless there is an easier way I am missing. You could enter the data as P1, P2, P3 etc of course!
I think you may need to use a combination of HLOOKUP and MATCH.
Assuming a table with columns A:L something like this would get the date corresponding to the first non-null on row 10:
=OFFSET($A$1,1,MATCH(HLOOKUP("x",B10:L10,1,TRUE),B10:L10,0)+1,1,1)
The HLOOKUP for "x" is expected to fail ("x" wasn't one the values you specified you were using). The TRUE following it says return the first value greater than that. It won't work if you enter your results in lowercase though. The B10:L10 range will vary depending on what columns you are using.
This works for the non numeric results, but not for the numbers. You could have a second worksheet which converts your table into text values (and makes them uppercase) and do the lookup on that instead to make it work, unless there is an easier way I am missing. You could enter the data as P1, P2, P3 etc of course!
Thanks for the advice. I've bounced a copy to fish to see whether two heads can come up with a solution. It's not essential, but would be very useful when researching a particular driver or chassis history. I appreciate the design is "obtuse", and a blatant misuse of Excel, but it's already proving much more effective than what we had before, and is so close to doing everything we need of it.
Gassing Station | Computers, Gadgets & Stuff | Top of Page | What's New | My Stuff