1

I'm trying to more fully automate the calculation of values and summary data in a spreadsheet I keep about the results of matches in a pool league.

I have a table with lots of information about each match, with the relevant fields being: Date of Match, Winner, Winner Starting Handicap, Winner Ending Handicap, Loser, Loser Starting Handicap, Loser Ending Handicap, Match Start Time.

Handicaps are adjusted at the end of every match, and before the next one. It's a pain to find the most recent past record for a player (could have been a Winner or Loser) and copy his ending handicap from that record to the Starting Handicap (winner or loser) for the one I'm now entering.

I'd like a formula that would find the most recent record (highest date & start time in case he played twice in one day) where he was a winner or a loser, and then get the ending handicap (from the respective winner or loser).

Per teylyn's suggestion, here's a Dropbox link to the file. The relevant tab is Match Results: https://www.dropbox.com/s/1j9c6zsxjd3q4dt/Sample%20for%20Excel%20Question%20on%20Superuser.xlsx?dl=0

I added a blank column L to test things, comparing the results to what's in K to see if they were working, that's why it's there. Forgot to remove it when I put it in Dropbox.

  • It would help to see the data structure. Can you post a sample file? Think file sharing service. – teylyn May 4 '15 at 1:10
1

Problem Statement

A worksheet has names in Columns E and X.  For every row n, EnXn.  There are numbers in Column M corresponding to the names in Column E, and numbers in Column AG corresponding to the names in Column X.  For any row after the first (let’s say Row 42), we want to get values for K42 and AF42 from previous rows, if possible.

  • If E42 is “John”, find the most recent row that contains “John” (in Column E or X).  Call that row n.  If En = “John”, set K42 equal toMn.  If Xn = “John”, set K42 equal toAGn.
  • If X42 is “Scott”, find the most recent row that contains “Scott” (in Column E or X).  Call that row n.  If En = “Scott”, set AF42 equal toMn.  If Xn = “Scott”, set AF42 equal toAGn.

                   

Solution

In the hopes of preserving some sanity, let’s use helper columns; let’s say AR and AS.  Assume that (as in the example file), data start in Row 2.  Enter

=MAX(($E$2:$E2=$E3)*(100*ROW($E$2:$E2)+COLUMN($M:$M)), ($X$2:$X2=$E3)*(100*ROW($X$2:$X2)+COLUMN($AG:$AG)))

into AR3 (skipping AR2).  End with Ctrl+Shift+Enter, to make it an array formula.  Likewise, set AS3 to

=MAX(($E$2:$E2=$X3)*(100*ROW($E$2:$E2)+COLUMN($M:$M)), ($X$2:$X2=$X3)*(100*ROW($X$2:$X2)+COLUMN($AG:$AG)))

as an array formula.  (This is the same as AR3 except the two occurrences of $E3 have been replaced with $X3.)

Set K3 to

=IF($AR3=0, "?", INDEX($A$1:$BG$999, INT($AR3/100), MOD($AR3,100)))

and AF3 to

=IF($AS3=0, "?", INDEX($A$1:$BG$999, INT($AS3/100), MOD($AS3,100)))

(not as array formulas).  These are the same except the three occurrences of $AR3 have been replaced with $AS3.

And, of course, drag/fill down.

The helper columns find the most recent previous occurrences of the names — ARn finds the most recent previous occurrence of En, and ASn finds the most recent previous occurrence of Xn — basically by finding the maximum, throughout the previous rows, of

(previous_value=this_value) * ROW())

i.e., the highest row number where the name is a match.  It then encodes the location where the name was found as

100*ROW() + COLUMN(data_we_want_to_copy)

Both formulas look in both Columns E and X, and return the encoded coordinates of the corresponding Columns M or AG cell.  Then the K and AF formulas simply decode the cell address and retrieve the value.

               

So AR6 is 213 because “John” (E6) was most recently seen on Row 2, and, since he was seen in E2 (rather than X2), we want to copy the value from Column 13 (Column M).

  • This a great answer! It solved the problem in a straightforward way (at least as much as possible), it was very clear and explained how you did it, not just "here's the answer", and taught me something I didn't know about excel. I'd say I couldn't ask for more but I'm going to. What did you use to create the pictures of the spreadsheet? I may need to use that technique for my next question. – John Biddle May 4 '15 at 14:38
  • That's simple (although a little tedious): press PrtScr (Print Screen) to get a snapshot of the screen as an image in the copy/paste buffer.  (There are other ways of doing this, such as the Snipping Tool.)  Paste into Microsoft Paint (or any other graphics editor; people seem to like Paint.NET and GIMP, and I've heard that Adobe might have something useful, too.)  Then it's straightforward to crop, draw shapes (e.g., circles, ovals, boxes, and lines), copy and paste (e.g., see here and here), and save to a file. – Scott May 4 '15 at 19:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.