List Item Batch based on data with multi duplicates

[kaoticone]

Hi There.

Im trying use the list function for batch number based on their Material code. The issue is we have multiple Material codes that have the same naming but different batch numbers

I have all the data in a worksheet (that gets pulled form SharePoint) named Materials2.
The main sheet is called Lab Test Sheet Template
Example on the main sheet in column A starting at cell 28 to 52 I select the Material Code say CM600. However CM600 has multiple Batch Numbers say lot01 lot02 and lot03 located in column F in the materials2 worksheet
I want to be able to select one of those lot numbers. Same for the next cell down Cell A29 Material Code CM302 and then select its Batch Numbers. However what I've tried doesn't work.


cheers for any help

Thanks
J

 

[myall_blues]

It's not clear from the description what you want to do with it, especially since in the two pictures nothing seems to match.

Once you've picked the batch number then what? What column do you want to put it into, or is there some other property associated with the batch number you want to fill in?

Also it would be helpful if you update your profile to tell us what version of Excel you're using as it affects the solution.

 

[kaoticone]

Yer sorry mate . I wasnt that clear.

I did get it to work. Thanks internet, however it is not always matching the correct rows with each other.
So to try and keep it simple this is what its meant todo.

On the lab test worksheet there are drop down list based on items in the materials2 worksheet.

So you select a material on the dropdown, It then matches all possible Lot numbers based on the selected material, once you select that Lot number for that material it will then match the SG for that lot number.

for example
Material CAFA Lotaa SG 2.34
Material CM600 Lot01 SG 1.15
Material CM600 Lot02 SG 1.10

I select Lot 01 and it matches 1.15 . Perfect
I select Lot 02 and it matches 2.34 Not good,

It does this with a few for some reason.

Below is the VBA code
and I'll attached the worksheet . For the life of me I cant see where to upload, but here is a link to my onedrive
LAR LAB REPORT BLANK Auto save next number with tracking multiple users v8.35 -send to mrexcel.xlsm

If you could have a look and tell me why it matching some items and not others, and what I would need to change to fixe it.

Much apricated.
Thanks
J

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo ErrorHandler

    Application.ScreenUpdating = False
    Application.EnableEvents = False

    Dim cell As Range
    Dim materialCode As String
    Dim lotRange As Range
    Dim cellAddress As String
    Dim lastRow As Long
    Dim lotList As String
    
    ' Unprotect the sheet
    Me.Unprotect Password:="password"

    ' Check if the change is within the range A21:A53
    If Not Intersect(Target, Me.Range("A21:A53")) Is Nothing Then
        For Each cell In Target
            If Not IsEmpty(cell.Value) Then
                materialCode = cell.Value
                
                ' Clear existing validation
                Me.Cells(cell.Row, "C").Validation.Delete
                
                ' Find the last row with data in Materials2 sheet
                With Sheets("Materials2")
                    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
                    
                    ' Construct the formula for the drop-down list
                    lotList = "=OFFSET(Materials2!$D$21, MATCH(" & Chr(34) & materialCode & Chr(34) & ", Materials2!$A$21:$A$" & lastRow & ", 0) - 1, 0, COUNTIF(Materials2!$A$21:$A$" & lastRow & ", " & Chr(34) & materialCode & Chr(34) & "), 1)"
                    
                    ' Apply new validation
                    Me.Cells(cell.Row, "C").Validation.Add _
                        Type:=xlValidateList, _
                        AlertStyle:=xlValidAlertStop, _
                        Operator:=xlBetween, _
                        Formula1:=lotList
                End With
            End If
        Next cell
    End If
    
    ' Handle additional sheet activation and filtering
    If Me.Name = "Lab Test Sheet Template" Then
        Dim ws As Worksheet
        Dim startCell As Range

        Set ws = Me
        Set startCell = ActiveCell

        ' Ensure the worksheet is activated before selecting the range
        ws.Activate

        With ws
            .Range("T26:T347").AutoFilter Field:=1, Criteria1:="1"
            startCell.Select
        End With
    End If

    ' Reprotect the sheet
    Me.Protect Password:="password"

    Application.ScreenUpdating = True
    Application.EnableEvents = True

    Exit Sub

ErrorHandler:
    MsgBox "An error occurred: " & Err.Description
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Me.Protect Password:="password" ' Ensure protection is reapplied if an error occurs
End Sub

 

[kaoticone]

Also it doesn't have to be a VBA solution, if a normal cell level formula works better then I can just go that route.

Thanks

 

[kaoticone]

Ok so I found a solution to my problem with the SG portion

=IF($A28="", "", IFERROR(VLOOKUP($C28, Materials2!$D$23:$G$157, 2, FALSE), "Not Found"))

however I believe the VBA code doesnt like items not being in order.
In the materials2 worksheet materials are not always in order, so the batch number are not always matched.

example

A25 CM600
A26 CM600
A27 CM600
A28 CM602
A29 CM602
A30 CM600
......


A55 CM703
A56 CM703
A57 CM705
A58 CM703

would this be causing the mismatch?

thanks

 

[myall_blues]

A few questions then:

Ok so I found a solution to my problem with the SG portion

=IF($A28="", "", IFERROR(VLOOKUP($C28, Materials2!$D$23:$G$157, 2, FALSE), "Not Found"))

Where does this formula belong? Is it in F28?
Also, since you say you found a solution is there still an issue?

however I believe the VBA code doesnt like items not being in order.
would this be causing the mismatch?

No it shouldn't matter to VBA if the code is written correctly but the bigger problem is VLOOKUP can't deal with duplicates, so a different method is needed.

