File Optimization with Macro

Hello guys,
I'm getting crazy with this file.
in the last sheet named ANALISI in the column E ... I have calculed the avarage using this formula =MEDIA(SE(A:A=A2;D:D)) after that I copied and pasted only the value because everything got slower.

As you can see in the file it has calculed the avarage also when it wasn't necessary... how can I solve it?
Later on , I have to create a new macro so I can also use VBA formulas...

I should have also to hide line with the text NON CONSIDERARE.

Hope someone can help me ..

Thanks in advance,
your help would be very appreciated.

I hope the formula is clear to you, but the reason it's slow is because you use A:A as range, this is over 1 million rows !!!
You should try $A$2:$A$1400

Thanks for the answer, I m going to try .. is it possible to change formula from $A$2:$A$1400 into $A$2:$A$(Last cell in the column) ?

Otherwise do you know another method smarter to do it? I try to explain what I need in that fields ... I have to check if in the column A there are two or more equal codes, get the price in column D and make the average of them.

further..do you have any suggestion about hiding line with the text NON CONSIDERARE?

I'm so sad

I'll see what I can do and let you know, you can always hide the columns you do not want to see

In English, the formula would be:

Formula:

Please Login or Register  to view this content.

My formula for column E

Please Login or Register  to view this content.

in Italian:

Please Login or Register  to view this content.

Thelast row part follows but this is a starter, TMS also works

Again, in English, all in one go:

Formula:

Please Login or Register  to view this content.

@Keebellah: why in Italian?

Updated file

Originally Posted by TMS

@Keebellah: why in Italian?

I guess the assumption from the file name is that the OP is an Italian, even though they appear to be in France and have a very 'French' user name.

"ESPANSIONE" is also an Italian word. Maybe their locale is also Italian?

https://support.microsoft.com/it-it/...2-ef9cc9ad4023

Veuillez changer la location dans votre profil: "Italy" suffira. Merci.

That looks like it will be a #SPILL error in English. Delete everything in the column before entering the formula.

However, if that is a Structured Table, it won't work.

Thanks for the rep.

Maybe ...

D2:

Formula:

Please Login or Register  to view this content.

copied down

E2 (only):

Formula:

Please Login or Register  to view this content.

Clear everything on the ANALISI worksheet apart from the headings. Then:

A2:

Formula:

Please Login or Register  to view this content.

B2:

Formula:

Please Login or Register  to view this content.

C2:

Formula:

Please Login or Register  to view this content.

D2:

Formula:

Please Login or Register  to view this content.

E2

Formula:

Please Login or Register  to view this content.

Updated sample file attached.

As far as I can see, there is only one of each code, hence the values are the same. However, the problem is that the INDEX/MATCH in column D will return the first matching value for any code. So, , if there ARE more than one entries for a code, it will return the first value found for them all.

Need to come back to this later as I'm going out for the rest of the day.

Ok, given that columns A:C are simply row by row concatenations, there's no point trying to use INDEX/MATCH to return column D ... it won't work.

Anyway, simple solution ... a "straight" link:

Formula:

Please Login or Register  to view this content.

See the updated workbook. Note that the code in the third row of RDO has been copied from the second row to demonstrate the formulae.

You probably need to translate the formula into Italian:

=LET(lr;CONTA.VALORI(RDO!A:A);PERRIGA(B2:INDICE(B:B;lr);LAMBDA(x;INDICE(RDO!Y:Y;RIF.RIGA(x)))))

I changed the code in english using " , " in place of " ; " ... now works in the macro but generate a mistake in excel sheet

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

The formulae in the latest screenshot are all in English. They need to be in Italian. It's not just a case of changing commas to semi-colons. You've gone the wrong way, anyway: change English into Italian.

PS The final formula is a mixture of Italian and English. You need to be consistent throughout.

This is the code in English

Please Login or Register  to view this content.

Note that it is Formula2 NOT Formula2R1C1

Try replacing just the formulae in the code with the Italian versions from the workbook that I uploaded.

This compares Median with Average in columns E and F. Which do you need?

Please Login or Register  to view this content.

Do you really need VBA? These formulae all auto-adjust. You only need to drop them in once.

