VBA First in First out by dates

[Rakeshexcel]

Hello,

I am trying to extract a reference number from a database using FIFO method. Have tried numerous ways, while i could get to write some code with google ref., i couldn't really achieve the full result. Please help me.

I am sharing the details along with codes i had worked. To assist, i have pre-populated expected values in "Output & desired column" in which the vba has to throw same results. Please feel free to erase the details in this column

Step 1. I want the code to lookup dates from each category in Table B and see if there are any dates lesser to the one its searching in same category from Table A
Step 2. If found, the Ref. No from Table B should updated in Table A, corresponding to column of lesser date found
Step 3. If no lesser dates are found in Table A for a particular category, VBA to pick the next date & do the similar step 1 & 2
Step 4. Once the above step is completed for a particular category, VBA to pick next category.

Imp Note: If a lesser date found in Table A, the ref. no from Table B needs to be populated in output column from Table A. Once the Ref. no is used, VBA should pick the next date only.

TABLE A:

Category	Date	Output & desired column
Sample 1	03-Jan-21	100
Sample 1	04-Jan-21	120
Sample 1	24-Jan-21	123
Sample 1	30-Jan-21	0
Sample 4	15-Jan-21	732
Sample 4	15-Jan-21	750
Sample 4	02-Mar-21	810
Sample 4	04-Mar-21	0
Sample 4	07-Mar-21	0

TABLE B:

Category	Date	Ref. No
Sample 1	24-Jan-21	100
Sample 1	27-Jan-21	120
Sample 1	28-Jan-21	123
Sample 1	01-Feb-21	144
Sample 4	21-Jan-21	732
Sample 4	21-Jan-21	750
Sample 4	10-Mar-21	810

Code:
Sub test()
Dim srow As Range
Dim sdate, sdate1 As Date
Dim chk As String
Dim sl_no As String
Set Rng = Selection
'Loop through sheet B data
Range("B1").Sort key1:=Sheets(2).Range("B1"), Order1:=xlAscending, Header:=xlYes
For Each cel In ThisWorkbook.Sheets("B").Range("A2:C" & Cells(Rows.Count, 1).End(xlUp).Row).SpecialCells(xlCellTypeVisible).Rows
sdate = Sheets(2).Range("B" & cel.Row).Value
sl_no = Sheets(2).Range("C" & cel.Row).Value
MsgBox sdate


flag1:
For Each cel1 In ThisWorkbook.Sheets("A").Range("A2:C" & Cells(Rows.Count, 1).End(xlUp).Row).SpecialCells(xlCellTypeVisible).Rows
chk = Sheets(1).Range("C" & cel1.Row).Value
If chk = "" Then
sdate1 = Sheets(1).Range("B" & cel1.Row).Value
If sdate1 < sdate Then
Sheets(1).Range("C" & cel1.Row).Value = sl_no
End If
MsgBox "Sheet A" & sdate1
End If
Next cel1
Next cel
End Sub

 

[Rakeshexcel]

I did not understand your question. but yes, the logic has to be based on both category and Date. At present, the formula works based on date only

So you mean to say you need to apply logic based on category and date . Hmmm

I suggest to keep Table B as a based that keeps updating each time in table A . but for that Table A needs to be start from Scratch . is that possible ?

 

[Rakeshexcel]

Hello, am not sure if you have stopped working on this & i know you would have drained your energy on this. But i really need this, thanks for all your support

 

[earthworm]

Hello, am not sure if you have stopped working on this & i know you would have drained your energy on this. But i really need this, thanks for all your support

I don't see another scenario where your second logic will apply.

If you can share another small sample with both logic applicable maybe then I can assist.

also give answer table as to how you want the data to appear

 

[Rakeshexcel]

HI,

thanks for responding. have attached a different sample also provided the logic to be in table B that can be referred. Column D in table A is the expected outcome

