Search by fortnight

[leovilla]

Hello friends, I need a new help, I have a column with dates, another with the provider and two with values, I need to find the first and last value every fortnight.
Grateful Leandro

[JohnTopley]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[leovilla]

John follows an example

[JohnTopley]

Thank you for the file:

I need to understand the pattern: the first set of entries are 25 days apart but it is not obvious to me what the pairing is for 07 Jan, 12 Jan.

Is based on pairing the "Prod" value i.e a/f, b/b,c/c ? And what is the "Prod" column which currently has 6 and f in consecutive rows.

Can you please add more examples to your file

[leovilla]

John, 1 is the product cod, "a" is the product, I need to find its price in the quarters, the first price in the first quarter and last price in the first quarter, and so on all quarters of the year.
In the example I put the values that I need to find in Plan1.

Leandro

[JohnTopley]

See attached:

I used a "helper" column K to identify the !st/Last values for each month.

=COUNTIFS($E$2:E2,1,$F$2:F2,"a",$D$2:D2,">=" & EOMONTH(D2,-1)+1,$D$2:D2,"<=" & EOMONTH(D2,0))

in "Plan 2"

in E7

=SUMPRODUCT((Plan1!$I$2:$I$25)*(MONTH(Plan1!$D$2:$D$25)=MONTH(Plan2!E$2))*(Plan1!$K$2:$K$25=1)*(Plan1!$F$2:$F$25="a"))

in E8

=SUMPRODUCT((Plan1!$I$2:$I$25)*(MONTH(Plan1!$D$2:$D$25)=MONTH(Plan2!E$2))*(Plan1!$K$2:$K$25=2)*(Plan1!$F$2:$F$25="a"))


n F7

=SUMPRODUCT((Plan1!$J$2:$J$25)*(MONTH(Plan1!$D$2:$D$25)=MONTH(Plan2!E$2))*(Plan1!$K$2:$K$25=1)*(Plan1!$F$2:$F$25="a"))

in F8

=SUMPRODUCT((Plan1!$J$2:$J$25)*(MONTH(Plan1!$D$2:$D$25)=MONTH(Plan2!E$2))*(Plan1!$K$2:$K$25=2)*(Plan1!$F$2:$F$25="a"))

Select all 4 cells and copy paste to next month

I don't know if this is "flexible" enough for your need so maybe see if there a better way of formatting the data in "Plan 1"

[leovilla]

John, I've tried to adapt it to my worksheet, but it's not right, I'm sending you to take a look and help me.
I realized that you are adding the result, I just need the date value.

Grateful
Leandro

[leovilla]

John, Could you help me ??

[mrshl9898]

updated below

[mrshl9898]

Slight ammendment to deal with week 1.

