Solved

extract wins / losses from other worksheet

Posted on 2014-09-19
9
98 Views
Last Modified: 2014-09-22
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
Comment
Question by:Pedrov664
9 Comments
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40333830
Attachment? :-)
0
 

Author Comment

by:Pedrov664
ID: 40334387
Glen,

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

Pedro
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40334392
Yes, the attachment is missing. :-)
0
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

Author Comment

by:Pedrov664
ID: 40334406
Glen,

Please post attachment.

Thanks
0
 
LVL 21

Accepted Solution

by:
Ejgil Hedegaard earned 500 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 33

Expert Comment

by:Rob Henson
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

by:Pedrov664
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

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

This is the craziest thread...
0
 

Author Closing Comment

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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
VBA modules import 4 57
Need Help with Mapping and Toggling 3 22
assignment of laptops - risks 6 49
Excel 2010 Pivot Table Calculated Fields 2 14
This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
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…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question