Table A					Table B
A	B	C	D		F	G	H	I
Category	Ref. No	Date	Output & desired column		Category	Ref. No	Date	Logic
Sample 1	1100	03-Jan-21	100		Sample 1	100	24-Jan-21	Ref. no alloted to D3, lesser than date found in sample 1 of table A
Sample 1	1212	04-Jan-21	120		Sample 1	120	27-Jan-21	Ref. no alloted to D4, next lesser than date found in sample 1 of table A
Sample 1	1304	15-Feb-21	156		Sample 1	123	28-Jan-21	Ref. no NOT ALLOTED, no next lesser date found in sample 1 of table A
Sample 1	1322	20-Feb-21	163		Sample 1	144	01-Feb-21	Ref. no NOT ALLOTED, no next lesser date found in sample 1 of table A
Sample 4	1948	15-Jan-21	732		Sample 1	156	19-Feb-21	Ref. no alloted to D5, next lesser than date found in sample 1 of table A
Sample 4	2249	15-Jan-21	750		Sample 1	163	01-Mar-21	Ref. no alloted to D6, next lesser than date found in sample 1 of table A
Sample 4	2304	02-Mar-21	810		Sample 4	732	21-Jan-21	Ref. no alloted to D7, lesser than date found in sample 4 of table A
Sample 4	4325	04-Mar-21	0		Sample 4	750	21-Jan-21	Ref. no alloted to D8, lesser than date found in sample 4 of table A
Sample 4	3042	07-Mar-21	0		Sample 4	810	10-Mar-21	Ref. no alloted to D9, lesser than date found in sample 4 of table A
Sample 7	3222	03-Jan-21	900		Sample 7	900	28-Jan-21	Ref. no alloted to D12, lesser than date found in sample 7 of table A
Sample 7	3948	04-Jan-21	901		Sample 7	901	28-Jan-21	Ref. no alloted to D13, lesser than date found in sample 7 of table A
Sample 7	2002	08-Mar-21	0		Sample 7	921	14-Jan-21	Ref. no NOT ALLOTED, no next lesser date found in sample 7 of table A
Sample 7	212	20-Mar-21	0
Sample 7	492	22-Mar-21	0
Sample 7	2035	23-Mar-21	0

 

[bebo021999]

Table B:
- Is the last Ref of Sample 7 14-Feb-21? I am assuming date is increasing per specific sample.
- Is the Ref unique thru out all categoried? I found no duplicate Ref in table B

 

[Rakeshexcel]

Yes thats right, dates are sorted from oldest to newest and Ref No. is unique in table B.

But where did you find 14-Feb-21 in sample 7? I assume you meant 14-Jan-21 as last ref in sample 7, if yes then 14-Jan-21 is the last ref in sample 7. Similarly, sample 4 last ref. is 10-mar-21 and sample 1 last ref. is 1-mar-21

 

[earthworm]

HI,

thanks for responding. have attached a different sample also provided the logic to be in table B that can be referred. Column D in table A is the expected outcome

Table A					Table B
A	B	C	D		F	G	H	I
Category	Ref. No	Date	Output & desired column		Category	Ref. No	Date	Logic
Sample 1	1100	03-Jan-21	100		Sample 1	100	24-Jan-21	Ref. no alloted to D3, lesser than date found in sample 1 of table A
Sample 1	1212	04-Jan-21	120		Sample 1	120	27-Jan-21	Ref. no alloted to D4, next lesser than date found in sample 1 of table A
Sample 1	1304	15-Feb-21	156		Sample 1	123	28-Jan-21	Ref. no NOT ALLOTED, no next lesser date found in sample 1 of table A
Sample 1	1322	20-Feb-21	163		Sample 1	144	01-Feb-21	Ref. no NOT ALLOTED, no next lesser date found in sample 1 of table A
Sample 4	1948	15-Jan-21	732		Sample 1	156	19-Feb-21	Ref. no alloted to D5, next lesser than date found in sample 1 of table A
Sample 4	2249	15-Jan-21	750		Sample 1	163	01-Mar-21	Ref. no alloted to D6, next lesser than date found in sample 1 of table A
Sample 4	2304	02-Mar-21	810		Sample 4	732	21-Jan-21	Ref. no alloted to D7, lesser than date found in sample 4 of table A
Sample 4	4325	04-Mar-21	0		Sample 4	750	21-Jan-21	Ref. no alloted to D8, lesser than date found in sample 4 of table A
Sample 4	3042	07-Mar-21	0		Sample 4	810	10-Mar-21	Ref. no alloted to D9, lesser than date found in sample 4 of table A
Sample 7	3222	03-Jan-21	900		Sample 7	900	28-Jan-21	Ref. no alloted to D12, lesser than date found in sample 7 of table A
Sample 7	3948	04-Jan-21	901		Sample 7	901	28-Jan-21	Ref. no alloted to D13, lesser than date found in sample 7 of table A
Sample 7	2002	08-Mar-21	0		Sample 7	921	14-Jan-21	Ref. no NOT ALLOTED, no next lesser date found in sample 7 of table A
Sample 7	212	20-Mar-21	0
Sample 7	492	22-Mar-21	0
Sample 7	2035	23-Mar-21	0