=IF(IF(OR(WEEKNUM(J4-0)-WEEKNUM(J4-1)<-1,WEEKNUM(J4-0)-WEEKNUM(J4-1)=1),"Sun",IF(OR(WEEKNUM(J4-1)-WEEKNUM(J4-2)=1,WEEKNUM(J4-1)-WEEKNUM(J4-2)<-1),"Mon",IF(OR(WEEKNUM(J4-2)-WEEKNUM(J4-3)=1,WEEKNUM(J4-2)-WEEKNUM(J4-3)<-1),"Tues",IF(OR(WEEKNUM(J4-3)-WEEKNUM(J4-4)=1,WEEKNUM(J4-3)-WEEKNUM(J4-4)<-1),"Wed",IF(OR(WEEKNUM(J4-4)-WEEKNUM(J4-5)=1,WEEKNUM(J4-4)-WEEKNUM(J4-5)<-1),"Thurs",IF(OR(WEEKNUM(J4-5)-WEEKNUM(J4-6)=1,WEEKNUM(J4-5)-WEEKNUM(J4-6)<-1),"Fri",IF(OR(WEEKNUM(J4-6)-WEEKNUM(J4-7)=1,WEEKNUM(J4-6)-WEEKNUM(J4-7)<-11),"Sat","")))))))="Mon",TEXT(J4,"dd/mm/yyyy")&" - "&TEXT(J4+14,"dd/mm/yyyy"),IF(IF(OR(WEEKNUM(J4-0)-WEEKNUM(J4-1)<-1,WEEKNUM(J4-0)-WEEKNUM(J4-1)=1),"Sun",IF(OR(WEEKNUM(J4-1)-WEEKNUM(J4-2)=1,WEEKNUM(J4-1)-WEEKNUM(J4-2)<-1),"Mon",IF(OR(WEEKNUM(J4-2)-WEEKNUM(J4-3)=1,WEEKNUM(J4-2)-WEEKNUM(J4-3)<-1),"Tues",IF(OR(WEEKNUM(J4-3)-WEEKNUM(J4-4)=1,WEEKNUM(J4-3)-WEEKNUM(J4-4)<-1),"Wed",IF(OR(WEEKNUM(J4-4)-WEEKNUM(J4-5)=1,WEEKNUM(J4-4)-WEEKNUM(J4-5)<-1),"Thurs",IF(OR(WEEKNUM(J4-5)-WEEKNUM(J4-6)=1,WEEKNUM(J4-5)-WEEKNUM(J4-6)<-1),"Fri",IF(OR(WEEKNUM(J4-6)-WEEKNUM(J4-7)=1,WEEKNUM(J4-6)-WEEKNUM(J4-7)<-11),"Sat","")))))))="Tues",TEXT(J4-1,"dd/mm/yyyy")&" - "&TEXT(J4+14,"dd/mm/yyyy"),IF(IF(OR(WEEKNUM(J4-0)-WEEKNUM(J4-1)<-1,WEEKNUM(J4-0)-WEEKNUM(J4-1)=1),"Sun",IF(OR(WEEKNUM(J4-1)-WEEKNUM(J4-2)=1,WEEKNUM(J4-1)-WEEKNUM(J4-2)<-1),"Mon",IF(OR(WEEKNUM(J4-2)-WEEKNUM(J4-3)=1,WEEKNUM(J4-2)-WEEKNUM(J4-3)<-1),"Tues",IF(OR(WEEKNUM(J4-3)-WEEKNUM(J4-4)=1,WEEKNUM(J4-3)-WEEKNUM(J4-4)<-1),"Wed",IF(OR(WEEKNUM(J4-4)-WEEKNUM(J4-5)=1,WEEKNUM(J4-4)-WEEKNUM(J4-5)<-1),"Thurs",IF(OR(WEEKNUM(J4-5)-WEEKNUM(J4-6)=1,WEEKNUM(J4-5)-WEEKNUM(J4-6)<-1),"Fri",IF(OR(WEEKNUM(J4-6)-WEEKNUM(J4-7)=1,WEEKNUM(J4-6)-WEEKNUM(J4-7)<-11),"Sat","")))))))="Wed",TEXT(J4-2,"dd/mm/yyyy")&" - "&TEXT(J4+14,"dd/mm/yyyy"),IF(IF(OR(WEEKNUM(J4-0)-WEEKNUM(J4-1)<-1,WEEKNUM(J4-0)-WEEKNUM(J4-1)=1),"Sun",IF(OR(WEEKNUM(J4-1)-WEEKNUM(J4-2)=1,WEEKNUM(J4-1)-WEEKNUM(J4-2)<-1),"Mon",IF(OR(WEEKNUM(J4-2)-WEEKNUM(J4-3)=1,WEEKNUM(J4-2)-WEEKNUM(J4-3)<-1),"Tues",IF(OR(WEEKNUM(J4-3)-WEEKNUM(J4-4)=1,WEEKNUM(J4-3)-WEEKNUM(J4-4)<-1),"Wed",IF(OR(WEEKNUM(J4-4)-WEEKNUM(J4-5)=1,WEEKNUM(J4-4)-WEEKNUM(J4-5)<-1),"Thurs",IF(OR(WEEKNUM(J4-5)-WEEKNUM(J4-6)=1,WEEKNUM(J4-5)-WEEKNUM(J4-6)<-1),"Fri",IF(OR(WEEKNUM(J4-6)-WEEKNUM(J4-7)=1,WEEKNUM(J4-6)-WEEKNUM(J4-7)<-11),"Sat","")))))))="Thurs",TEXT(J4-3,"dd/mm/yyyy")&" - "&TEXT(J4+14,"dd/mm/yyyy"),IF(IF(OR(WEEKNUM(J4-0)-WEEKNUM(J4-1)<-1,WEEKNUM(J4-0)-WEEKNUM(J4-1)=1),"Sun",IF(OR(WEEKNUM(J4-1)-WEEKNUM(J4-2)=1,WEEKNUM(J4-1)-WEEKNUM(J4-2)<-1),"Mon",IF(OR(WEEKNUM(J4-2)-WEEKNUM(J4-3)=1,WEEKNUM(J4-2)-WEEKNUM(J4-3)<-1),"Tues",IF(OR(WEEKNUM(J4-3)-WEEKNUM(J4-4)=1,WEEKNUM(J4-3)-WEEKNUM(J4-4)<-1),"Wed",IF(OR(WEEKNUM(J4-4)-WEEKNUM(J4-5)=1,WEEKNUM(J4-4)-WEEKNUM(J4-5)<-1),"Thurs",IF(OR(WEEKNUM(J4-5)-WEEKNUM(J4-6)=1,WEEKNUM(J4-5)-WEEKNUM(J4-6)<-1),"Fri",IF(OR(WEEKNUM(J4-6)-WEEKNUM(J4-7)=1,WEEKNUM(J4-6)-WEEKNUM(J4-7)<-11),"Sat","")))))))="Fri",TEXT(J4-4,"dd/mm/yyyy")&" - "&TEXT(J4+14,"dd/mm/yyyy"),IF(IF(OR(WEEKNUM(J4-0)-WEEKNUM(J4-1)<-1,WEEKNUM(J4-0)-WEEKNUM(J4-1)=1),"Sun",IF(OR(WEEKNUM(J4-1)-WEEKNUM(J4-2)=1,WEEKNUM(J4-1)-WEEKNUM(J4-2)<-1),"Mon",IF(OR(WEEKNUM(J4-2)-WEEKNUM(J4-3)=1,WEEKNUM(J4-2)-WEEKNUM(J4-3)<-1),"Tues",IF(OR(WEEKNUM(J4-3)-WEEKNUM(J4-4)=1,WEEKNUM(J4-3)-WEEKNUM(J4-4)<-1),"Wed",IF(OR(WEEKNUM(J4-4)-WEEKNUM(J4-5)=1,WEEKNUM(J4-4)-WEEKNUM(J4-5)<-1),"Thurs",IF(OR(WEEKNUM(J4-5)-WEEKNUM(J4-6)=1,WEEKNUM(J4-5)-WEEKNUM(J4-6)<-1),"Fri",IF(OR(WEEKNUM(J4-6)-WEEKNUM(J4-7)=1,WEEKNUM(J4-6)-WEEKNUM(J4-7)<-11),"Sat","")))))))="Sat",TEXT(J4-5,"dd/mm/yyyy")&" - "&TEXT(J4+14,"dd/mm/yyyy"),IF(IF(OR(WEEKNUM(J4-0)-WEEKNUM(J4-1)<-1,WEEKNUM(J4-0)-WEEKNUM(J4-1)=1),"Sun",IF(OR(WEEKNUM(J4-1)-WEEKNUM(J4-2)=1,WEEKNUM(J4-1)-WEEKNUM(J4-2)<-1),"Mon",IF(OR(WEEKNUM(J4-2)-WEEKNUM(J4-3)=1,WEEKNUM(J4-2)-WEEKNUM(J4-3)<-1),"Tues",IF(OR(WEEKNUM(J4-3)-WEEKNUM(J4-4)=1,WEEKNUM(J4-3)-WEEKNUM(J4-4)<-1),"Wed",IF(OR(WEEKNUM(J4-4)-WEEKNUM(J4-5)=1,WEEKNUM(J4-4)-WEEKNUM(J4-5)<-1),"Thurs",IF(OR(WEEKNUM(J4-5)-WEEKNUM(J4-6)=1,WEEKNUM(J4-5)-WEEKNUM(J4-6)<-1),"Fri",IF(OR(WEEKNUM(J4-6)-WEEKNUM(J4-7)=1,WEEKNUM(J4-6)-WEEKNUM(J4-7)<-11),"Sat","")))))))="Sun",TEXT(J4-6,"dd/mm/yyyy")&" - "&TEXT(J4+14,"dd/mm/yyyy"),"")))))))