Please don't upload pictures, upload the working workbook/code.

Yes, I know how to do it. You are asking for a formula that you haven't used before?

it doesn't work yet... I am getting sad I want to cry..
columns D and E are not working they don't take the correct value in column D and the E consequently doesn't make the avarage maybe bcs for calculate columns F,G,H,I,J I had change columns A,B,C copy and paste as value..?

please see the attached file, unfortunately I have to delete some double codes due to the max dimension for the attachment

I don't understand this:

columns D and E are not working they don't take the correct value in column D and the E consequently doesn't make the avarage maybe bcs for calculate columns F,G,H,I,J I had change columns A,B,C copy and paste as value..?

If it is not giving you the values you want, what should they be? As I explained before, you hav unique entries in RDO so you won't see any changes between column D and E. I would need to see more data where there are multiple entries for some codes.

As I explained before, if you DO have more than one entry for a specific code, then INDEX/MATCH will always return the matching value for the FIRST entry. Not a problem with the sample data because there is only one entry for each code.

Please check for ex rows 70-72 they are the same code (3B-017168) bought two times one from supplier FI00009079 and one from FI00003257 in the column D it takes the correct value 48 and 23 (price in RDO file colum Y) but in column E it doesn't do the average 35,5 ...
....furthermore I was now thinking that for calculating in the next formulas this average should appear just one time so difficult :/

70 3B-017168FI0000907944898
71 3B-017169FI0000907944898
72 3B-017168FI0000325744898


I think your tears may be my fault, so please forgive me. In your OP you quoted the formula =MEDIA(SE(A:A=A2;D:D))

I mistranslated the function MEDIA straight into English as MEDIAN. It should, I think, have been AVERAGE. I have mentioned the difference a couple of times because it didn't seem right to me that you would want/need the Median value, but stranger things have happened.

So, maybe this gives you what you want

Please Login or Register  to view this content.

Again, please not that INDEX/MATCH may not return the value that you expect.

In your original post, you used the formula: =MEDIA(SE(A:A=A2;D:D)) which I now know translates to =AVERAGE(IF(A:A=A2,D:D))

That is saying, average column D if column A equals A2. Copied down, that would become A3, A4, A5, etc.

However, you are using CONCAT to build a reference code in column A and they are all unique.

it doesn't work again..I try to be clearer

Sheet ANALISI - Column A for example:

70 3B-017168FI0000907944898

code: 3B-017168
Supplier: FI00009079
Date: 44898

72 3B-017168FI0000325744898

Code: 3B-017168
Supplier: FI00003257
Date: 44898


if I find the same code (in the column A) in the same date (last numbers in the each cells of column A) BUT from different suppliers (FI part) then I need the average of their price indicated in column D .


I believe you !! you the lonely one can solve this :D

I suspect that, if your desire and intent is to total and average by Codice Prodotto (Product Code), your analysis is not going to give you that.

Perhaps we are starting from the wrong place? As I've heard it said, "if I wanted to go there, I wouldn't start from here".

Similarly, If you want to total and average by Product and Date, your analysis doesn't do that.

On Analisi, column A is a concatenation of RDO columns A and F. On RDO, column A is a concatenation of columns L and B. Hence, as you know, this is a concatenation of columns L, B and F. You don't have a column that is just Product and Date (L and F) and that is what you need for your analysis.

I can do that but do you have a preference as to where it goes? And am I correct in my supposition that that is what you want?

I don't know... I just want to die :')

every code has been asked to more suppliers in the same date and any supplier offers a price (column Y RDO Sheet).. I need the average of these prices.
In this way calculating the total expense minus the average of the offers I can estimate a saving.
this what I need and only concatenating and matching them I thought to find these values

Leave it with me. I think I know what is needed now and I will try and produce something later.

yes ...I m so sorry to bother you
I m also bad in English and it's difficult to explain it.

Pretty sure your English is way better than my French (not great) or Italian (non existent) Worry not, I'm sure we'll get there

I can give you this, but it will need a little time to tidy it up and give you the formulae and code.

Let me know if this is what you want.

I think you are close to the solution.
first three lines seem to be ok, the sixth no.

