Match codes from file

Hello everyone,

I have a file, that we run weekly, to show codes, that when they are running together, we can possibly save time, when certain codes can run with each other, what we call 'Sweeps'.

We create a new list of codes, each week that we produce against and what I would like to see, is to match the new codes, to see if any of those, can be ran together when checked against the Criteria list.

Is it possible to view the attached and let me know, what is possible to create.

Many thanks

Pugg

Hi,

Many thanks the reply.

The attached file shows Sweeps and the other sheets, is the codes, we upload every week.

The Weekly codes, need to somehow, lookup the Sweeps sheet and any code that is present to the weekly codes, is to check if they can run alongside each other.

As an example:
534199 16SPM from Row 2, can run with 537503 SPM from Row 20, as the Sweep data sheet shows this.

i hope that makes sense.

Many thanks

Pugg

Sorry for the delay.

Here is the file, with the Example sheet attached.

What the Example sheet shows, is when viewing the Sweeps Master data sheet, it will tell you what codes can be ran with each other, to create savings (ie Sweeps).

I hope that makes sense

Oh no Sorry for the misunderstanding.

The Sweeps sheet, is what we have checked and verified over the last 66 Weeks (So this will be our check list), checked and found, certain codes can run with other codes, and be able to apply Sweeps.

The Weekly sheet, when we create the codes, needs to check against the Master Sweep sheet, to see if any of those can run with each other and place Sweeps, to save us time.

If, when we create the New week, I would like a program or something that with a click, would check the weekly codes against the Sweeps Master sheet and highlight, those that can run with each other, so all we need to do is to add the Sweeps, instead of digging through alot of data, to see which can match.

Hope that makes sense

Many thanks

Pugg

Hi,

im running Excel 365 for MSO (16.01.13801.20240) 32 Bit.

Ok, when we do it manually, we take all the New Weekly created codes (which are Materials) and we check their colour, smell etc, to see if they can run in parallel with other codes and to create Sweeps.
Once the checks have been validated with the Teams, we then manually add Sweeps to the codes (I have added one of our Weeks, that show The Weekly codes and which ones, can run with other codes etc).
To do it manually, takes Hours, so I have over the last 66 Weeks, compiled data from what we have already verified, which codes can run with each other to save time, hence we call Sweeps.
All I am asking, if there is a way, for when i create the New Week codes, there is a simple way for the weekly codes to sense check the Sweep List codes, to see if any of the weekly codes match.
Then we will update the Week sheet.

Im sorry, if I don't come across with the information you require, I don't know, which way to explain but I do value you support.

Many thanks

Pugg

Ok, let me start from scratch, hoping that will help.

The Week 14 list that shows all the codes we will be running for the week, some of those codes may be able to run with other codes.

The Mast Code List attached, shows codes, when running with other codes, we can save and create Sweeps.

Week 14, I need to lookup the Master Code List (or whichever way is practical) and each codes in Week 14, checks if they can run with other codes.
So if I had from week 14 (534199 16SPM), that code would check the Master code List and see if 534199 16SPM can Sweep into other codes.
If the Weekly sheet codes not find any Matches, we will place WASH, meaning we cannot Sweep that code into other codes.

Many thanks

Pugg

Thank you for the support and work carried out.

i have attached both, your file that you created through PQ and my file that shows the Master and a Test sheet (that has a list of codes).
What I need to know now, is how do I run the Test sheet, to search the Master sheet, to see if any of the codes (from the Test sheet), can be ran with Sweeps.

Your PQ file does not show, which codes run with Sweeps to the next code that will be a WASH.

Please let me know, if there is any other way, I can explain, how it needs to be solved.

Pugg

Oh I'm so sorry, if what I have tried to ask, isnt coming across that well.

I have added the attached Master Code list, with Wk11 & Wk12, that will show you:

1) Wk11 & Wk12 before the Sweep saving check
2) Wk11 & Wk12 after the Sweep check.

The before Sweep saving check, we have to manually test each code to see if, when running with the next code, we can save by adding a Sweep (if not, then it will be added as a WASH).
The after Sweep saving check, once agreed, certain codes can be produced and when the next code is to be be made, we can add a sweep (instead of a WASH).

We have been testing each week manually and for the last 66 weeks, I have compiled data, to show which codes, when being produced before other codes, can be added with a sweep.
That is what the Master Code list shows.

