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

 

[earthworm]

You need to update missing date data from Table2 to table 1 right ?

 

[Rakeshexcel]

Not exactly! I want to find lowest date in table A and move ref. No’s wherever it is found

You need to update missing date data from Table2 to table 1 right ?

 

[earthworm]

Can you give 2 examples and highlight the answer in yellow for understanding

Not exactly! I want to find lowest date in table A and move ref. No’s wherever it is found

O

 

[Rakeshexcel]

The third column in table A is the outcome am looking for.

For ex:

1. The first sample 1 record in Table B is 24-01-2021 having a ref. No of 100. If there is any date lesser to 24-01-2021 found in table A, then the Ref. No 100 should populate in Table A. In this ex, its 3-01-2021 in Table A, hence ref no. Is 100 corresponding to the date
2. Now ref no. 100 is already used, so the macro has to pick next date in Table B, find if there are any lesser date to that in Table A and move the ref. No accordingly in the corresponding least date found

Hope this helps

Can you give 2 examples and highlight the answer in yellow for understanding

O

 

[earthworm]

Try this

Book1
	A	B	C	D	E	F	G
1	Table A				Table B
2	Category	Date	Output & desired column		Category	Date	Ref. No
3	Sample 1	03-Jan-21	100		Sample 1	24-Jan-21	100
4	Sample 1	04-Jan-21	120		Sample 1	27-Jan-21	120
5	Sample 1	24-Jan-21	123		Sample 1	28-Jan-21	123
6	Sample 1	30-Jan-21	0		Sample 1	01-Feb-21	144
7	Sample 4	15-Jan-21	732		Sample 4	21-Jan-21	732
8	Sample 4	15-Jan-21	750		Sample 4	21-Jan-21	750
9	Sample 4	02-Mar-21	0		Sample 4	10-Mar-21	810
10	Sample 4	04-Mar-21	0
11	Sample 4	07-Mar-21	0
Sheet1

Cell Formulas
Range	Formula
C3:C11	C3	=IFERROR(INDEX($G$3:$G$9,SMALL(IF(B3<=$F$3:$F$9,ROW($F$3:$F$9)-ROW($F$3)+1),ROWS(B$3:B3))),0)
Press CTRL+SHIFT+ENTER to enter array formulas.

Value 850 will not populate on 2nd March of Table A because lesser date is not found in table B

 

[Rakeshexcel]

Fantastic... Just a small tweak required. The date calculation in the above formula is isolated. Date checks & ref. no extraction should happen based on the categories (Column A).

 

[earthworm]

Fantastic... Just a small tweak required. The date calculation in the above formula is isolated. Date checks & ref. no extraction should happen based on the categories (Column A).d

Fantastic... Just a small tweak required. The date calculation in the above formula is isolated. Date checks & ref. no extraction should happen based on the categories (Column A).

What is the difference i didn't get it . I only focused on the answer that you required.

 

[Rakeshexcel]

Am sorry if i din't put it across righly. i will have numerous categories in both Table A & Table B. My search should be relevant to a particular category, as i will have to run this formula for more than 1000 rows with atleast 100 different categories.

 

[earthworm]

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 ?