Stock Check Multiple Variables and Pulling Information Through

[lawsonj08]

Hello,

I'm new to VBA, so apologies if this seems a simple one.!

I'm currently working on a stock check spreadsheet which contain a stock list with datasheets numbers (these would be typical numbers, but the cell may contain more than one) and size, and a requirements sheet with a typical datasheet (single value typically but may be more than), and a size.

I have wrote the below:-

Sub stock_check()


Sheets("Armada").Select


Dim datasheet_armada As Range
Dim datasheet_stock As Range


Set datasheet_armada = Range("ac1:aC6000")
Set datasheet_stock = Sheets("Stock").Range("E1:E6000")


For Each cell In datasheet_armada


If cell.Value Like "*" & datasheet_stock & "*" And (cell.Offset(0, -21).Value = datasheet_stock.cell.Offset(0, 5).Value) Then


cell.Offset(0, 34).Value = datasheet_stock.Cells.Offset(0, -2).Value


End If
Next cell
End Sub

What the aim of this is that it would check the datasheet in the initial range against the datasheet on the stock tab. If it finds a match, i want it to check the valve that is offset on both and see if they match and if they do, i want it to pull information from the stock tab (a unique reference to the specific item)

Some sample data would be as follows:-
Sizes - will be inches so 2", 4", etc
Datasheets - will be BA-F01, BA-F02 etc (note these will not be exact matches as one cell may contain more than one)
Unique Reference - SPR-123456-0001, VLV-123456-0002

If you could point me in the right direction; it would be appreciated.

Thanks!

 

[mumps]

Can you post a screen shot of what your data on the 2 sheets looks like? Section B at this link has instructions on how to post a screen shot: https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html Alternately, you could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells, rows, columns and worksheets. If the workbook contains confidential information, you could replace it with generic data.

 

[lawsonj08]

Thanks for replying, I've uploaded a dump of the two spreadsheets and columns and matched some to try and make it easier. http://i.xomf.com/wjywq.png

What i want the code to do is as follows:-

Cell 1 to be checked against cell 2 to see if it matches, if it matches, i want cell 3 to be checked against cell 4 (almost like a Vlookup idea) and then if that matches also, i want cell 6 to be copied into the stock availability cell.

The actual spreadsheet itself has over 2000 rows, and multiple columns hence the offset values in my above code.

Hopefully this helps you.

Anther thing i would like it to do is, if it finds multiple unique numbers where this criteria is met, if it could list all these in the stock availability against said size and datasheet.

 

[mumps]

The link you posted takes me to a blank screen. Also, it looks like you are uploading a picture (png). It is difficult to work with a picture. Please upload a copy of the file. You may want to try www.box.com or www.dropbox.com

 

[lawsonj08]

Apologies, i've uploaded a mock book here:- http://www.filedropper.com/book1_7

I've used that site as it doesnt require me to register!

So what i want to do, is in Requirement Sheet C2, i want it to check B2 against Stock Sheet to see if finds a match. If it does, i want it to check A2 against the relevant cell to the right of its match and then return the value of unique number.

As i said in the original post, where this may become a bit more difficult is, the datasheet could contain multiple values so would have to be a wildcard search?

Hopefully this all makes sense! Thanks again

 

[mumps]

Copy/paste the formula below into C2 of the "Requirement" sheet. Change the 100 to the last row or greater of your data range. This is an array formula so after pasting, you have to hold down the CTRL and SHIFT keys and press the ENTER key. This will place curly brackets around the formula. Copy the formula down the column to the last row with data. This can also be done with a macro. If you want a macro, please let me know.

=INDEX(Stock!A2:A100, MATCH(B2&A2,Stock!B2:B100&Stock!C2:C100, 0))

 

[lawsonj08]

Perfect, thank you!

Yes please, if you could do a macro that would be appreciated.

Is it possible to make the match for the datasheet a wildcard search so that if a cell contains more than one value; it would show the result?

Also, if the unique number is referenced elsewhere on that sheet (i.e. used for another item) it would not be listed and it would take the next appropriate item (if available)

 

[lawsonj08]

I've managed to change the code to work as a wild card (i believe) -

=INDEX(Stock!$C$2:$C$6000, MATCH("*"&$H2&"*"&"*"&$AC2&"*",Stock!$J$2:$J$6000&Stock!$E$2:$E$6000, 0))

Would it be possible for you to convert this to VBA please and if possible, make it so it doesn't duplicate its answers?

 

[mumps]

Could you upload another sample file that has data which reflects the following:

Is it possible to make the match for the datasheet a wildcard search so that if a cell contains more than one value; it would show the result?
Also, if the unique number is referenced elsewhere on that sheet (i.e. used for another item) it would not be listed and it would take the next appropriate item (if available)

Use a few examples to explain what you want to do.

 

[lawsonj08]

So for example, if there are multiple results that have 2" (size) ABC-123 (datasheet), it pulls through a specific unique number assigned to each? Just now, as soon as it finds the first occurrence this is then used throughout the results.

So, if we have 5 occurrences and 5 stock items, it would display the following:-

2" - ABC-1235 - STOCK-REF-1
2" - ABC-1235 - STOCK-REF-2
2" - ABC-1235 - STOCK-REF-3
2" - ABC-1235 - STOCK-REF-4
2" - ABC-1235 - STOCK-REF-5

Does that make sense?