how do you change the row order of data in a column into different row positions based upon 1 cell criteria in another column?



As a very early learner! to excel formulas - any advise offered greatly appreciated.

I have a worksheet which uses formula to import data (Football results) from another worksheet,within the workbook, which show's column data in a specific row order.

Column a / row 1 - a1 - is a specific football team name

column b - row 1 informs if the game is H or A - Home or away (this is determined if a1 matches either b2 or b5)

b2 - shows away team name

b3 - shows the home team result

b4 - shows the away team result

b5 - shows home team name

I want to show in b6 the result of the team named in a1 - irrespective of it being H or A game, and then in b7 the result of the opposing team - again irrespective of it being a H or A game - keeping the data in b1 to b5 unchanged.

This would then be applied to subsequent games played in adjacent columns.

Thanks for any help offered.



Maybe I'm just not understanding it, but you should definitely edit your question and upload a sample file for this one.
don (rep: 1959) Nov 28, '18 at 6:51 pm
Your question doesn't specify whether the sheet you describe so exactly is the target or source sheet. Anyway, it wouldn't be possible to answer your question without replicating your work. There should be no need of that. Please post your workbook.
Variatus (rep: 4402) Nov 28, '18 at 9:04 pm
Add to Discussion



You can achieve this with a couple of worksheets formulas.

In "B6" enter: =IF(A1=B2,B4,B3) which translate to - if the team in A1 = the team in B2 then enter the B4 away team result, else enter the B3 home team result.

In "B7" enter: =IF(B6=B3,B4,B3) which translate to - if B6 = B3 then enter B4 result, else enter B3 result.

Cheers   ;-}


Answer the Question

You must create an account to use the forum. Create an Account or Login