Is this what you want

Book1
	A	B	C	D	E	F	G	H	I
1	Table A					Table B
2	A	B	C	D		F	G	H	I
3	Category	Ref. No	Date	Output & desired column		Category	Ref. No	Date	Logic
4	Sample 1	1100	03-Jan-21	100		Sample 1	100	24-Jan-21	Ref. no alloted to D3, lesser than date found in sample 1 of table A
5	Sample 1	1212	04-Jan-21	120		Sample 1	120	27-Jan-21	Ref. no alloted to D4, next lesser than date found in sample 1 of table A
6	Sample 1	1304	15-Feb-21	750		Sample 1	123	28-Jan-21	Ref. no NOT ALLOTED, no next lesser date found in sample 1 of table A
7	Sample 1	1322	20-Feb-21	0		Sample 1	144	01-Feb-21	Ref. no NOT ALLOTED, no next lesser date found in sample 1 of table A
8	Sample 4	1948	15-Jan-21	144		Sample 1	156	19-Feb-21	Ref. no alloted to D5, next lesser than date found in sample 1 of table A
9	Sample 4	2249	15-Jan-21	156		Sample 1	163	01-Mar-21	Ref. no alloted to D6, next lesser than date found in sample 1 of table A
10	Sample 4	2304	02-Mar-21	0		Sample 4	732	21-Jan-21	Ref. no alloted to D7, lesser than date found in sample 4 of table A
11	Sample 4	4325	04-Mar-21	0		Sample 4	750	21-Jan-21	Ref. no alloted to D8, lesser than date found in sample 4 of table A
12	Sample 4	3042	07-Mar-21	0		Sample 4	810	10-Mar-21	Ref. no alloted to D9, lesser than date found in sample 4 of table A
13	Sample 7	3222	03-Jan-21	900		Sample 7	900	28-Jan-21	Ref. no alloted to D12, lesser than date found in sample 7 of table A
14	Sample 7	3948	04-Jan-21	901		Sample 7	901	28-Jan-21	Ref. no alloted to D13, lesser than date found in sample 7 of table A
15	Sample 7	2002	08-Mar-21	0		Sample 7	921	14-Jan-21	Ref. no NOT ALLOTED, no next lesser date found in sample 7 of table A
16	Sample 7	212	20-Mar-21	0
17	Sample 7	492	22-Mar-21	0
18	Sample 7	2035	23-Mar-21	0
Sheet1

Cell Formulas
Range	Formula
D4:D18	D4	=IFERROR(INDEX($G$4:$G$15,AGGREGATE(15,6,ROW($H$4:$H$15)-ROW($H$4)+1/(C4<=$H$4:$H$15)*($F$4:$F$15=$A4),ROWS(C$4:C4))),0)

 

[Rakeshexcel]

Not exactly. Cell D6 (Sample 1) has picked up the ref. no from sample 4, while it has to search lesser date within sample 1 only.

 

[Rakeshexcel]

Corrected statement:
Not exactly. Cell D6 (Sample 1) has picked up the ref. no from sample 4, while it has to search next higher date within sample 1 only.