# Posts by jarko28

• ## \$40 USD - Determine all possible combinations of differences between 2 items

Re: \$40 USD - Determine all possible combinations of differences between 2 items

SMC, I have done a manual excercise of how this needs to be optimized and the logic between changes:

Plate and colour changes are NEVER applied to the first line. Every other line after that gets a plate and colour change charge.

Common plates and colours remain in the press if the next item needs them to be printed. # of changes is determined by the next line you are looking at - what needs to change to run the next item based on what is on press now.

I am hoping the attachment makes it clear as to the logic. Let me know if this needs to be clarified further.

thanks.

• ## \$40 USD - Determine all possible combinations of differences between 2 items

Re: \$40 USD - Determine all possible combinations of differences between 2 items

I have a couple of solver sheets which solve very similar sequence issues, I have attached them. The issue is that I cannot adapt them to my situation easily

• ## \$40 USD - Determine all possible combinations of differences between 2 items

Re: \$40 USD - Determine all possible combinations of differences between 2 items

Hi,

the order in which the items appear in on each row does not matter. the only importance is that they share common plates and colours.

The problem trying to be solved is this:

There is a printing press which has several round cylinders. On each cylinder a rubber plate is mounted. The rubber plate is like a stamp with raised engravings which picks up ink (colour) as it spins, and transfers that ink onto foil. Each plate does this and by the end of the complete turn of all cyinders, you end up with a full image on the foil.

In example for printing item 1, say you are trying to print an image of a basket of fruit. Plate a1 picks up colour blue and prints a blueberry. plate a2 picks up yellow and prints and banana, plate a3 prints an orange background first before the other plates print the fruit. the press spins millions of times to create a roll of foil with hundreds of millions of impressions of that image at a certain size.

After the job is done, the press still has the cylinders mounted for item one, and buckets of colour are hooked up to the machine for item one as well. The question is, which item should be printed next to minimize the amount of physical changes a worker has to do to print all the items in on his schedule.

If he prints item 2, he must unmount plates a3-a10 because the next item does not use them. But he would leave item a1-a2 mounted in the machine because they will be used. Same with the common colours, he would only change colours not used.

if for example, item 20 on the list had all common colours and just one different plate, it would make sense to print it immidiately after item one, because only 1 change would need to be done physically by an operator. The complete sequence for the entire schedule that would minimize changes is the goal.

Please let me know if further clarification is required.

• ## \$40 USD - Determine all possible combinations of differences between 2 items

Re: \$40 USD - Determine all possible combinations of differences between 2 items

SMC,

when comparing item 1 to item 2, there would be 12 changes, i have attached the file and highlighted.

now i need excel solver to compare all possible items against themselves to see how many changes would be needed between every possible combination, and determine the optimal sequence to run to job to minimize changeovers.

• ## \$40 USD - Determine all possible combinations of differences between 2 items

Re: \$40 USD - Determine all possible combinations of differences between 2 items

I am thinking it may be easier than the original post. If we look at Sheet 1, is it possible for Excel Solver to determine the sequence to run this in without doing the interim step of determining the number of changes by compiling the table in sheet 2? the goal is to run in a way that minimizes plate and color changes.

• ## \$40 USD - Determine all possible combinations of differences between 2 items

Re: \$40 USD - Determine all possible combinations of differences between 2 items

thanks!

• ## \$40 USD - Determine all possible combinations of differences between 2 items

*10% sent to Ozgrid*

Hi,

I have a table of items. in column A is the item #, consequent columns list the attributes of each item in separate columns. My goal is to compare all items in the list against each other, determine the # of DIFFERENCES between any 2 items, and output a table that shows how many colors and plates I would need to switch to print one item after the other.

For example, if you have common 2 common colors and one common plate, you would not need to switch them during production. But the uncommon ones you would…so how many uncommon ones would there be, and once this is determined, you put it in the grid. Of course, on the grid when items are compared to themselves, the answer is 0.

Two things to note: 1. The number of items varies each day so the ouput chart would vary in size, and 2. # of colors and plates is unique to each item but some have lots of colors and plates, while others have very little.

The solution must be able to accommodate a large number of skus. there are thousands, but they will not all be run at once. If I could have the code cover 50 items, this should be enough.

I have included the file. Sheet 2 has the format I need the output in once it has been calculated.

example:

item 1 has 4 plates (a1, a2, a3, a4) and colors (c1, c2, c3, c4)
item 2 has 3 plates (a1, a2, a3, a5) and colors (c1, c2, c209, c3000)

in this case, in order to switch from item 1 to 2, I would need to change plates a4 to a5 and colors c 3 to c209 and colors c4 to c3000. this is a total of 3 changes. The output in the comparison table between item 1 and 2 would be "3", as there are 3 differences.

Also, when item 3 has 1 color and 1 plate and item 4 has 5 colors and 3 plates, none of which are common, the output table would say 8 as everything would need to change, and the total # of changes is 8 because 8 things need to be switched out.

Please let me know if you are interested. This aim of this table is to be used with a Solver equation that will determine sequence of production in order to minimize the total # of change overs.

Also note that sheet 2 output is to show layout needed only, the # of differences is not correct.

Thanks.

• ## \$30 USD - sum up values in column 3 based on unique values in column 1&2

Re: \$30 USD - sum up values in column 3 based on unique values in column 1&amp;2

Hi All.

Thanks for all the help! Kris' formula has solved the issue for me. Thank you all for the very quick replies!

• ## \$30 USD - sum up values in column 3 based on unique values in column 1&2

Re: \$30 USD - sum up values in column 3 based on unique values in column 1&amp;2

you guys weren't kidding when you said it will be slow LOL, like 30 mins slow for real....crazy haha