pratically I receive a code , I have to ask the price to 3 different suppliers once received the quotations I need the average of the prices...
I need also the date because same code could be asked many times and when I do this report I need to be sure that everything refer at the same date.

I need also the concatenation of Code + supplier + date + price ordered (Sheet Ordinato) in this way subtracting price ordered - average of the quotations I can calculate the saving on that code


RDO Sheet is the total amount of requests of quotation in a defined period of time , ORDINATO sheet is the file of code ordered , sheet Analisi is the sheet for calculating the saving

I haven't forgotten you. I've just got other stuff to do. I need to try things out. I have resolved the average problem, I think. Well, I'm pretty sure. The example you quoted of rows 70 and 72 works correctly.

But I need to look at the other issues you mentioned.

My version of Excel (2021) does not support all the functions used

This code loads all the formulae for columns A:E and J.

Note that all the CONCAT(...) functions have been replaced by (TEXTJOIN("_", ...) functions. This allows me to separate out the different elements, for example, price, product code and vendor. I can then use product code and vendor to match entries in the RDO sheet.

Please Login or Register  to view this content.

The workbook is attached.

Very important: For this to work all the CONCAT(...) functions have been replaced by (TEXTJOIN("_", ...) functions in the RDO and ORDINATO sheets.

There is a problem with your INDEX/MATCH statements in column F and G. For example:

Formula:

Please Login or Register  to view this content.

Here you are trying to MATCH ANALISI!C2 with ORDINATO!B:B. The issue is that C2 has price/product code/vendor. Column B on ORDINATO also has those same elements but there are no matches. I suspect that could be that the price may be different. However, if I check just the product code and vendor there are still no matches. I cannot test whether or not these formulae work as they all return "NON CONSIDERARE".

I have created a new ANALISI sheet .... ANALISI2

The new sheet requires only two formulae and it does not matter how you set up your CONCAT on the RDO sheet.

This formula will Fetch all rows from RDO and select required columns:

Formula:

Please Login or Register  to view this content.

This formula will Calculate AVERAGE for ALL rows

Formula:

Please Login or Register  to view this content.

As you can see, they are relatively simple compared to the formulae required to extract the Supplier, Product, Date and Price in the original ANALISI worksheet.

In the sample file, there are other formulae which, hopefully, will demonstrate the validity of the calculations. You don't need them, but they might prove a useful cross reference should you have any doubts about the correctness of your calculations.

We do, however, still need to get to the bottom of the calculations relating to the ORDINATO sheet.

Further update. I have added a test record to the RDO and ORDINATO sheets. All the formulae appear to work as expected. I have included some test columns which you may wish to retain to cross check your assumptions.

The code will create the formulae but, as the formulae are self adjusting, you shouldn't really need to to run the code.

Please Login or Register  to view this content.

If you wanted to, you could convert all the formulae to values after they have been loaded.

Please Login or Register  to view this content.

Judging by the lack of input/response/feedback over the last five days, I assume you have lost interest or found a solution elsewhere.

If I hear nothing back today I will unsubscribe from this thread and leave it with you as it stands. I hope it does what you want.

Hello guys, I had my Pc broken...sorry
Now I can check your kind update !

Which of the two macros do I have to use? Sub sAddFormulae() or Sub sAddFormulae2() ? ...maybe both? I'm sorry but you are too expert for me, I don't have enough skills to understand it correctly

I tried however blue columns don't work ... everything appear with NON CONSIDERARE.

I don't know why.... I had to copy and paste your VBA code because once opened your file, my company data protection blocks your macro.
could be the problem?

I think that is a plausible reason.
You cannot just copy macros without I understanding what you’re doing

Provide a workbook for troubleshooting.

I have generated this vba code, however Blue columns of TMS's File don't work

Please Login or Register  to view this content.

I asked for a workbook with the changes you've made.

Not at all - just trying to get enough information to help you!

I have added the code to your latest sample file.

I have also added a test record at the bottom of both the RDO and the ORDINATO worksheets. This demonstrates that the formulae DO work. The issue is that you do not have any matching records. I can't say this a different way. You DO NOT have any matching records. Please look at the test records and the output record in the ANALISI worksheet to understand how the formula works and what it is matching.

Please Login or Register  to view this content.

If you are copying the code, please copy EVERYTHING in the mANALISI module.

The code will check if the ANALISI worksheet exists and, if it doesn't, it will create it. It then clears the entire worksheet. After that, it adds the headings (using formulae) and adds all the formulae for the analysis. Finally, it formats the columns ... headings (colour) and data (column width, number format, text wrap, etc).

You DO NOT NEED to try and record a macros. It is all done. Just copy the code from the sample file.

Is there any method to share with you the complete file? it's about 1.4 Mb and zipping it doesn't get 1Mb..

Blue cells got some issues...they don't extend values till the end (please check the pic).
other things I have to understand:

- column G: does it takes the ordered price from ORDINATO Sheet ?
- column H: does it takes the Q.ty from ORDINATO Sheet ?
- column I: does it shows column G * I ?
- column J: does it shows column F * H ?
- column K: does it shows column I - J ?

maybe the problem is that you get columns A-B-C-D-E from the RDO sheet but I need them from the ORDINATO because they are the item really ordered , basically from RDO I need only to get the average of same code from request for quotation in order to get the "total average"

Don't kill me please

Cut down the data in the file until you have a copy that is small enough to share and that shows the issues.

If you remove all the empty columns after the last filled column as well as the last rows below the last filled cells your file will be reduced too.
And if you save it as xlsb it's even smaller

maybe the problem is that you get columns A-B-C-D-E from the RDO sheet . . .

I get the data from the RDO sheet because, in your first example which only calculated the average, YOU only used the RDO sheet.

Losing the will here.

- column G: does it takes the ordered price from ORDINATO Sheet ? Yes, column Z
- column H: does it takes the Q.ty from ORDINATO Sheet ? Yes, column V
- column I: does it shows column G * I ? Yes
- column J: does it shows column F * H ? Yes
- column K: does it shows column I - J ? Yes

I will need to investigate why the formula is not spilling down. I may be some time.

OK, at the moment, I do not know why the formula is not spilling down. There ARE matching Supplier/Product Code entries in RDO and ORDINATO so a) it should spill down and b) it should get matching values. I suspect the problem may be because there are more than one matching entries.

