Same Product Order Rank issue

[RAMU]

Dear All,
I got this file from internet, unable to understand the feature of Column "G" (Same product order Rank), & its formula. Can anyone pls explain me
Regards
RAMU

 

[HighAndWilder]

View attachment 117273

Dear All,
I got this file from internet, unable to understand the feature of Column "G" (Same product order Rank), & its formula. Can anyone pls explain me
Regards
RAMU

Are you able to use XL2BB to post a section of this sheet as shown in the image?

 

[RAMU]

Are you able to use XL2BB to post a section of this sheet as shown in the image?

Are you able to use XL2BB to post a section of this sheet as shown in the image?

Column C Formula : =DATE(RIGHT([@[Delivery dat]],4),MID([@[Delivery dat]],4,2),LEFT([@[Delivery dat]],2))
Column F Formula :
=COUNTIFS([Item],[@Item],[Converted date],"<=" &[@[Converted date]])
+[@[Required qty]]/10000000000
+COUNTIFS([Item],[@Item],[Order number],"<=" &[@[Order number]])
Column G Formula :
=IF(ISNA(VLOOKUP([@Item],Tbl_StockAlloc,2,FALSE)),"Not listed",
VLOOKUP([@Item],Tbl_StockAlloc,2,FALSE))
Column H Formula : =IF(ISNUMBER([@[Total in Stock]]),[@[Total in Stock]]-SUMIFS([Required qty],[Item],[@Item],[Same Product Order Rank],"<" & [@[Same Product Order Rank]]),0)
Column I Formula :
=IF([@[Required qty]]<[@[Total Available after Same Product Orders Filled]],
[@[Required qty]],
IF([@[Total Available after Same Product Orders Filled]]>0,
[@[Total Available after Same Product Orders Filled]],
0)
Column J Formula :
=IF(AND([@[Allocated Qty]]>0,[@[Allocated Qty]]<[@[Required qty]]),"Partially Filled",
IF([@[Allocated Qty]]=0,"Not Filled","")
)

	Table 1
A	B	C	D	E	F	G	H	I	J
Order number	Delivery dat	Converted date	Item	Required qty	Same Product Order Rank	Total in Stock	Total Available after Same Product Orders Filled	Allocated Qty	Status
CIV/SI/18-19/1831/4	08/02/2019	08-02-2019​	01-AR2-HB-BT-01	100​	6.00000001​	1,300​	1,200​	100​
CIV/SI/18-19/1831/4	08/02/2019	08-02-2019​	01-AR2-HB-BT-01	500​	6.00000005​	1,300​	1,100​	500​
BHR/SI/18-19/2054/1	30/03/2019	30-03-2019​	01-AR2-HB-BT-01	100​	5.00000001​	1,300​	1,300​	100​
BHR/SI/18-19/2054/1	29/04/2019	29-04-2019​	01-AR2-HB-BT-01	700​	6.00000007​	1,300​	600​	600​	Partially Filled
KWT/SI/18-19/2102/1	30/04/2019	30-04-2019​	01-AR2-HB-BT-01	50​	10.00000001​	1,300​	-100​	0​	Not Filled
KWT/SI/18-19/2103/1	30/04/2019	30-04-2019​	01-AZ-01-CM-01	2,000​	2.0000002​	210​	210​	210​	Partially Filled
KWT/SI/18-19/2101/1	30/04/2019	30-04-2019​	01-AZ-01-GT-01	50​	2.000000005​	125​	125​	50​
KWT/SI/18-19/2102/1	30/04/2019	30-04-2019​	01-AZ-02-BT-01	1,800​	2.00000018​	Not listed	0​	0​	Not Filled
KWT/SI/18-19/2101/1	30/04/2019	30-04-2019​	01-AZ-02-GT-01	1,800​	2.00000018​	Not listed	0​	0​	Not Filled
KWT/SI/18-19/2102/1	30/04/2019	30-04-2019​	01-AZ-TG-BT-01	1,80,000​	2.000018​	Not listed	0​	0​	Not Filled
KWT/SI/18-19/2101/1	30/04/2019	30-04-2019​	01-AZ-TG-GT-01	1,80,000​	2.000018​	20,100​	20,100​	20,100​	Partially Filled
SIMS/18-19/JI/0085/4	29/04/2019	29-04-2019​	01-JI-01-CM-01	20​	4.000000002​	Not listed	0​	0​	Not Filled
SIMS/18-19/JI/0065/10	29/04/2019	29-04-2019​	01-JI-01-CM-01	160​	3.000000016​	Not listed	0​	0​	Not Filled
SIMS/18-19/JI/0065/10	29/04/2019	29-04-2019​	01-JI-TG-CM-01	32,000​	2.0000032​	2,22,855​	2,22,855​	32,000​
GBR/SI/19-20/2158/1	30/04/2019	30-04-2019​	01-RD-AP-COM-01	5,000​	2.0000005​	4,000​	4,000​	4,000​	Partially Filled
SIMS/17-18/TT/0002/24	30/04/2019	30-04-2019​	01-TT-01-CM-02	150​	3.000000015​	300​	300​	150​
SIMS/17-18/TT/0003/21	30/04/2019	30-04-2019​	01-TT-01-CM-02	200​	4.00000002​	300​	150​	150​	Partially Filled

 

[RAMU]

View attachment 117273

Dear All,
I got this file from internet, unable to understand the feature of Column "G" (Same product order Rank), & its formula. Can anyone pls explain me
Regards
RAMU

Sorry it's column F

 

[myall_blues]

It's a strange formula but it sums values meeting certain criteria.
If we assume the headers are in row 1, then row 5 looks like this:

The first part of the formula (COUNTIFS([Item],[@Item],[Converted date],"<=" & [@[Converted date]])) counts all items in the Item column matching the item number (01-AR2-HB-BT-01) where the date is less than or equal to the date in that row (<=29/4/2019). In total there are 6 rows with dates <=29/04/2019, and of those three match the item number (rows 2, 3 and 5).
The next part ([@[Required qty]]/10000000000) divides the required quantity by 10,000,000,000 (1E10) = 0.00000007.
Finally, COUNTIFS([Item],[@Item],[Order number],"<=" &[@[Order number]]) counts the items in the Item column matching the item number (01-AR2-HB-BT-01) where order numbers are less than or equal to that order number (BHR/SI/18-19/2054/1). This I find odd as it is comparing text values rather than numbers, so B comes before C comes before D etc. Anyway, there are two of those in rows 4 and 5.
The sum of all that is 3+0.00000007+2 = 5.00000007.

Hope this helps.

 

[RAMU]

It's a strange formula but it sums values meeting certain criteria.
If we assume the headers are in row 1, then row 5 looks like this:
View attachment 117295

The first part of the formula (COUNTIFS([Item],[@Item],[Converted date],"<=" & [@[Converted date]])) counts all items in the Item column matching the item number (01-AR2-HB-BT-01) where the date is less than or equal to the date in that row (<=29/4/2019). In total there are 6 rows with dates <=29/04/2019, and of those three match the item number (rows 2, 3 and 5).
The next part ([@[Required qty]]/10000000000) divides the required quantity by 10,000,000,000 (1E10) = 0.00000007.
Finally, COUNTIFS([Item],[@Item],[Order number],"<=" &[@[Order number]]) counts the items in the Item column matching the item number (01-AR2-HB-BT-01) where order numbers are less than or equal to that order number (BHR/SI/18-19/2054/1). This I find odd as it is comparing text values rather than numbers, so B comes before C comes before D etc. Anyway, there are two of those in rows 4 and 5.
The sum of all that is 3+0.00000007+2 = 5.00000007.

Hope this helps.

Hi,
Thanks for your reply, can you pls help me to provide a better formula for column F where I shall be able to allocate qty on FIFO basis (based on order date) automatically against available stocks. Pls note in the sheet there is customised sort as per the image below, but I don't want to use converted date Column as in my sheet the delivery date column is formatted as Date ("dd"-"mm"-"yy). And since I shall not use converted Date column, then the formula will be used in Column E now, And my sheet is not converted to Table also

Thanks in advance

 

[myall_blues]

Hi Ramu

Rather than starting from something not made for your situation, it would be easiest if you posted examples of what your data looks like as well as an example of the result you are trying to achieve. Then we can have a go at building something suitable.
Post examples with XL2BB if possible.

 

[RAMU]

Hi Ramu

Rather than starting from something not made for your situation, it would be easiest if you posted examples of what your data looks like as well as an example of the result you are trying to achieve. Then we can have a go at building something suitable.
Post examples with XL2BB if possible.

Hello,
I tried to install XL2BB but unfortunately repeatedly an error is coming, Am using MS office 365
If i share a link of dropbox will it be helpful ?
Regards
RAMU

 

[myall_blues]

Yes that’s fine.

 

[RAMU]

Yes that’s fine.

This is the link

Dropbox

www.dropbox.com