Also realised you may be looking at 26 fortnights as opposed to 52-53.... I found a solution, but it doesn't handle years with 53 weeks.. (cannot seem to upload the file)

Column Z =WEEKNUM(J4)

Column AA =ISODD(Z4)

Column AB =IF(OR(WEEKNUM(J4-0)-WEEKNUM(J4-1)<-1,WEEKNUM(J4-0)-WEEKNUM(J4-1)=1),"Sun",IF(OR(WEEKNUM(J4-1)-WEEKNUM(J4-2)=1,WEEKNUM(J4-1)-WEEKNUM(J4-2)<-1),"Mon",IF(OR(WEEKNUM(J4-2)-WEEKNUM(J4-3)=1,WEEKNUM(J4-2)-WEEKNUM(J4-3)<-1),"Tues",IF(OR(WEEKNUM(J4-3)-WEEKNUM(J4-4)=1,WEEKNUM(J4-3)-WEEKNUM(J4-4)<-1),"Wed",IF(OR(WEEKNUM(J4-4)-WEEKNUM(J4-5)=1,WEEKNUM(J4-4)-WEEKNUM(J4-5)<-1),"Thurs",IF(OR(WEEKNUM(J4-5)-WEEKNUM(J4-6)=1,WEEKNUM(J4-5)-WEEKNUM(J4-6)<-1),"Fri",IF(OR(WEEKNUM(J4-6)-WEEKNUM(J4-7)=1,WEEKNUM(J4-6)-WEEKNUM(J4-7)<-11),"Sat","")))))))