All i need now, is the find a way, when creating a Week of codes, then to run a program or a click of a Button, to show what codes when being produced, will be able to have Sweeps added, for the next code.
if there isn't any codes that can be ran and have Sweep savings, then that or all the codes, will be added with a WASH.

I hope yourself or any other person on the Forum, can help me with the conundrum.

Many thanks

Pugg

Thank you very much and I do really appreciate all the help and support you have given.

Pugg

All I needed, was to create a Weekly plan with codes, then each code in the Weekly plan, looks up the Master code list, to determine, if that code when ran before another code, can be added with a sweep.
If that code from the Weekly plan cannot find any other codes (from the Master code list) that they can Sweep into, then a WASH will have to be added.

i know it is simple for me to know, what is required and I am trying as hard as possible to get this across.
i wish there was another way of showing you what is needed, to possibly understand.

Many thanks for your support

Many thanks and Happy Easter

Good evening everyone

I was wondering if anyone else out there, was able to support me, in my quest, to have a solution to help me.

many thanks for the feedback.

How do you think, I can make it simple for all the read and understand, as you said before, I know what is needed, but probably, it is not generating the correct way

Ok thx,
Lucky I didnt add Sutty & Sue to the sections

Here is what we have:
Hope this helps a bit

There will be 2 tabs called “Master code list” tab and “New week code list” tab.

“New week code list” tab will have 2 buttons called “Retrieve & arrange SWEEP items from master code list” and “Save new sweep items to master code list” which will run possibly be creating a macro etc.

“Master code list” tab includes product codes from the previous weeks that are marked as SWEEP and WASH.

Sweep is a detailed name, we use for when we can run one product into another, without adding any additional WASH (so the Teams will use a Brush to SWEEP the Tanks out, saving time)
A WASH means any products running together, will have to be WASH with solutions the plant have for those products and cannot have a SWEEP.

When a list of products for a new week is pasted in “New week code list” tab and “Retrieve & arrange SWEEP items from master code list” button is pressed macro will go through all the entries in “New week code list” tab. Whenever macro comes across the code (primary code) in “Master code list” tab, it will check if the code in the row below (secondary code) exists in “New week code list” tab. If secondary code is also in “New week code list” tab, primary code will be marked as SWEEP in “New week code list” tab and the secondary code will be moved below the primary code in “New week code list” tab. In this case both primary code and secondary code will be highlighted to indicate that they were processed.

Otherwise, the primary code will stay as WASH and won’t be highlighted.

User can change the remaining codes in “New week code list” tab as SWEEP and move the other items up or down whenever needed after the macro finishes running. Only the items that are not highlighted should be moved around and edited because the highlighted ones are already processed.

Once user is happy with all the entries in “New week code list” tab and presses “Save new sweep items to master code list” button, macro will go and add the new items that has SWEEP next to it and the item in the row below to the end of “Master code list” tab so that they can be used in the coming weeks.

Aww sorry, didn't realize it needed to be in a different forum.

Definition of 'Running one product into another'

This means, when we produce one product and then make another product, can the check be placed to see if the product to product can either have a SWEEP, or if not then a WASH has to be added.

Greg hello,

I believe this is the right track, you have created.

The Master code List sheet, that shows multiple instances of the same product.
This reason is, I created 66 Weeks worth of Data, that shown, individual products, that can Sweep into other products, if this is applied.
Some products that show Sweep, may show different products, it can Sweep into.

I do really appreciate the help and if there is anyway I can show you more, on how this should work, please let me know

Many thanks

Pugg

Greg hi,

I have attached your sheet and added one of my weeks to the file (called Wk16).

You can see, this is what we check weekly, when the products are created, to see if any of them, can Sweep into the next product.
This (Wk16), we assess, then assign Sweep, if possible to the next or one that it can fit to.
if once the updated Week, with the Sweep added, I add to the Master Code List file, so it tells you, the products each one can Sweep into.

hope that helps too

Greg hello,

I have attached the updated file.

If you view Test Sheet, you will see cells I have highlighted Yellow.
According to the data in Col G, it shows it can SWEEP but
When you view the Master Code List, 522287 SPM doesn't show up to SWEEP into 554201 CB
Or 554274 4SPM doesn't show up as a SWEEP into 554201 CB.
But Col G shows it can SWEEP.

