Go Premium for a chance to win a PS4. Enter to Win

x
Solved

# extract wins / losses from other worksheet

Posted on 2014-09-19
Medium Priority
105 Views
Hi everyone,

The attached file contains wins/losses versus a specific team in line 1 of data. For instance, D1 says 'BOS' so B1 and C1 contain the wins losses of BAL (B3) versus BOS (D1).

FYI: all teams in row 1 are changed to reflect the vs team. All teams in row 3 are permanently there to reflect which team the score is for.

What I would like to do is to be able to do this in the sheet named 'Team W-L'. For instance, to populate 'E6' I need to find where 'LAD' (Z3) is located then find all the times it played versus 'CHC' (AB1) and put the wins in E6 and losses in 'F6'.

FYI: D and G columns determine which team is compared to which in the 'Team W-L' sheet. The results from the 2014-2 sheet go in 'EF' and 'HI' for each respective team. The first row of data is manually done to show the expected result.

The other teams should be populated similarly.
0
Question by:Pedrov664

LVL 27

Expert Comment

ID: 40333830
Attachment? :-)
0

Author Comment

ID: 40334387
Glen,

Am I missing something? i.e. attachment missing?

Pedro
0

LVL 27

Expert Comment

ID: 40334392
Yes, the attachment is missing. :-)
0

Author Comment

ID: 40334406
Glen,

Thanks
0

LVL 23

Accepted Solution

Ejgil Hedegaard earned 2000 total points
ID: 40334457
Guess it is the same file as Team-Loss-in-Red.
Team in row 3 and W/L in row 1 for the selected compare team.
The formula to get the win (E6) is =INDEX('2014-2'!\$1:\$1,1,MATCH(\$D6,'2014-2'!\$3:\$3,0))
Similar for the other, see file.
Team-Loss-in-red.xlsx
0

LVL 34

Expert Comment

ID: 40336336
Pedrov664 - In your question you quote "In the attached file", Glenn Ray was pointing out that you had omitted to attach a file. How is Glenn supposed to attach the file that you have omitted, it is your file.

Hopefully, Ejgil has made the right assumption regarding the required file in the last comment.

Thanks
Rob H
0

Author Comment

ID: 40336367
Rob,

Thanks for pointing that out. You are right the same file can be used.

That said ejgil's formula does work.

Thanks,
0

LVL 27

Expert Comment

ID: 40336971
Pedro,  If ejgil's solution works, please properly close this thread by clicking the "Accept this solution" link above his post.

0

Author Closing Comment

ID: 40337780
short, sweet and to the point.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns overâ€¦
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaacâ€¦
###### Suggested Courses
Course of the Month6 days, 7 hours left to enroll