Anyway, analysis so far:

In RDO, there are 1399 rows/records, of which 1308 are unique Supplier/Product combinations, of which, 330 entries match records in ORDINATO. Similarly, in ORDINATO, 4308 rows/records, of which 3374 are unique Supplier/Product combinations, of which, 330 entries match records in RDO (as expected). There are 47 uniique suppliers in each worksheet. If we remove the uniqueness from the equation, there are 379 matching entries.

So, the question now is, how do you want to analyse this data? Originally, it looked as though you had an entry in ANALISI for every record in RDO. Do you want to see an entry in ANALISI for every record in ORDINATO? Or, do you want to see entries for the records that match in RDO and ORDINATO. And, given there are "duplicate" entries on both sides, how do you want to cater for that?

So, for example, a couple of matches in ORDINATO

PHP Code: 

Conc. Code+fornitore ID    Fornitore ID    Codice Prodotto    Quantit  & agrave     Prezzo
3-8258/1_FI00000127    FI00000127    3-8258/1    48    7.8
3-8258/1_FI00000127    FI00000127    3-8258/1    32    7.8
3-8258/1_FI00000127    FI00000127    3-8258/1    80    7.8
3-13155_FI00000127    FI00000127    3-13155    36    6.9
3-13155_FI00000127    FI00000127    3-13155    44    6.9 


Note that there are "duplicate" Supplier/Product entries and the quantities are different but the price is the same.

Over to you to give it some thought and make some decisions.

Whatever you decide, I suspect it's back to the drawing board. Good job I'm not charging you for my time

Where is the workbook we need to be looking at?

Please take two or three specific examples of requests for prices in RDO and the matching entries in ORDINATO and manually show what you expect to see. I can't get my head round checking 4000+ ORDINATO records.

