Any Excel Gurus?
I need help. Here's a sample spreadsheet:
| A | B | C | |
| 1 | alex | fred | ted |
| 2 | 5 | 8 | 31 |
| 3 | 7 | 16 | 20 |
| 4 | 14 | 19 | 11 |
| 5 | 21 | 22 | 4 |
Pretty easy for this example. I want to get the maximum of a row (say MAX A1,b1,c1) and then print the name associated with it. So I'd print TED for row 2, 3, and FRED for rows 4 and 5. My real example is much larger, but I'm unable to even get the concept. I've tried Google but haven't found a good example yet. I'm sure there's a solution.
2-13-07 update: A number of people have suggested the HLOOKUP and VLOOKUP but those functions both have limits as to where the data is placed. The actual data is far more complex, with five 6 x 6 grids of data and the MAX function is on random cels. If anyone wants the actual spreadsheet to see the issue, I will gladly provide a copy of one set of data. Maybe the solution is a Macro, If/Then, or some combination.
posted by Rev. CMOT TMPV @ 2/09/2007 12:58:00 PM (permalink)
Stumble It!
1 Comments:
Sorry dude; I'm no Excel guru. But it seems kinda like a database problem, or maybe a hashtable problem. I'm assuming that you wouldn't just be picking rows at random, so you have a relationship between the data in the rows and in the columns that would make sense. I dunno; thinking inside my head but out loud, I'd want to put the data in a couple of tables, if an artificial relationship can be created. Then iterate through your record set: for every value in row 1, column C is the max. For every value in row 2, column A is the max. Mapping A and C to FRED and TED is easy, and I see myself looping through the table, putting the output in another searchable table. We probably do something similar at work where we have a database where a group of tables basically tracks our sales reps. Reps A, B, and C did x, y, and z dollars in sales on day 1. Max for day one is y, which corresponds to rep B. And so on.
I have no idea if this helps. If it has to be Excel, well, sorry man, but Excel's not a good database...
Post a Comment
Links to this post:
Create a Link
<< Home