• ## \$30 USD - sum up values in column 3 based on unique values in column 1&2

Re: \$30 USD - sum up values in column 3 based on unique values in column 1&amp;2

q

• ## \$30 USD - sum up values in column 3 based on unique values in column 1&2

Re: \$30 USD - sum up values in column 3 based on unique values in column 1&amp;2

Smuzeon,

I think I will have to pay Kris given that I just tried it and it seems to work.

Kris,

What do I do to transfer the 90% to you? Also, would you be able to do me a favor and edit the formula is I sent you an excel file that had the proper columns in the proper order? I want to make sure I don't screw this up.\

thanks to both of you for the quick response.

p.s Smuz, I'll contact you if VBA is needed - I'll be glad to pay another 30 if this is too slow, but this will solve my issue for now.

• ## \$30 USD - sum up values in column 3 based on unique values in column 1&2

Re: \$30 USD - sum up values in column 3 based on unique values in column 1&amp;2

I'm concerned with VBA because new columns will be added to the extract, so I want to make sure that I can edit the code with this solution in the event that columns change or are added, which I don't know how to do. with a formula, I can just edit the fields. But I don't know how flexible the code can be...

• ## \$30 USD - sum up values in column 3 based on unique values in column 1&2

Re: \$30 USD - sum up values in column 3 based on unique values in column 1&amp;2

Krishnakumar,

formula is absolutely preferred as I would like to copy it into the cells i need and edit the formula as required. The only thing is that there are always a varying amount of rows from the extract and my pivot table is always 10,000 rows to account for the all the data.

If you're in, please let me know if you need anything else to proceed.

thanks.

• ## \$30 USD - sum up values in column 3 based on unique values in column 1&2

Re: \$30 USD - sum up values in column 3 based on unique values in column 1&amp;2

Hi, My problem is that the file is get is an extract from an inventory system. If i can make sure that the Macro inserts another column in an appropriate column within my extract and each time, this is fine, but I want to make sure I can leave the rest of the columns undisturbed as I will be using the entire file, along with the newly added column in a pivot table.

Let me know as I would most likely need to send the file in the format it is always in, correct? meaning the column names etc positions would need to be known in order for the macro to work without me having to edit the vba code?

• ## \$30 USD - sum up values in column 3 based on unique values in column 1&2

I have paid the 10% paid to Ozgrid

I have 3 columns of data: Item number, purchase order number, and quantity. Item numbers and purchase order numbers repeat sometimes, so what I need to get is really only the first instance of the Item # and PO#, then add it to each unique instance of the same Item # but different PO# once:

Item PO Qty Total Qty on all Purchase Orders for item number listed in Row 1
1 101 100 300
1 101 100 300
1 102 100 300
1 103 100 300
2 104 100 200
2 105 100 200
3 106 100 100
4 107 100 100
5 108 100 200
5 108 100 200
6 109 100 100
7 110 100 200
7 110 100 200

Basically, for each item number, count each PO line only once and sum it with other instances of that item on different PO's so you see how much in total was ordered for that item, but i can't remove the duplicates from the sheet, so i need some sort of formula that does that for me.

If anyone knows how to solve this, that would be very much appreciated.

Thanks.

Jerry

• ## sum values in column 3 based on unique values in column 1 and 2

Hi!

I am stuck big time trying to resolve my issue with a table of data that is extracted and I cannot change:

I get in column 1 Item #, in Column 2 the Purchase Order #, and in column 3 the total on that Purchase Order.

The problem is that the extract has rows where the same purchase order and item number repeat. When I go to sum up the values in column 4 for all of a certain item number across all Purchase Orders, I get values which duplicate and it is not the true qty.

I have attached the test file - tab two is the required result.

I need to consolidate all qty by item number from all PO's and put that full qty in column 4. The fact that the number in column 4 will repeat is fine, i just need to total qty per item number from all PO's by excluding the duplicate entires. Removing duplicates from the sheet is not an option, I assume you have to use some sort of an array formula...

• ## autofit size of comment that is generated based on txt from another wrkbook using vba

Re: autofit size of comment that is generated based on txt from another wrkbook using

I just figured it out, it's not autosize but it makes it legible enough so you don't have to resize manually:

Code
``Target.Comment.Shape.Width  = 430``

Thanks again omp65! Very much appreciated.

• ## autofit size of comment that is generated based on txt from another wrkbook using vba

Re: autofit size of comment that is generated based on txt from another wrkbook using

Thanks omp65 for the prompt response! that seems to work, the only issue now is that the box is so long it runs off the page, is there any way to include word wrap so that the size of the box is not as a long one line but multiple lines just a shorter box?

• ## autofit size of comment that is generated based on txt from another wrkbook using vba

Hi!

First post ever, I am a complete newbie and this is just way above my head, but I have a feeling my issue is a matter of a couple of lines of code that someone here would be able to help me out with

I have code that generates comments in certain cells based on cell contents in another workbook.

I would like to edit the code so that the comment box size is either made larger compared to the default size that is generated, or if possible, autofit the size of the comment box to fit the contents of text from the source cell in the second workbook (i.e if the cell has 200 characters, the comment box generated in the other workbook fits all 200, if it is 10, it is big enough to fit 10).

The other change to the code I would like to make is that I would like to action of generating the comment box to occur when I hover over the cell - right now I have to click the cell, I want to be able to hover over it and make the comment auto generate. This second part is not as critical - it's the text box size that is causing the whole procedure to be very inefficient as I have to manually increase each comment size if I want to see the text, and also, as soon as I click the cell again, the code runs again and reset the comment box size back to default and too small...very very frustrating

If anyone knows how to achieve this, I would be very grateful. Thanks!!!

This is the code: