1

I have some values in column B that reads like this:

Last 20
Team1
Team2
Team3
Top
Last 50
Team1
Team2

...and so on

What I want is to put the text 'Last 20' next to each team in column A. And then when it gets to last 50 I want to put 'Last 50' next each team that comes after last 50.

Is this possible? I've messed around with VLOOKUP but I don't think that is the right function.

user537137
  • 606
  • 2
  • 10
  • 23

1 Answers1

1

Put in C2 the following and copy it down:

=IF(LEFT(B2;4)="Last";"";IF(LEFT(B1;4)="Last";B1;C1))

In case you need to use commas in stead of semicolon exchange them in the formula.

K_B
  • 3,668
  • 1
  • 19
  • 29
  • Thanks, but sometimes there will be different numbers of teams. So there might be 3 teams after the 'Last 20' and there might be 6 teams after the 'Last 50'. I also need to do this in multiple sheets so it will change each time. Is there a way to find the text string 'Last 50' and then fill everything until it sees the word 'Top'? – user537137 Mar 22 '13 at 11:05
  • The code provided works fine for any number of teams, it is not clear to me what isnt working for you, please explain further – K_B Mar 22 '13 at 11:08
  • If want to also exclude the lines with "Top" then you should change the first part into `OR(LEFT(B2;4)="Last";LEFT(B2;3)="Top")` – K_B Mar 22 '13 at 11:55