Returning a date from a range

[pbyrne462]

I have a set of three criteria, Product, Country, Date

My data appears like this:

Product 1 Country 1 01JAN11
Product 2 Country 2 01APR11
Product 3 Country 3 01JUL11
Product 4 Country 4 01OCT11

I have a seperate table which I want to sequence the countries depending on the quarters, so I have 4 columns. I need to look at the table above speficically the dates and return them in sequence depending on if they are in quarter 1 to 4. So following the example above I would have a resulting table that contains Country 1 in Q1, Country 2 in Q2 etc.

Hope this makes sense and I hope someone can help.

 

[GlennUK]

Do you have a single value per quarter?

Are your dates real Excel dates ( they look like strings to me )?

Does your output ( where you want the output ) have a specific layout with specific headings that are to be used in the calculations?

 

[pbyrne462]

No I could have multiple values per quarter, I need to pick all that apply to the Quarter and return then in date order.

they are real excel dates, I just wrote them out that way.

The layout does not have headings but I could construct some and hide them if that will help with the calculations?

 

[GlennUK]

Is the source data going to be in date order?

Can I ask what you are going to be doing with the data after it's been split into quarters? And why do you want this? I would have thought a PivotTable of the data grouping the date by quarter would have been much much more useful.

 

[pbyrne462]

the source data will be in date order yes.

The end aim is to build a picture of which countries go in which quarter. the format is quite specific for the audience that is why I can't use a pivot table

 

[GlennUK]

If the format is quite specific then post it here.

 

[pbyrne462]

I am unable to post attachments. I need the following submission dates to be represented on graph that has Quarters along the bottom and the Countries stacked above the quarter depending on the submission date.

<TABLE style="WIDTH: 170pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=226 border=0><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3145" width=86><COL style="WIDTH: 57pt; mso-width-source: userset; mso-width-alt: 2779" width=76><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: white" width=64 height=20>Product</TD><TD class=xl64 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 65pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: white" width=86>Country</TD><TD class=xl65 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 57pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: white" width=76>Submission</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: white" height=20>Product 1</TD><TD class=xl64 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: white">Country 1</TD><TD class=xl65 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: white">20-Jan-11</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: white" height=20>Product 2</TD><TD class=xl64 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: white">Country 2</TD><TD class=xl65 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: white">9-Feb-11</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: white" height=20>Product 3</TD><TD class=xl64 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: white">Country 3</TD><TD class=xl65 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: white">23-Feb-11</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: white" height=20>Product 4</TD><TD class=xl64 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: white">Country 4</TD><TD class=xl65 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: white">10-Mar-11</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: white" height=20>Product 5</TD><TD class=xl64 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: white">Country 5</TD><TD class=xl65 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: white">15-Apr-11</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: white" height=20>Product 6</TD><TD class=xl64 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: white">Country 6</TD><TD class=xl65 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: white">23-Aug-11</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: white" height=20>Product 7</TD><TD class=xl64 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: white">Country 7</TD><TD class=xl65 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: white">14-Dec-11</TD></TR></TBODY></TABLE>

 

[GlennUK]

Look at this example, and see if it makes sense to you:

Excel Workbook
	A	B	C	D	E	F	G	H
1	Product	Country	Submission	Qtr		Q4
2	Product 1	Country 1	01-Jan-11	Q1		Product	Country	Submission
3	Product 5	Country 5	02-Jan-11	Q1		Product 11	Country 11	01-Oct-11
4	Product 9	Country 9	04-Jan-11	Q1		Product 4	Country 4	02-Oct-11
5	Product 33	Country 33	02-Feb-11	Q1		Product 8	Country 8	03-Nov-11
6	Product 6	Country 6	01-Apr-11	Q2		Product 12	Country 12	21-Dec-11
7	Product 10	Country 10	05-Apr-11	Q2
8	Product 3	Country 3	01-Jul-11	Q3
9	Product 44	Country 34	02-Jul-11	Q3
10	Product 7	Country 7	03-Jul-11	Q3
11	Product 11	Country 11	01-Oct-11	Q4
12	Product 4	Country 4	02-Oct-11	Q4
13	Product 8	Country 8	03-Nov-11	Q4
14	Product 12	Country 12	21-Dec-11	Q4
Sheet38

 

[pbyrne462]

Thanks I can now get the Quarters to work but when I try to use the formula

=IF(ROW()-ROW($F$2)<=COUNTIF($D$2:$D$14,$F$1),INDEX(A$2:A$14,MATCH($F$1,$D$2:$D$14,0)+ROW()-ROW($F$2)-1),"")

it returns a #VALUE, so I am missing soemthing, could you explain the ROW()-ROW($F$2) part and +ROW()-ROW($F$2)-1. I think its nearly what I need I just need to understand how it works to apply it to my cells.

 

[T. Valko]

Thanks I can now get the Quarters to work but when I try to use the formula

=IF(ROW()-ROW($F$2)<=COUNTIF($D$2:$D$14,$F$1),INDEX(A$2:A$14,MATCH($F$1,$D$2:$D$14,0)+ROW()-ROW($F$2)-1),"")

it returns a #VALUE, so I am missing soemthing, could you explain the ROW()-ROW($F$2) part and +ROW()-ROW($F$2)-1. I think its nearly what I need I just need to understand how it works to apply it to my cells.

Using ROW()-ROW(...) makes the formula dependent on what row the formula is entered on.

Using ROWS is more robust.

=IF(ROWS(F$3:F3)>COUNTIF($D$2:$D$14,$F$1),"",INDEX(A$2:A$14,MATCH($F$1,$D$2:$D$14,0)+ROWS(F$3:F3)-1))