The cells I have highlighted Green, is correct.
This shows it has checked col B against the Master Code List file and shows it can SWEEP into the products highlighted in Col C, so Yes, it should show SWEEP

If any products from Col B (which I will use as the check list (When I paste from the Week we are to use), as this is the list from the Wk16 Update tab.
Col E should show what is possible Col B to SWEEP into (when it checks the Master Code List), then adds to Col E
Col G will then check if a SWEEP is possible, based on the findings from the Master Code List.
If the products in Col B,(when it checks the Master Code List against all the products from Col B) cannot find a SWEEP Match, then it should show WASH

Many thanks

Pugg

Hi again Pugg,

Many thanks for your latest posts.

I'll certainly take a look at them, but things are a bit hectic at this end right now, so it may be a few days before I can post anything here.

Sorry about the delay.

Regards,

Greg M

Greg hi,

Thanks for your support.

Pugg

Hi again Pugg,

I'm still somewhat confused, but maybe a little less so than before

Columns B:G on the "Test Sheet" worksheet in the attached workbook seem to show the Sweep/Wash requirements correctly.

However, the product shown on row 28 (534199 SPM) is listed on thirteen separate rows of the "Master Code List" worksheet. In columns J:P of the "Test Sheet" worksheet I have tried to use the same approach to deal with these multiple listings - have I understood the requirements correctly? If I have, we can consider what needs to be done regarding updates to the "Master Code List" worksheet, but it's quite possible that I still haven't understood your exact requirements.

Regards,

Greg M

Greg hi,

wow, I can see that is is nearly there, just a few tweaks I think needed.

I have attached an update sheet, that shows Wk17 as we are currently running this week.
This is the actual run that we have created and checked in the Lab and then placed the codes to which we deem can have Sweep.
I have now added those Sweep date from Wk17 to the Master Code List.
I then added the Codes from Wk17 to the Test sheet., to see how it matches.

See if they match to what you have created, as I think we are nearly there, but maybe a few more tweaks, to fine tune it, then Im sure, its a Hit.

I do seriously appreciate all the support and help that you have given

Many thanks

Pugg

Greg hi,

Hope you had a chilled weekend.

Do you know if you had a view of the update, that I sent you.

Many thanks

Pugg

Hi Pugg,

I might have something for you later this afternoon/evening.

Regards,

Greg M

Hi Pugg,

Take a look at the "Test Worksheet" sheet in the attached workbook and see if it correctly identifies all of the "SWEEP" situations for Week 17.

If it does, we can then look at what's involved in copying those data to the "Master Code List" worksheet.

Use the "Check Products" button to perform the checks. When the "Product by Product" checkbox is ticked the routines perform the checks for a single product and then pause so that the results of those specific checks can be examined.

Hope we're making progress.

Regards,

Greg M

Greg hello,

I can see now the improvements made and alot does match to what has been asked.

I have attached, the copy and highlighted in RED on the Test Sheet, the Product No's that do not match.
The ones in Black, are exactly as I want them to be.

For some reason (not sure how), Col I, that should be the Product being able to Sweep into col J from those in RED, do not match and are not showing on the Master Code List, as being able to Sweep into each other.

it shows from Col I:
589536 CB should be able to SWEEP into 554601 2CBPF
It does show though, the reverse (554601 2CBPF can SWEEP into 589536 CB)
So 554601 2CBPF should be in Col I and 589536 CB should be in Col J

The work carried out, is amazing and I think a few more tweaks, will get the result asked for.

I do appreciate, the work carried out.

Many thanks

Pugg

Hi again Pugg,

From your Post #31:

When a list of products for a new week is pasted in "New week code list" tab and "Retrieve & arrange SWEEP items from master code list" button is pressed macro will go through all the entries in "New week code list" tab. Whenever macro comes across the code (primary code) in "Master code list" tab, it will check if the code in the row below (secondary code) exists in "New week code list" tab. If secondary code is also in "New week code list" tab, primary code will be marked as SWEEP in "New week code list" tab . . .

Based on the results you've corrected, it would appear that the SWEEP/WASH value is taken from the appropriate row of Column B on the "Master Code List". Is this correct?

The attached version of the workbook shows results based on this.

Regards,

Greg M

Greg hi,

I think you have got it, as checked and all shows correct.

I added the following future weeks products, to check if all were showing and amazingly, they all shown up and even gave a greater variation, of what we can SWEEP into.

Amazing work than you.

Is there any updates, that you need to work on, now it looks the correct way.

Pugg

Hi Pugg,

Glad to hear that it's starting to look the way it should.

In order to automate the application any further I need you to take the output from the checks performed, and show me precisely how those values (and any others?) should be added to the Master Code List.

On a single worksheet, show the values from the "Sweep Summary" table, and show also a mini-table of the additions that should be made to the "Master Code List" worksheet.

I don't guarantee that I'll do it, but I'll certainly take a look.

Regards,

Greg M

Greg hi,

I think as it shows in previous comments, I created the SWEEP from the files, we already agreed we can SWEEP into other products.
This data spanned from beginning of 2020 to date (Actually Wk16).
So the Master Code List shows all the permutations that we can SWEEP into, so far.

The "Sweep Summary" table, every time I add a different Week of products to Col B and the file runs to show what products can SWEEP into each other, I will update that information to the Product Plan.
Any products that the Master Code List doesn't show as a SWEEP, will be verified by the Quality Team to assess, if products (that do not show up), can create a SWEEP.
If any products are found, able to SWEEP, that the Master Code List, doesn't have, I will add the data to the list, already available.
This will then, shows when the "Sweep Summary", is ran, it should pick up the SWEEP availability, from the products that I add to Col B, weekly.
I hope that all makes sense.

not sure how you want me to shows data.

Many thanks

Pugg

Hi Pugg,

not sure how you want me to shows data

See the attached workbook for what I had in mind.

Regards,

Greg M

Greg hi,

Thanks for the workbook sent through.

If I see and reading this right:

The additions to the Master Code List, will only be SWEEP, if allowed to SWEEP into the next product.
if the next product cannot SWEEP into anything, then WASH needs to be entered:
Example:
511616 SPM will SWEEP into 585270 10SPM
But if 585270 10SPM cannot SWEEP into anything else, then WASH has to be submitted.

Hope that makes sense, If I get it right.

Pugg

Greg hi,

I used the updated file against this weeks products, to see if the SWEEP's shown, Quality will validate.
it worked exactly as you have updated and is great.

Is there anything that needs to be updated, from the previous message, I sent you.

Many thanks

Pugg

Greg hi,

Do you know, if there is any further work to be added to this project, or does it now need closing off

Many thanks

Pugg

Greg hi,

I came across an error, when running a future week.

This is what came up:

.Offset(iNoFound, 0).Value = sProduct_Primary

I think I fixed it by pasting Cells I & J further down, as the Blue cells stopped and the error popped up.

Is that true, ot would you say it is something else.

Here is a copy of the sheet.

Hi again Pugg,

The error occurred because the number of Products being checked exceeded the number of rows (30) in a Defined Name Range. See the attached workbook for how to avoid this.

Regards,

Greg M

Greg hi,

Thanks for the update.

I have added to my file.

Is there anything else that is required, for this to be complete?

Many thanks

Pugg

Hi Pugg,

I still cannot understand how you decide what to add to the Master list after you have performed the checks.

Can you please post a workbook which highlights the block of text which you have added to the Master list after performing the checks for a specific week.

Regards,

Greg M

Greg hi,

We will be checking next weeks products tomorrow, so once updated, I will send the file across, with info, hopefully you will understand, that shows SWEEP etc.

Many thanks

Pugg

I was randomly browsing, and I saw AliGW's comment about there being a large number of views of this thread, but no one helping. I think because there is so much back and forth, and the thread is so long, that it seems like this must be well-in-hand, so no need to dive into it. Frankly, there is a lot of text to digest here. I started to read it - got as far as the word "SWEEP", and got thoroughly lost. I would love to help if I can, but I have no idea what 'sweep' refers to - perhaps it's a cultural or industry term. My focus is VBA, so happy to help if needed - I just don't know if I'm needed...?

Yes, I got there eventually. Thank you for following up.

Hi Pugg,

We will be checking next weeks products tomorrow, so once updated, I will send the file across, with info, hopefully you will understand, that shows SWEEP etc.

Still waiting!

Regards,

Greg M