Formula to transfer data from one range of cells to another based on conditions

1. Formula to transfer data from one range of cells to another based on conditions

Hi guys,

This thread is all based on the American sport of college basketball. Every year there's a tournament of college basketball teams to decide 1 champion. It's designed in a bracket format. Every team is ranked from 1-16 with 1 being the better team and 16 being the worst team. I am looking for a formula (I'm pretty sure it's an array formula) to transfer data from one range of cells to another range based on certain conditions.

I've uploaded two workbooks. The first workbook is titled "Example" and it contains an example of what I'm trying to achieve. The second workbook is titled "Working" and it's the workbook that needs assistance.

Let me briefly explain the "Example" workbook:

On the sheet "#1 vs #16" you will notice that anytime the value in column I is greater than the value in Column H the row's font changes to red and the value in the corresponding K column is negative. This is indicating that the team in the corresponding F cell has beaten the corresponding team in C cell. That's pretty self explanatory. Using line 4, you can notice that when K4 <0:

P4 shows the value from F4
Q4 shows the value from C4
R4 shows the values from I4 & H4 with a "-" in between.

*obviously the process repeats itself across row 4 depending on how many upsets occur that year.

I'm trying to replicate this same outcome in the "Working" workbook yet there are additional conditions and possibilities that create more depth to this formula. This time Table2 contains more columns (Columns C and G are new). These columns are identifying the rankings of specific teams. Cell C4 = 1 which is saying that St. John's (Cell D4) is ranked as #1. Cell G4 = 9 which is saying that Arkansas (Cell H4) is ranked as #9. If i copy the formula from the "Example" workbook and adjust for the cell lettering changes everything works. The problem is when Column C or G is adjusted (In this example it doesn't matter if cell G is adjusted, but in the future it can). If cell C4 is changed from 1 to 16 than R4:V4 will not accurately display. Why? Because the formula does not account for columns C and G. Column C will only ever show one of two values: 1 or 16, while Column G will only ever show a value of 8 or 9.

If you're wondering what the purpose of R4:V4 is it's basically a summary of the games that resulted in an upset from a specific year.

The formula understands that an upset occurs because M <0, but R4:V4 are returning the wrong values. They're returning values based on not having Columns C & G. Try it out; Adjust cell C4 to 16 and the formula still believes that H4 is the lesser ranked team (the team that upsets) hence why S4 returns the value from H4 when it should be returning the value from D4 because cell C4 is now 16. That's a lot to digest so in simple terms, if St John's (Cell D4) is ranked as #1 and it loses to Arkansas (Cell H4) who is ranked as #9 than the lesser team (Arkansas) has created an upset. St. John's (Ranked #1) lost to Arkansas (Ranked #9). Now if you change C4 from 1 to 16 than St John's is now ranked as a #16 and as it stands St. John's beats Arkansas. This means that an upset occurs because a #16 team (St. John's) has beat a better ranked team #9 (Arkansas). When this happens S4 should be returning D4 (St. John's) rather than H4 (Arkansas) and so on. Here are the conditions:

If C4 = 1, and if G4 = 8 or 9, and if M4<0 than the result should be:
R4 = G4
S4 = H4
T4 = C4
U4 = D4
V4 = K4 "-" J4

If C4 = 16, and if G4 = 8 or 9, and if M4<0 than the result should be:
R4 = C4
S4 = D4
T4 = G4
U4 = H4
V4 = J4 "-" K4

This has to follow the same formula format used in the "Example" workbook.

* Range R5:AN5 represents the data in range A8:M11 as an example of how the workbook should look.

2. Re: Array Formula Help

Isn't the "Upset" determined by your value in Column M i.e. M<0 indicates an "Upset".?

If so, the formulae are required are of the same form as those in "Example": you can test if M<0 in the SMALL (IF( .......) condition.

e.g SMALL(IF((M <0)*(Year=YearList),........

3. Re: Array Formula Help

Yes the upset is determined by whether or not the M column is < 0.

I'm not sure what i have to replace by your example. Can you elaborate?

4. Re: Array Formula Help

I'm assuming this can't be done?

5. Re: Array Formula Help

that doesn't work because if you change a cell in the C column from 1 to 16 it won't accurately display the upset. I've edited the original post to make it easier to understand.

6. Re: Formula to transfer data from one range of cells to another based on conditions

See attached:

helper column in Q

=IF(M4<0,B4&COUNTIFS(\$M\$4:M4,"<"&0,\$B\$4:B4,B4)&IF(C4=1,"070803041110","030407081011"),"")

to give results in form

yyyynaabbccddeeff

yyyy+ year from B

n=count of "Upsets" (incremented for one in each year)

aa=column number for data in R

bb=column number for dat in ss

etc

In R! and copy across

=INT((COLUMNS(\$R:R)-1)/6)+1

in R4

=IFERROR(INDEX(\$A\$4:\$K\$200,MATCH(\$O4&R\$1&"*",\$Q\$4:\$Q\$200,0),MID(INDEX(\$Q\$4:\$Q\$200,MATCH(\$O4&R\$1&"*",\$Q\$4:\$Q\$200,0)),6,2)+0),"")

Repeated for columns S-U changing highlighted by 2 each time i.e. 8,10,12

in V4

=IFERROR(INDEX(\$A\$4:\$K\$200,MATCH(\$O5&V\$1&"*",\$Q\$4:\$Q\$200,0),MID(INDEX(\$Q\$4:\$Q\$200,MATCH(\$O5&V\$1&"*",\$Q\$4:\$Q\$200,0)),14,2)+0),"") & " - " &IFERROR(INDEX(\$A\$4:\$K\$200,MATCH(\$O5&U\$1&"*",\$Q\$4:\$Q\$200,0),MID(INDEX(\$Q\$4:\$Q\$200,MATCH(\$O5&U\$1&"*",\$Q\$4:\$Q\$200,0)),16,2)+0),"")

Select columns R tot V and copy/paste to next group

7. Re: Formula to transfer data from one range of cells to another based on conditions

it doesn't work for line 4. if you change c4 to 16 r4:v4 returns 16. i take it the only way to achieve the desired result is with a helper column?

8. Re: Formula to transfer data from one range of cells to another based on conditions

It does ... if I had changed the formulae correctly!! Did you check them out ????

I could not think of way of doing this with an array formula because of the need to change columns. However, it might be possible.

I changed the IFERROR on "Score" to remove "-" if no results apply ..

in V4

=IFERROR(INDEX(\$A\$4:\$K\$200,MATCH(\$O4&V\$1&"*",\$Q\$4:\$Q\$200,0),MID(INDEX(\$Q\$4:\$Q\$200,MATCH(\$O4&V\$1&"*",\$Q\$4:\$Q\$200,0)),14,2)+0) & " - " &INDEX(\$A\$4:\$K\$200,MATCH(\$O4&U\$1&"*",\$Q\$4:\$Q\$200,0),MID(INDEX(\$Q\$4:\$Q\$200,MATCH(\$O4&U\$1&"*",\$Q\$4:\$Q\$200,0)),16,2)+0),"")

Copy to other "Scores"

9. Re: Formula to transfer data from one range of cells to another based on conditions

seems like it works, thank you

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1