if condition (date range) met in one column, returning values in adjacent columns

CR1989

New Member
Joined
Jun 6, 2014
Messages
26
Hi

i have the following:

B1 C1 D1
[TABLE="width: 196"]
<tbody>[TR]
[TD="align: right"]21/08/2013[/TD]
[TD]Chelsea[/TD]
[TD]Aston Villa[/TD]
[/TR]
[TR]
[TD="align: right"]24/08/2013[/TD]
[TD]Aston Villa[/TD]
[TD]Liverpool[/TD]
[/TR]
[TR]
[TD="align: right"]24/08/2013[/TD]
[TD]Everton[/TD]
[TD]West Brom[/TD]
[/TR]
[TR]
[TD="align: right"]24/08/2013[/TD]
[TD]Fulham[/TD]
[TD]Arsenal[/TD]
[/TR]
[TR]
[TD="align: right"]24/08/2013[/TD]
[TD]Hull[/TD]
[TD]Norwich[/TD]
[/TR]
[TR]
[TD="align: right"]24/08/2013[/TD]
[TD]Newcastle[/TD]
[TD]West Ham[/TD]
[/TR]
[TR]
[TD="align: right"]24/08/2013[/TD]
[TD]Southampton[/TD]
[TD]Sunderland[/TD]
[/TR]
[TR]
[TD="align: right"]24/08/2013[/TD]
[TD]Stoke[/TD]
[TD]Crystal Palace[/TD]
[/TR]
[TR]
[TD="align: right"]25/08/2013[/TD]
[TD]Cardiff[/TD]
[TD]Man City[/TD]
[/TR]
[TR]
[TD="align: right"]25/08/2013[/TD]
[TD]Tottenham[/TD]
[TD]Swansea[/TD]
[/TR]
[TR]
[TD="align: right"]26/08/2013[/TD]
[TD]Man United[/TD]
[TD]Chelsea[/TD]
[/TR]
</tbody><colgroup><col><col><col></colgroup>[/TABLE]

i want to analyse the statistics of the games above by each round (ie 10 games) but as you can see the dates of each game are staggered so its difficult to isolate them by each round. as i want to do this over several years I want to avoid doing it manually.

Is threre any way that i could formulate a cell such that if the dates in column B fall into a certain date range (say 21/08<=21/08+6), that it could return the two values from column C and D? also it doesnt necessarily have to be by date, if i could split the data once one full combination of the twenty values is displayed that would work as well.

I could only come up with this so far =IF($B$2<=$B$2+6,C2,0) but this just returns the list of values in the original column that i have in the first place. any suggestions?
 
Hi, not sure to understand your query, you want to return the values from Column B and Column C for all that fit the date in between the 21st and the 27th of August?
Wouldn't a pivot table give you that kind of results?
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top