I am struggling to understand how you relate RDO requests for product/date to the entries in ORDINATO (which has lots of dates but not, as far as I can see, the date the quote was requested in RDO.

I can understand getting an average of product/date requests. What I don't understand is how to relate them to entries in ORDINATO.

I can easily produce entries in ANALISI for every record in ORDINATO, but is that realistic? If I have interpreted the data correctly, many of the records in ORDINATO do not have matching supplier/product entries in RDO. If that assumption is correct, you won't get an average price to use in your calculations.

@Veronique: just to reassure you that I am still looking at this, and getting close (I think).

I have a couple of issues to try and understand and resolve and I will let you have an update. I may need to come back and ask for clarification of a couple of scenarios.

@Veronique: I think this is working . . . but what do I know?

I was having some trouble with the dates converting erroneously. That is, things like 05/03/2023 changed to 03/05/2023 and, consequently, I was not able to match them. That seems to be fixed now, fingers crossed.

Please note that the macro converts the formulae to values except for the first row. That is intentional so that it is easier to be able to check what is going on.

There is a lot of diagnostic information on the right hand side which I think is still quite useful at this stage. It is my intention to make it possible to switch that on and off. However, I don't have time at the moment. Where data is available on the RDO sheet, the diagnostics will list the date(s) and value(s) for the products, as well as checking minimum, maximum, average and count of values.

Please note that some products have more than one date. It is not immediately obvious to me which I should pick so I have taken the first.

There are 436 records on the ANALISI sheet that have dates/values on the RDO sheet (out of 438).

Let me know if this is what you were expecting.

Dates have been a serious issue when working within Excel.
When using VBA you should always save the date as a number and not as date.
The cell formatting serves only to view the data in the cell but when calculatin dates as a number you will not get the date 'changing' like you mention
03/05/2023 (45049) or 05/03/2023 (44990)
Als when you sort the dates the sorting as a number will always be correct
When you include the time value : NOW() 10/09/2023 08:59:57 (45179.3748842593)
You play around with it and you will understand what is happening and will be able to solve it

@Keebellah: thanks for your comments. I know what was happening and why. And also the problems associated with VBA and dates. However, I was trying to keep them displayed as dates in the ANALISI worksheet especially where there was more than one date for a product.

Because there can be more than one date, I am collating them as text values. For performance reasons, I convert the formulae to values. But, if there is only one date, and the day is less than or equal to 12, Excel can and does mess it up.

I overcame the problem by dropping the formula in, then formatting the cells as text before converting to values. That seems to have worked.

If not, I will have to follow your advice. We'll see.

@Veronique: This, I hope, is the final version . . . at least for this stage of the development. There may be changes subject to your requirements.

The ANALISI sheet is purposely not present. The first thing you need to do is read through the Information sheet and then run the sAddFormulae macro. Diagnostic mode is switched off. If you want to try it, please follow the instructions in the Information sheet.

I'm going to be away for a few days so I won't be able to make any changes until I get back . . . no macro development on an iPad

Good luck.

@TMZ: you've outdone yourself, nice job

@Keebellah: thank you . And thank you for the +rep

@Veronique: enjoy. I hope it gives you the results you expect . . . or, at least, results you will understand.

Hi Veronique

How can I copy this Macro in my file?
I'd like to test it but I have problem... do I have to copy the code in my file?

Yes, you will need to copy the code to your file.
The easiest way, I think, is to open this test/demonstration workbook and your file. Then, in the Visual Basic Editor, drag all the modules across to your file. There is no code in the RDO or ORDINATO worksheets, or the Workbook module.

do I have to do any operations in sheet named RDO and ORDINATO?

No, not in the worksheets themselves.
But you will need to set up the Dynamic Named Ranges. I have created a macro, sAddNamedRanges, to do that.

Please Login or Register  to view this content.

And I have attached the latest updated workbook. Please read the Information sheet carefully. It should explain everything but, if not, let me know. I suggest you run the code on a copy of the live workbook. The code does not make any changes to the RDO or ORDINATO worksheets.

Did you try running the sAddFormulae macro in the sample workbook? If so, did the results look as you would expect them to?

@Veronique: hang fire. You can run the macros without copying them into the live file. I will send you a new file with just the macros and instructions on how to run them.

@Veronique

A quick follow up. You can run the macros without copying them to the live data file. I have just tested it, and it works.

I have attached a workbook that only has the code in it. It has no data, and it has no Named Ranges.

The Information sheet in this workbook explains how to run the macros.

In brief, open the macro file first. Then open the data file so that it becomes the Active Workbook. Then you can press Alt-F8 to see the macros in the Code workbook. Run sAddFormulae.

@Veronique: My guess is that it has something to do with our different Regional Settings, specifically for the date format.

I will have to experiment and see what I can do to overcome the issue.

You don't need to close the thread just yet. We have, I think, come a long way and it would be a shame not to see it to a conclusion.

In the meantime, here is a copy of the updated workbook ... the macro works for me hence the reason for thinking it has something to do with the dates.

Maybe you can check if the figures look as you expect them to?

Check your data set this column to TEXT to avoid the automatic interpretation en setting a value to a data

@Veronique: please try this new version.

I think the problem is most likely to do with different date formats. Originally, I was trying to make the dates in column C readable as dates, but I think that is the issue. I have changed the macro now so that the values in column C are just the numeric representation of the dates. For example, 4th October 2023 is 45203. On my system, with my settings, that would display as 04/10/2023 but I am guessing it might be different on your system. Hopefully, this will fix it.

I have, however, output the readable dates in column L. They are only there for convenience and not used elsewhere in any formulae.

You do not need to do anything to the RDO and ORDINATI worksheets provided the columns remain in the same order as they are now.

Please follow the instructions: open the macro file first but do not run any macros. Then open the data file so that it is the active workbook. Then run the macro.


Bon chance. Buona fortuna. Good luck.

Note you do not have to copy the macro to your file. In fact, if you do, it won't run.

When you down load the macro file, you will need to navigate to the file in your download folder, check the properties and unblock it. Otherwise, you can't enable the macros. Guessing you have done this before though ... but I thought I'd best double check.

As I said before, please just follow the instructions.

@Keebellah

Dates have been a serious issue when working within Excel.
When using VBA you should always save the date as a number and not as date.
The cell formatting serves only to view the data in the cell but when calculatin dates as a number you will not get the date 'changing' like you mention
03/05/2023 (45049) or 05/03/2023 (44990)
Als when you sort the dates the sorting as a number will always be correct
When you include the time value : NOW() 10/09/2023 08:59:57 (45179.3748842593)
You play around with it and you will understand what is happening and will be able to solve it

@Keebellah: thanks for your comments. I know what was happening and why. And also the problems associated with VBA and dates. However, I was trying to keep them displayed as dates in the ANALISI worksheet especially where there was more than one date for a product.

Because there can be more than one date, I am collating them as text values. For performance reasons, I convert the formulae to values. But, if there is only one date, and the day is less than or equal to 12, Excel can and does mess it up.

I overcame the problem by dropping the formula in, then formatting the cells as text before converting to values. That seems to have worked.

If not, I will have to follow your advice. We'll see.

Well, I finally had to give in and follow your advice . The macro still works on my side, so let's hope it works for Veronique.

If this file is so important you can ask the person responsible for your netwrok to mark the file as trusted.
It's not a question to copy and so but then make it useable.
You cannot compare a local system to a newtrok

@TMS; I've been struggling with dates in Excel for ages, when I think I've got it right a new issue pops up and messes it up, especially when it comes to importing or reading external data.
I always conver my dates to double values for date and time and it's getting bteer, but still after more than 35 years of coding ...
Now again Oktober 10 shows up sometimes as 04/10/2023 (dd mm yyyy) or 10/04/2023 (mm dd yyyy) so I decided to not use dates before the 13th of each month
Serioulsy date has to be read as a double adn then format as the user likes. I also use a Function to check the system settings

Now It s urgent 😔😔

Véronique - whilst I understand that you may be in a bit of a panic now, don't tell us that it is urgent, please. All your helpers here are volunteers giving freely of their own time: you haven't paid a penny for any assistance you get here, therefore expecting any form of speed response or preferential treatment, or seeming to, is inappropriate.

Thank you for your understanding. If you are pressed for time, consider finding a paid 24/7 agency who can do the job for you. This is not a service like that.

Moderator