+ Reply to Thread
Results 1 to 9 of 9

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

  1. #1
    Forum Contributor
    Join Date
    02-27-2015
    Location
    Los Angeles, California
    MS-Off Ver
    office 2010 pro
    Posts
    238

    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.

    Thanks in advance!
    Attached Files Attached Files
    Last edited by RachelMads02; 06-26-2017 at 10:55 PM.

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,518

    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. #3
    Forum Contributor
    Join Date
    02-27-2015
    Location
    Los Angeles, California
    MS-Off Ver
    office 2010 pro
    Posts
    238

    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. #4
    Forum Contributor
    Join Date
    02-27-2015
    Location
    Los Angeles, California
    MS-Off Ver
    office 2010 pro
    Posts
    238

    Re: Array Formula Help

    I'm assuming this can't be done?

  5. #5
    Forum Contributor
    Join Date
    02-27-2015
    Location
    Los Angeles, California
    MS-Off Ver
    office 2010 pro
    Posts
    238

    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.
    Last edited by RachelMads02; 06-26-2017 at 08:07 PM.

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,518

    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
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    02-27-2015
    Location
    Los Angeles, California
    MS-Off Ver
    office 2010 pro
    Posts
    238

    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?
    Last edited by RachelMads02; 06-27-2017 at 08:44 PM.

  8. #8
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,518

    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"
    Attached Files Attached Files
    Last edited by JohnTopley; 06-28-2017 at 03:55 AM.

  9. #9
    Forum Contributor
    Join Date
    02-27-2015
    Location
    Los Angeles, California
    MS-Off Ver
    office 2010 pro
    Posts
    238

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

    seems like it works, thank you

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Transfer data to closed workbook based on more conditions
    By Villalobos in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 02-20-2017, 01:39 PM
  2. Replies: 29
    Last Post: 01-01-2017, 10:25 AM
  3. Transfer data to specific range based on condition
    By khatibcell in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-16-2013, 05:46 AM
  4. Replies: 2
    Last Post: 02-20-2013, 12:40 PM
  5. Replies: 5
    Last Post: 10-15-2012, 06:33 PM
  6. [SOLVED] Add cells from a range based on 2 conditions from 2 other ranges
    By Kelly in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 02:05 AM
  7. [SOLVED] Add cells from a range based on 2 conditions from 2 other ranges
    By Kelly in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM

Bookmarks

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