for example
Material CAFA Lotaa SG 2.34
Material CM600 Lot01 SG 1.15
Material CM600 Lot02 SG 1.10

I select Lot 01 and it matches 1.15 . Perfect
I select Lot 02 and it matches 2.34 Not good,

When I select CM600 in your spreadsheet I don't get Lot01, Lot02, but rather a choice of 202310, 20210201 or 5961210A in the batch no column. Is that right?

 

[myall_blues]

After playing around a bit with your spreadsheet I don't think you need all the VBA code to get the SG, especially since you're using 365.
Try this formula in F28 of the Lab Test Sheet and copy it down.

=FILTER(Materials2!$E$1:$E$155,(Materials2!$A$1:$A$155=A28)*(Materials2!$D$1:$D$155=C28)*(Materials2!$D$1:$D$155<>""),NA())

 

[kaoticone]

Thanks, that did work for the SG. Perfectly. I sort of fixed that already by sorting the data when it is imported from SharePoint which is the part I didnt want to do.
I just wanted to copy the sheet from sharepoint in a as in state
So it was more the batch numbers that didnt work when imported, if they weren't in order

above the lot numbers lot01, lot02 was just an example

so if the materials2 worksheet reads like this
A25 CM600 SG 1
A26 CM600 SG 1.1
A27 CM600 SG 1
A28 CM602 SG 2.22
A29 CM602 SG 2.50
A30 CM600 SG 1

It wont match A30 CM600 batch number or it will match SG2.5 etc. Something like that

I was just hoping for it to recognize the batch numbers no matter what order it was in and match to the correct SG

 

[kaoticone]

Thanks, that did work for the SG. Perfectly. I sort of fixed that already by sorting the data when it is imported from SharePoint which is the part I didnt want to do.
I just wanted to copy the sheet from sharepoint in a as in state
So it was more the batch numbers that didnt work when imported, if they weren't in order

above the lot numbers lot01, lot02 was just an example

so if the materials2 worksheet reads like this
A25 CM600 SG 1
A26 CM600 SG 1.1
A27 CM600 SG 1
A28 CM602 SG 2.22
A29 CM602 SG 2.50
A30 CM600 SG 1

It wont match A30 CM600 batch number or it will match SG2.5 etc. Something like that

I was just hoping for it to recognize the batch numbers no matter what order it was in and match to the correct SG

its ok, im happy with the way it is working now.

thanks for your help Murray

 

[myall_blues]

Not wishing to admit defeat, I randomised (unsorted) the data on your Materials2 sheet and came up with what's in the file linked here: LAR LAB REPORT BLANK

I have added a lookups tab, which in cell B1 contains a formula to get all the unique material codes, sorted across the page. The formula in B2, copied across the page, then gives valid batch numbers for each material code in each column.

LAR LAB REPORT BLANK.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
1	Material code	CA FA	CA FAB30	CA GSFAB30	CA GSGP	CA SLGP	CDA100	CM102	CM200	CM201	CM204	CM300	CM301	CM302	CM401	CM403	CM500	CM502	CM505	CM600	cm601	CM701	CM703	Fresh Water	G	HTB	LC01	W FAB25	W GP
2	Lot Numbers	9	3	4	5	6	GU5J0015	200403	No Lot No.	180644-1	Supplier Sample	AMC230414	Returns	30323133	23101086	23050485	No Lot No.	240401	20240323	5961210A	11122233	None found	FA24-126-TI-703	Tap Water	1606697	2	None found	7	8
3														30218134		23030173				20210201			FAB1345	Silver Springs	1
4														30518135						202310				Surat
5														30923135										Monreagh
6																								Wilgas
7																								Reedy Creek
8																								Spring Gully Concept
9																								Spring Gully WTF
10																								Bethany
11																								Kenya
12																								Woleebee Ck
13																								SummerHills Bore
14																								Rockybar WB45
15																								Atalas
Lookups

Cell Formulas
Range	Formula
B1:AC1	B1	=TRANSPOSE(SORT(UNIQUE(FILTER(Materials2!A:A,(Materials2!A:A<>"")*(Materials2!A:A<>"Material Code")))))
B2:AC2	B2	=IFERROR(FILTER(Materials2!$D:$D,(Materials2!$A:$A=B$1)*(Materials2!$D:$D<>"")),"None found")
Dynamic array formulas.

Then the data validation in columns A and C on the LAb sheet template is defined as indicated below. The lookup formula for the SG is the same as the previous one I gave you.

LAR LAB REPORT BLANK.xlsm
	A	B	C	D	E	F
26	Material Code	Component	Batch No.	Conc.	UoM	S.G.
27	CA FA	Bulk Cement	9	#VALUE!	lb/sk	2.35
28	CM600	Accelerator	5961210A	0.010	%BWOC	2.25
29	cm601	Accelerator	11122233		%BWOC	55.00
30	CM102	Retarder	200403	0.500	gal/sk	1.05
Lab Test Sheet Template

Cell Formulas
Range	Formula
F27:F30	F27	=FILTER(Materials2!$E$1:$E$155,(Materials2!$A$1:$A$155=A27)*(Materials2!$D$1:$D$155=C27)*(Materials2!$D$1:$D$155<>""),NA())

Cells with Data Validation
Cell	Allow	Criteria
C27:C30	List	=INDEX(Lookups!B$2:AZ$200,0,MATCH(A27,Lookups!B$1:AZ$1,0))
A27:A30	List	=Lookups!B$1#

Note: I've commented out the VBA code in this version as the event handlers were giving me grief.