Restrict entries of similar dates

[nishantnambiar]

Hi friends,
I have been making this planning sheet, where the dates for planning for products should be restricted. For eg : you can make only 8 products for any particluar day, for the 9th product, the planning date should automatically either the next day or the previous day. This way on any day, the planning will be only for 8 products or less and not more. I hav tried countif, MAX, MIN but didnot help.
Can anyone help.

[JeteMc]

Hello nishantnambiar and Welcome to Excel Forum.
If you could provide us with a sample of your spreadsheet we may be able to do something to help. Be sure to manually include the expected outputs so that we can compare them to our formulas/code.
To upload a sample workbook (not a picture or pasted copy) click on the GO ADVANCED button below the Quick Reply window and then scroll down to Manage Attachments to open the upload window.
Let us know if you have any questions.

[nishantnambiar]

Hi there,
Thanks for your reply. I have attached the file and explained what is needed. Hope this information would be sufficient.

Thanks again in advance.

Regards,

Nishant Nambiar

[Richard Buttrey]

Would you manually add the results you expect to see for column F.

In post #1 you are specifying a restriction of 8 products per day yet this is exceeded in C7 but F7 still shows the 10/5/2019

Also please clarify the explanation for the Hydro test. I don't know what you expect to see when there are more than 5 products on a single day.

[JeteMc]

This is about as "Low Tech" as it gets.
1. Convert the range B3:H29 into a table,
2. Sort the delivery date column "oldest to newest",
3. Paste the following in I4 and copy down: =E4-10
4. Paste the following in J4 and double click to copy down: =ROUNDDOWN((COUNTIF(I$4:I4,I4)-1)/5,0)
5. Paste the following in K3: =COUNTIF(J4:J29,">0")
6. Paste the following in K4 and double click to copy down: =I4-J4
7. Select J3:K29 and drag the fill handle to the right until the value in row 3 is zero, as occurred in cell S3 of the attached file.
8. Paste the following in F4 and copy down to populate the "Hydro Planned Dates" column: =INDEX(K4:AA4,MATCH(0,K$3:AA$3,0))
Let us know if you have any questions.

[kvsrinivasamurthy]

ARRAY Formula in F4 then copy down.

=IFERROR(LARGE(IF((COUNTIF($F$3:$F3,($E4-9-ROW($1:$10)))<5),($E4-9-ROW($1:$10)),""),1),"")

How ARRAY formula is entered

Paste Formula in the cell.
Press F2
Hold Shift+ Ctrl Keys and hit Enter key.
Now the formula is surrounded by {} by excel.

[avk]

One point notice : column "E" & "H" Dates are not corrected format.
Choose location as : English United States.

[nishantnambiar]

Hi sir,
Thanks for your reply. This seems to be working fine for the sheet I have attached, but when I added dates further, after row 53 all the dates going blank(sheet attached for your reference).
Also if you could explain the how the formula is devised, would help me to understand it better.


Thanks in advance,


Regards,

Nishant Nambiar

[kvsrinivasamurthy]

In this formula you get 10 dates before the given date.
=IFERROR(LARGE(IF((COUNTIF($F$3:$F3,($E4-9-ROW($1:$10)))<5),($E4-9-ROW($1:$10)),""),1),"")

In this you can get 30 dates. Changed portion is in red.
=IFERROR(LARGE(IF((COUNTIF($F$3:$F3,($E4-9-ROW($1:$30)))<5),($E4-9-ROW($1:$30)),""),1),"")

If you want still more dates increase it further.