Column AC =IF(OR(WEEKNUM(J4-0)-WEEKNUM(J4-1)<-1,WEEKNUM(J4-0)-WEEKNUM(J4-1)=1),"Sun",IF(OR(WEEKNUM(J4-1)-WEEKNUM(J4-2)=1,WEEKNUM(J4-1)-WEEKNUM(J4-2)<-1),"Mon",IF(OR(WEEKNUM(J4-2)-WEEKNUM(J4-3)=1,WEEKNUM(J4-2)-WEEKNUM(J4-3)<-1),"Tues",IF(OR(WEEKNUM(J4-3)-WEEKNUM(J4-4)=1,WEEKNUM(J4-3)-WEEKNUM(J4-4)<-1),"Wed",IF(OR(WEEKNUM(J4-4)-WEEKNUM(J4-5)=1,WEEKNUM(J4-4)-WEEKNUM(J4-5)<-1),"Thurs",IF(OR(WEEKNUM(J4-5)-WEEKNUM(J4-6)=1,WEEKNUM(J4-5)-WEEKNUM(J4-6)<-1),"Fri",IF(OR(WEEKNUM(J4-6)-WEEKNUM(J4-7)=1,WEEKNUM(J4-6)-WEEKNUM(J4-7)<-11),"Sat","")))))))

[JohnTopley]

I am sorry but you example does not clarify (to me) what is required:

Take your example of "Ovalado 14x16 x 1250m"

there are 4 entries with dates of 14/03/201, 02/03/2017, 15/03/2017 and 02/03/2017

In you table in "Dados" you have entries in January (March?) which are "amounts": you say you want "dates" so should the entries be 02/03/2017 (Wed) and 15/03/2017 (Thu) which are not 14 days apart?

Why not 14/03/2017 and 02/03/2017?

So basically, I do not understand the logic behind any selection.