Generating a list of sports opponents from database

[rubsley]

Let's say I have a database containing a large number of sporting matchups, like this:

sample2.JPG

I want to be able to generate a list of opponents and match numbers for any given participant (ie, what's shown in 'output) - basically, if player = x and match = y, put the opponent's name in the box next to y.

I've been trying to do it using SUMIF or SUMPRODUCT, but I don't seem to be getting anywhere.

Help please?

[azumi]

Dont' put an image better if you upload your example excel file, click on "Go Advanced" button then scroll down to find "Manage Attachment" text button to attach the file


thank you

[rubsley]

Oops. I thought I'd attached it but had actually attached the jpg (same file name).

Done now. Thanks for the notice.

[azumi]

Deleted Post

[FlameRetired]

Try in G22 and fill down. I strongly suspect there is a simpler, more elegant solution. I just cannot seem to come up with it tonight.

Please Login or Register  to view this content.

[Gregb11]

Also not elegant, but uses helper columns.
If in Column E and Column H you concatenate the names/Match# (E8 would be =C8&D8 and copied down)
Then in G22 and copied down you could try:
=IFERROR(IFERROR(INDEX($F$8:$F$14,MATCH($D$20&F22,$E$8:$E$14,0)),INDEX($C$8:$C$14,MATCH($D$20&F22,$H$8:$H$14,0))),"")

[FlameRetired]

Gregb11 if you are not aware of it the IFERROR function isn't available in Excel 2003.

[Gregb11]

Oops. Sorry. Thanks for letting me know.

[FlameRetired]

If you are interested you might find this link helpful. https://support.office.com/en-us/art...6-c6d90033e188

[Gregb11]

Excellent - thank you!

[FlameRetired]

This is simpler than my previous formula.

Please Login or Register  to view this content.

[FlameRetired]

@ Gregb11
You are welcome. Glad to help.

[rubsley]

Thanks for this, really appreciate it - I see that it works great for what I posted.

Just wondering: what would I change (apart from the range) to accommodate it for up to around 200 matches? Is it the (1;1;1;1;1) part? Would that get really really long? And need updating when new matches get added?

I've also been looking at this one:

=INDEX($F$8:$F$14,MATCH(1,INDEX(($C$8:$C$14=$D$20)*($D$8:$D$14=F22),0,1),0))

That works fine for when the player in question is in the home column, and seems like it should be easy enough to make it work for both - though my way of doing it is generally pretty clunky; something like:

=IF(ISERROR(INDEX($F$8:$F$14,MATCH(1,INDEX(($C$8:$C$14=$D$20)*($D$8:$D$14=F22),0,1),0))),(INDEX($C$8:$C$14,MATCH(1,INDEX(($F$8:$F$14=$D$20)*($G$8:$G$14=F22),0,1),0))),(INDEX($F$8:$F$14,MATCH(1,INDEX(($C$8:$C$14=$D$20)*($D$8:$D$14=F22),0,1),0))))

Thanks again. Look forward to hearing your thoughts on this.

[FlameRetired]

That array constant should be {1;1;1;1} (4 ones) because you are dealing with two offsetting ranges C8:F14 and D8:G14 whose overlap is 4 columns. Therefore you should not have to change that no matter how many matches you have to accommodate ... double check the syntax of MMULT.

Try array entering this shorter version of the formula.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Please Login or Register  to view this content.

You may find that you do not have to array enter this. You can try and see. My 2007 version is in another building and I am operating here with version 365 (which does not require Ctrl + Shift + Enter most of the time.) so I cannot test this myself.

[rubsley]

Ah, that's good news that nothing needs changing depending on the size of the field. Phew.

Do you know what I need to change to expand it to the full database? The sample has seven rows and when I add one more it seems I have to add an eighth "1" in the second array to make it work. But the actual database has around 5000 rows.

Trying to avoid array formulas.

[FlameRetired]

I have two questions.

1.) When you say "trying to avoid array formulas" are you trying to avoid Ctrl + Shift + Enter or is it something else? If you are not aware of it there are non-CSE array formulas.

2.) In that last formula there should be no need for the 7 {1;1;1;1;1;1;1}. Check the syntax rules for MMULT. Since there will always and only be 4 relevant columns to work with the size of the database should not matter.

I can make an non-CSE version of than last formula. Would you like that?

Or am I miss-understanding something?

Perhaps what you need are Dynamic Named Ranges. They automatically resize to fit the data. Is that it?