How to speed up the calculation

Hello,

In my sample I use the following code, the code is working fine just my problem is that the calculation time is too long in the original file (more than 10 minutes), because the sheet BOM has more than 10.000 rows.

The target of the formula on sheet Evaluation, column Q: if the material (sheet Evaluation column D) has 1 "Active melange" then "Mono material" if it has 2 then "Bi material".

Could somebody provide me a faster/simplier solution?

Please Login or Register  to view this content.

The sample file has been added to this sheet.

Thanks in advance the reply!

Hello Leith Ross,

Thank you for your time! I checked your proposal but unfortunately not give me back the desired results. Please, could you see the attached file.

Hi Villalobos,
try it

Please Login or Register  to view this content.

Hi nilem,

Your solution is WORDCLASS for me, and working perfectly in my sample file.
But when I use in the original file (with the original data set) there are some problem, please could you have look this file (only 232 KB)?

Hi Villalobos,
it is very difficult to understand the logic of your issue.
Whence undertake values of the helper column 'S' on the sheet 'BOM'?

I try to summarize the concept:

On the sheet BOM there are the all active status bill of materials / material. The "Alternative BOM" column is contain the counters (<-- if one material has more than 1 unique counter it means that it has more than one active bill of material and so on). The column S is display that if the "Alternative BOM" has or hasn't got "Active melange". This value is delivered, there is a simple VLOOKUP formulae behind in the original file and just check that what is the status of the melange (the number of melange is not listed in the sample file) in the BOM. Do you need these values to the calculation?

On the Evaluation sheet, in column Q my target is that to know how many unique "Active melange" is/are in the unique "Alternative BOM"/material.
There are only 3 options, if the value is 0 <= Not assigned active melange to the BOM, 1 <= Mono material, only 1 unique active melange per alternative BOM or 2 <= BI material, there are 2 unique active melanges per alternative BOM.

I hope it is help for you, let me know if you need more.

The array formulae gave me the desired result but the calculation time is to long, 10 minutes.

Hi Villalobos,
Let's take a look at the material "8975144". Why it has the status of "Mono material" (as you said, there are 2 unique active melanges per alternative BOM #3)?

you catched me... my array formula is wrong...
What can we do?

please write manually the desired result for this example

Hello nilem,

I placed manually in column R (sheet Evaluation).

last question
8024170 - "Mono material"
It's not a mistake?

no, "Mono material" due to the higher "Alternative BOM" counter

Ok, try this
There is only one condition. The counter in the column "Alternative BOM" must be sorted in ascending order within a single group of materials "Material (Plant)" (as it have now on the sheet "BOM")

Please Login or Register  to view this content.

Спасибо большое!

На здоровье