Sorting sections within large table

[Javid]

Hi dear Lucas
I'll be appreciate if you help me too with my problem almost the same.
I have a large table with more than 5000 rows with more than 700 section, each section have their own data in about 7 to 11 rows and what i need is to have a code to sort each section with my selection rows and sorting them by column Q. this column is permanently fixed for my sorting.
i mean there is no need to ask user which column i need to sort by.
i will be thankful if you would help me for my challenge. : pray2:
here is a part of my table:
29757
For example i want to select rows in each part of my table hat has No.1 of 2 or... in column A but from G column to Ah and then sort them based on column Q with minimum number on top.

[SamT]

WOW! Lookee all those Merged Cells; and Images, Hidden Columns, Colored Rows, Highlighted Cells, Borders and Conditional Formats.

I pass. Good luck.

The only Formatting there should be is: The Entire Header Row Height Resized, Bold, Font Size +1, Wrap Text, and Bottom Border. Maybe a Border above each section. A Header that covers many Columns (V:W; Y:Z) can be Formatted "Center Across Selection". Vertical Center is OK (A,B,C; V:W)

What you have is a single Database Table with 700 Sections, each of which must be found and programmatically described. Very complex operation with Merged Cells. Looks something like

Set FirstCell = TitleCell.MergesArea.Cells(1)
LastRow = TitleCell.MergedArea.Cells(TitleCell.MergedArea.Cells.Count).Row
LastColumn = Cells(Columns.Count, "A").End(xlToRight).Column
Set LastCell = Range(LastRow, LastColumn)
Set Section = Range(FirstCell, LastCell)

If you follow my Formatting guidelines above, AND Insert an Empty Row above each Section's Top Border, AND add a vertical Border on the Right that covers only that Table's Rows, you will have 700 DB Tables, any one of which can be described by
Table = TitleCell.CurrentRegion

[p45cal]

try running this which acts on the active sheet:
Sub blah()
Set mydict = CreateObject("Scripting.Dictionary")
lr = Cells(Rows.Count, "B").End(xlUp).Row
For Each cll In Range("B12:B" & lr).Cells
If Not mydict.exists(cll.MergeArea.Address) Then mydict.Add cll.MergeArea.Address, cll.MergeArea
Next cll
For Each itm In mydict.items
itm.EntireRow.Columns("G:AH").Sort key1:=Range("Q1"), order1:=1
Next itm
End Sub

[Javid]

Hi dear friend,
So many thanks for your answer and code.
I tried to test it but it gives me no act on my data file:dunno
Thanks again and good luck.

[Javid]

Hi dear SamT,
I appreciate of your attention and response.
And also many thanks for your helpful tips, I'll try to edit and change my table and then will test your code on it.
Hope it solves my problem.
Let me say I learned great lessons from your tips.
Thanks again and Good luck:friends:

[Aussiebear]

Javid, are you able to upload a sample file so we can test our suggestions?

[Javid]

Dear Aussiebear I did upload an image sample on my request, do you mean a sample of excel file you need?
Otherwise Would you please tell me how can i upload my Excel sample file?
I can't see any tools in toolbar for attaching excel file!
29758
Hope i did it right:)

[Aussiebear]

"Images", are for polar bears in space suits. Just a dummy file with some sample data (must be similar) will be fine.

[Javid]

Thanks and let me know that can you see my attachment now?

[Aussiebear]

Yes I can. Just for future reference you can attach files by clicking on Go Advanced/ Manage Attachments/ Choose File/ Upload

[Aussiebear]

First issue I can see is apart from the merged cells is that if we use "Column Q to sort Minimum to Maximum" there are blank cells. What happens to that data?

[Paul_Hossler]

1. How do you want Col Q sorted?

For example, section 1 has 1 entry filled outy of 7 rows, and section 2 has 2 cells filled in out of 7 rows


2. Sort each section keeping the rows together? Which entry do I sort by (e.g. section 2 has 2)?

3. Delete rows with no entry in column Q, or just sort them to the bottom?

29759

[georgiboy]

If you amend your formula in column Q From:

=IF(OR(P12=0,M12=0),"",P12/M12)
To:

=IF(OR(P12=0,M12=0),0,P12/M12)

And are hell bent on keeping the merged cells then:

Sub test()
Dim wsCP As Worksheet, rng As Range, rCell As Range, tmpRng As Range

Set wsCP = Sheets("Comparateur prix")
Set rng = wsCP.Range("A12:A" & wsCP.Range("A" & Rows.Count).End(xlUp).Row)


Set tmpRng = rng(1, 1)
For Each rCell In rng.Cells
With rCell
If .MergeCells Then
If Intersect(rCell, tmpRng) Is Nothing Then
.MergeArea.Offset(, 6).Resize(.MergeArea.Rows.Count, 27).Sort wsCP.Range("Q9"), xlDescending
Set tmpRng = .MergeArea
End If
End If
End With
Next
End Sub

Hope this helps

[Javid]

Thanks my friend:)

[Javid]

First issue I can see is apart from the merged cells is that if we use "Column Q to sort Minimum to Maximum" there are blank cells. What happens to that data?

The section with no entry will change with it's own data soon, so we don't need to change or move them in data sheet:(

[Javid]

So many thanks dear Georgiboy,
Let me test your code and will notify the result.:yes

[p45cal]

Hi dear friend,
So many thanks for your answer and code.
I tried to test it but it gives me no act on my data file:dunno
Thanks again and good luck.
That's why pictures are useless; column B looks like merged cells in the picture, but they're not.
Column A does have merged cells so changing my offering to look at that instead of column B gives:
Sub blah()
Set mydict = CreateObject("Scripting.Dictionary")
lr = Cells(Rows.Count, "A").End(xlUp).Row
For Each cll In Range("A12:A" & lr).Cells
If Not mydict.exists(cll.MergeArea.Address) Then mydict.Add cll.MergeArea.Address, cll.MergeArea
Next cll
For Each itm In mydict.items
itm.EntireRow.Columns("G:AH").Sort key1:=Range("Q1"), order1:=1
Next itm
End Sub
and works over here.

[Javid]

Oh Dear my friend it's working very nice:)
Thank you so much for your help :yes:yes:yes

[Javid]

Now i need another help:
How may i change the background of two cells "I" and "Q" in each row with conditional format based on cell "Q" just where cell Q containing the minimum price, in whole data sheet?
These two cells is important because of the minimum price in cell Q and the vendor's name.
In fact I need yellow background for both of these cells, even if we don't sort each or some sections yet, but just where lower price and it's vendor name are both in the same row.
And It's clear that whenever sorting is done, the yellow background will shift with the sorting.

[p45cal]

try:
Sub blah()
Set mydict = CreateObject("Scripting.Dictionary")
lr = Cells(Rows.Count, "A").End(xlUp).Row
For Each cll In Range("A12:A" & lr).Cells
If Not mydict.exists(cll.MergeArea.Address) Then mydict.Add cll.MergeArea.Address, cll.MergeArea
Next cll
Range("Q:Q,I:I").FormatConditions.Delete
For Each itm In mydict.items
itm.EntireRow.Columns("G:AH").Sort key1:=Range("Q1"), order1:=1
Set rngCF = Intersect(itm.EntireRow, Range("I:I,Q:Q"))
With rngCF.FormatConditions.Add(Type:=xlExpression, Formula1:="=" & rngCF.Areas(2).Cells(1).Address(0, 1) & "=MIN(" & rngCF.Areas(2).Address & ")")
.StopIfTrue = False
With .Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
End With
End With
Next itm
End Sub
Don't forget to remove any manual colours you've put in.

[Javid]

Wowwwwwwwwwwwwwww I'ts my ticket:):):):yes:yes:yes
Working so gently and very nice my friend...
So many Thanks for your kindness and the time you spend for solving my problem.
I will be back with just one another and I think my last request in this project too :)
Please Just think around calling photos from this sheet to another sheet using formula.
I will more explain about my request and try to upload my worksheet.
Thanks again and Good luck my friend

[Javid]

HI dear "p45cal"
Sorry i don't know your real name yet and I have to call you by your ID:)
So, I tried to make sample of my original file, but it's not possible because of some formula that i used to create a new summary page and when i delete some rows the new page formula doesn't works and there will be noting in my new summary page after deleting rows!
I know it's my fault and unable to fix this problem and also I'm sure if you can see the file you would help me to fix this issue.
Even I tried delete all of pictures in my file to decrees the file size but now the original file size is around 9 MB.
Please let me know how can I upload my file. Is there any other way to send it for you?
Then I can better describe my requests.
I have some question around my work and need to get your help.
It will be my pleasure if you answer to my request.
Thanks in advance for your help.
Sincerely yours, Javid.

[Aussiebear]

David, Refer back to post#10 for a method on uploading a file. As to size, surely a single worksheets say 100 rows will be of suffice.

[Javid]

David, Refer back to post#10 for a method on uploading a file. As to size, surely a single worksheets say 100 rows will be of suffice.

Dear Aussieber, Thanks for your reminder about how to attach a file, actually I did it as you mentioned but every time I try to upload my sheet and after uploading processes, there is nothing attached and therefore I thought there may be a problem with my file size.
Now please give me a feedback if there is any limitation of file size for upload or any other points.
Thanks and regards.

[p45cal]

Javid,
I was disappointed to discover that you haven't provided links/information about where you have cross posted this topic (you're supposed to).
This message will be blunt, (because I'm exasperated this happens so often and how much time I waste) but is not intended to be nasty.
I'm not a moderator here.

This is where I stand:
1. I'm not helping on this topic any more.
2. I may change my mind about this if:
You read http://www.excelguru.ca/content.php?184 (it'll be obvious if you don't) and take the necessary action (add links or info at the site(s) which have similar rules - (they nearly all do)).
Some people's response to this is to get upset and indignant with the likes of 'the internet is free and I can do what I like' (it's not and you can't) or 'what I do elsewhere on the internet is none of your business' (I agree and refer you to point (1) above).

[Javid]

Hi
Dear friend
I read your message and I was very sorry and of course I was shocked. Not because I asked a few people for help, but because I did not know the rules.
I went to the link you sent me and applied for membership at the address mentioned, and there I apologize.
Whether or not you are still interested in helping me depends on your kindness, and I certainly can not force you to do anything, but I sincerely apologize to you. Thank you for honestly expressing your feelings and for helping me so much, and both are very valuable and unforgettable for me.
I assure you that from now on I will respect your rules.
I'm sorry again and I thank you again and I apologize.
Yours sincerely
And good luck

[Javid]

Javid,
I was disappointed to discover that you haven't provided links/information about where you have cross posted this topic (you're supposed to).
This message will be blunt, (because I'm exasperated this happens so often and how much time I waste) but is not intended to be nasty.
I'm not a moderator here.

This is where I stand:
1. I'm not helping on this topic any more.
2. I may change my mind about this if:
You read http://www.excelguru.ca/content.php?184 (it'll be obvious if you don't) and take the necessary action (add links or info at the site(s) which have similar rules - (they nearly all do)).
Some people's response to this is to get upset and indignant with the likes of 'the internet is free and I can do what I like' (it's not and you can't) or 'what I do elsewhere on the internet is none of your business' (I agree and refer you to point (1) above).

Dear friend, please tell me exactly what should I do so that you do not get angry with me and continue to help me?
Unfortunately, I did not understand what you meant
If you are upset about why I have asked for help in other forums, then this was the last place I came after my frustration with solving my problem and I made my request and you solved my problem with your kind attitude.
Now please tell me what I need to do to continue your cooperation.
I am also very upset and sometimes I can not sleep well even at night because I really do not know why I have upset a kind person like you.
Please talk to me more.
Thank you so much.

[p45cal]

All you're being asked to do is to provide links to wherever you've posted this same question, and preferably do the same at all those sites (if they require it also).
Again, asking for help on multiple forums is OK. Just tell everyone, everywhere else you've asked the same question.

[Javid]

All you're being asked to do is to provide links to wherever you've posted this same question, and preferably do the same at all those sites (if they require it also).
Again, asking for help on multiple forums is OK. Just tell everyone, everywhere else you've asked the same question.

OK thanks for your kindly answer:)
I just ask my request 3 days before asking here on 16 May at this site:
https://www.extendoffice.com/forum/3-excel/2714-conditional-format-with-formula.html#reply-2746
You can visit by yourself to see what happened there and if I found exact answer on my problem.
If this is enough and is what you are looking for and if I understand what you mean, It's my pleasure, and if not, i'll be so glad if you remind me again.
Also tell me to send and post this link wherever necessary.
Thank you so much my friend.

[p45cal]

What about at MrExcel.com?


You can visit by yourself to see what happened there and if I found exact answer on my problem.This is exactly why helpers need these links; so that they can check progress elsewhere and suggest solutions which move you forward rather then you getting a repeat of the same suggestions (which of course, wastes helpers' time).
Other sites have similar rules (see #13 at https://www.mrexcel.com/board/help/message-board-rules/ ) you may want to do something there? I don't know about ExtendOffice.

[p45cal]

Regarding uploading of files, if it's size which is the problem, you can try one of these:

Saving the file as .xlsb, they're usually smaller
Zipping your file into a .zip file
Saving your file to a file sharing service (many are free such as box.net) or Google Drive or One Drive then share a link to that workbook in the forums (I see you shared a picture on Google Drive at ExtendOffice, so you should be able to do the same with a workbook)

[Javid]

What about at MrExcel.com?

This is exactly why helpers need these links; so that they can check progress elsewhere and suggest solutions which move you forward rather then you getting a repeat of the same suggestions (which of course, wastes helpers' time).
Other sites have similar rules (see #13 at https://www.mrexcel.com/board/help/message-board-rules/ ) you may want to do something there? I don't know about ExtendOffice.

Oh Thanks for your reaction and replying to me...

Unfortunately, I had forgotten this site, but as you saw, I got an error with the code of our dear friend Mr. Anthony, and he did not answer me anymore.

This is the link of there:
https://www.mrexcel.com/board/threads/vba.1205546/#post-5887198
Thanks again for talking.
Yes I read the rules in your next messages and now I know exactly what you mean.
I give you the right because of your anger and I'm sorry again.
However, it is still necessary to explain that the non-observance of the rules was due to my ignorance of them, (I did not read them before) and not because underestimate them.

[Javid]

Thank you for your guidance.
Yes, I tried both the first and the second item in the text of your guide for sending the file, but I still have trouble sending the file, even as a low-volume photo, but I will try the third item and insert the upload link for you here.
The fact that I insist that you have the original file is due to the use of formulas that I have written in the "New Table" sheet and I want you to see them and know your opinion.
You will also find out for yourself why I still need your help.
Let me explain my request after you received my Workbook.
Thanks and regard.

https://drive.google.com/file/d/14LjUyCj8gxY23pGTRqabQoA0t7pbHY7m/view?usp=sharing

[p45cal]

Let me explain my request after you received my Workbook.
Received

[Javid]

Received

Ok Thanks:)
If you allow me, I will give a brief explanation about the second sheet "New Table"
I tried to exporting data based on minimum price located on "Compar_prix_Vrac" (Let me call it as Main Table" at column P from main table to "New Table" sheet

I did not have any problem with the formulas (although it was not very pleasant to write them) until I came across two same value exactly in cells "P2199" and "P2200" and stopped there.
Because in the new sheet, both items had to be entered, but I also had to think about the row number, and the only thing that came to my mind was that, for example, the next row number should have the same number but with an indexed such as 311 and 331a
Unfortunately, due to the nested and long formulas, I still can not solve this problem.
That's why I need your help.
Thanks you for your attention and kindness.

[p45cal]

What versions of Excel are you and the person you're helping using?
If you're using a version which includes FILTER as a function that you can use in a formula in a cell, then with a few alterations to the Compar_prix_Vrac sheet we can have a solution where there is a formula in a single cell, and above it copy the headers form the Compar_prix_Vrac sheet.
Otherwise, if you/he are using Excel 2016 or later we can have a solution which involves Power Query (it's built in).

Do you want to include in the New Table those products which have no prices in column P (Prix au Kilo HT)?

[Javid]

Hi dear friend and so many thanks for your replying.
You'r so kind and nice person :friends:

Well let me answer your questions in order:
I use (version 365) and I have the "Filter" function.
In the version of my friend, which is apparently 2016, I do not know if this function is available or not, and I have to wait until Tuesday to answer it!
I'm not sure if he can work with PowerQuery or not, considering he is a beginner!
Unless he don't needs to work with it after we making PowerQuery.
What he urgently needs is to have this table "New Table" and on another table to be able to have a custom list of some items at the lowest price, including seller name, product name, quantity or number of orders. And finally. He can see how much money is needed for this group of orders.
My expectation and plan from the process of preparing the "New Table" tab was that I would finally be able to summarize the custom list he expected from this tab, but I still do not know if I made the right choice. Route or not ...
In this regard, any of your intellectual help can be useful to prevent any mistakes in the path and re-work.
Do you think we can approach his request from the "New Table" tab or is it even possible to use the main tab directly?
Although he asked me to create it, and even if we could succeed directly using the "Compar_prix_Vrac" tab to get the final request, this "new table" tab would be useful for controlling the information entered.
And finally for your last question, that question was mine too, and he answered yes because he puts everything he needs into all the empty cells.

But I think we can temporarily hide them with formulas to prevent empty cells from appearing.
I apologize for the lengthy response.
Thanks again and regards.

[p45cal]

The arrangement of data on the Compar_prix_Vrac sheet makes it very difficult/comvoluted to make a new table from.
Many users of Excel do exactly as you have, used a table for 2 purposes: (1) to hold data and (2) to be looked at by humans (reports). The more friendly you make data to be looked at by humans, the more difficult it is to treat it as data (let's say as a 'database'). You want to create a New Table for people to look at from a table that is also made for people to look at.
You need to keep these reports and database separate. Ideally, a 'database' with NO FORMULAE at all, then we can make reports in multiple ways from that 'database'.
Think on that seriously; it's more important than what follows.

In the attached are 2 solutions (Power Query and Office365 formula solution).

1. Power Query
See the green tabs in the attached link; sheets Compar_prix_Vrac (PQ) and NewTable(PQ).
Power Query made the table in sheet Compar_prix_Vrac (PQ) into a table with its own headers (Column1, Column2 etc.). This is because from a database point of view you shouldn't have column (fields) headers in more than a single row. So in Power Query I convert the top 3 rows into a single header row. I did have to add a header to some columns without headers, such as columns V and AB.
The process of bringing the data into Power Query also unmerges cells, so now there are some empty cells on that sheet; I've tried to get PQ (Power Query) to correct that.
PQ further removes rows with no data, then it takes the minimum values only from each group of No, even if there are more than one, and in the case there are no prices, all the rows are kept.
The resulting table is output onto the sheet NewTable(PQ).

You mention that your friend "puts everything he needs into all the empty cells". As you realise, to do that he will be overwriting your formulae in your 'New Table', and it may not be easy to get them back. The same applies to PQ output, with a major difference: when you refresh a PQ query it overwrites what anyone may have manually put into the table since the last refresh; filled empty cells will be empty cells once again. This goes back to keeping data separate from reports. So what he should do is change the data on the Compar_prix_Vrac (PQ) sheet, then refresh the PQ query.

2. Office 365 Formula
There is a formula in one cell BB7 of the New Table sheet. It is:
=FILTER(Compar_prix_Vrac2!A12:AG5012,Compar_prix_Vrac2!P12:P5012=MINIFS(Com par_prix_Vrac2!P12:P5012, Compar_prix_Vrac2!A12:A5012, Compar_prix_Vrac2!A12:A5012)) In the rows above that formula is a straight copy of the table headers. As you can see this formula uses the Compar_prix_Vrac2 sheet which is a copy of your Compar_prix_Vrac sheet with cells unmerged and data filled in to those previously merged cells. There may be one or two other changes but I've forgotten them! This formula hides all rows with no prices, so Nos with no prices don't show up at all. Same advice as before, adjust the source data (the 'database') and the formula will automatically adjust its results.




Now because your sheets have many formulae in them as well as their formulae being quite complex, along with some of them using volatile functions, then the workbook takes significant time to recalculate when you change something. This has been made worse because I copied the sheets a few times. So I set calculation to manual - just so you're aware if you're not getting formula results updating when you change something.


I'm not going to be able to do anything on this topic until next Tuesday or Wednesday.

File is here: https://app.box.com/s/nh59esei87sleapsn0jtrr6b7rq4o3iq

[Javid]

I'm really ashamed of the hard work you put in :(
Well, my friend, I saw your attachment and realized the many problems you mentioned. I was thinking of such a basic surgery, but in the following order:
1. For cells merged in column "A" in the main sheet, deleting the entire column and use the same column in the new datasheet instead, (this can probably be expected from PowerQuery or Column can be added manually to the new sheet if needed ) but without merging any of the cells, this means that instead of duplicate numbers, we will have new and different numbers at the beginning of each rows. (Maximum 2, because in the new table we will have only the lowest prices, in which case we will probably not have more than two identical prices).
2. To solve the problem of merging column header cells, "converting merged column headers to non-merged ones and adding whatever is needed with single cells containing new text for each one" might increase the number of table columns I suggest, but It is worth avoiding this challenge.
3. You also mentioned that it is better to avoid having any formulas in the main sheet.
I agree, and if I'm not mistaken, we can create the same columns in the new sheet instead of some of the columns in the main table that have the formula.
With this changes in the main sheet, the user will enter only specialized information of goods such as name, seller name, code, type, weight of each package, unit price, tax and any kind of information except calculations and the final result of the calculations will be visible and available only in the new sheet.
If you agree with all three of my suggestions, I can apply all three changes to a new file and send it back to you by next week.
Just please tell me if by making these changes we can solve the power query problem as well as creating a new sheet or there are still other problems blocking and if other changes are needed tell me to do them.
Thank you so much for your consultation again and sorry for bothering you.
have a good and nice weekend.

[Javid]

I forgot to add one thing: the New Table sheet should not be edit or added any field after it has been created by us
The only place he can do that would be on the main sheet or the main board. Therefore, we will not lose any formulas or settings after making it, and this sheet will be just for viewing the summary of operations and any decisions.

[p45cal]

if by making these changes we can solve the power query problem as well as creating a new sheet or there are still other problems blocking and if other changes are needed tell me to do the
I'm not sure which PQ problem you're referring to but separating data from reports is always going to make things more robust. You'll be able to create lots of different new tables (reports), which is relatively easy with PQ, pivot tables, formulae even. Charts too.
The column A which has numbers in is useful if there is no other way to group products for price comparison.

Do what you can re setting up a data table. It doesn't even need to be in any particular order, the reports can do that. Do make the data table a proper Excel table, it:s easier to handle table size changes (it happens automatically).

Data manipulations should be a lot easier, more robust and much less resource hungry.

So go ahead and put something together and I'll look at it next week.

[p45cal]

I forgot to add one thing: the New Table sheet should not be edit or added any field after it has been created by us
The only place he can do that would be on the main sheet or the main board. Therefore, we will not lose any formulas or settings after making it, and this sheet will be just for viewing the summary of operations and any decisions.

Exactly.

[Javid]

Ok my friend I'll do my best and hope to succeed: pray2:
See you next:friends:

[Javid]

Hello my good friend, I hope you are well. I was a few days late due to a serious illness (pulmonary embolism) and I am sorry that I could not work during these few days. I want to summarize and do this as soon as possible. Of course, if I can count on your help.
The FILTER formula you used was very helpful and I was not able to use it properly before and thank you very much
I put the result of my efforts on the web for you. A file that contains 3 similar main tables and 3 summaries for them as well as 1 table for ordering goods. I still have a problem with this table, which I will explain to you about my requests. If possible, I would like to:
1- I want to mount this table on the summary of each of the main tables. (This means that we will have 3 order forms with their data tables.)
I am happy to see the image of each product in the order registration process of each product.
2- The information of these tables must be recorded and stored forever after entering the information and no mistakes will be deleted.
3- Possibility of summarizing based on each of the sellers separately.
I have written some necessary explanations as comments in different places in the file.
Thank you for your attention and help, my good friend, and I will wait for your reply.
Good luck.

https://docs.google.com/spreadsheets/d/1dqCKGC11QjDY-SZuJp5c3akrEVOAkYB3/edit?usp=sharing&ouid=101876086797041785217&rtpof=true&sd=true

[Javid]

the file above had some problem and i fixed it today

Please use this link for downloading my file:
https://docs.google.com/spreadsheets/d/1ZsDh3cAes43N7SVe0Z1KGGh0rmI99SZM/edit?usp=sharing&ouid=101876086797041785217&rtpof=true&sd=true

[p45cal]

the file above had some problem and i fixed it todayCould you tell me what that problem(s) was(were), as I've been working on your msg#44 file?

[Javid]

Could you tell me what that problem(s) was(were), as I've been working on your msg#44 file?


Sorry,
Yes, Unfortunately, I forgot to enter one of the main fields in the order table. (Famillies) i mean, This field was necessary because based on this field, the product type must be selected and then the other fields must be collected from summary1 table

[Javid]

So sorry Uploading the file took a long time and I repeated it several times until I managed to upload the new file

[p45cal]

I need to point out a problem with your use of the FILTER on the SUMMARY sheets.
The problem: Go to cell Q2197 of the Comp_prix_Vrac sheet. You see a value of 9.92. This is product no. 311. In the cell below that you see the same value 9.92. So for Item code 311 A and 311 B you have equal minimum prices. So you should see both on the SUMMARY sheet… but you don't: Go to row 285 of the SUMMARY1 sheet - only one entry (for 311 A).
This is because you have merged cells in column A of the Comp_prix_Vrac sheet. They all need to be unmerged and all need to have the correct No. in.
To demonstrate, go to the merged cells at cell A2197 of the Comp_prix_Vrac sheet, unmerge them, then put 311 into each of the 7 cells A2197:A2203.
Now go back to the SUMMARY1 sheet, row 285 and you'll now see both minima 9.92 appear in rows 285:286.

Note, I still don't know whether your friend has a version of Excel which can use FILTER in a cell.

Unmerging cells and putting the values of the merged area in each cell can be tiresome, so I've written a macro blah2 to do it to column A of whichever sheet is the active sheet. It may take a minute or 2 to run.

More to come on separating data from reports, but not today…

Too big to attach, here's a link: https://app.box.com/s/pgxhx36e51iy9m94ved5gsh7enneenhz

[Javid]

Well, I got your message, with the help of the code you wrote for me, I unmerged column A in all three tables, which of course took more than 20 minutes for each table. The summary sheet problem was partially solved.
Today I noticed a point that the information in the summary2 table is incomplete. This was because we based our choice of the lowest price on the Q column, which only gets its value when we have cash in the M column. But if there is a value in column R instead of column Q, then information In this row will not be added to the summary table!
We will have a quantity in column R when we buy the product in the packaging or quantity format and not based on its weight. However, the numerical value of column R is as valuable to us as the value of Q.
To avoid these problems, I changed the formula written in column Q as follows:

"IFERROR(IFS(P10=0,"",M10>0,P10/M10,N10>0,P10/N10),"")"
" also we must change the title of column Q next" for better meaning.
So please update the new formula to see the result in the summary table. Now there is another problem with using the previous code to sort and color I and Q cells that does not work properly in other tables. Not bad if you test and you see the result.
You may need to change the blah code.

[p45cal]

Well, I got your message, with the help of the code you wrote for me, I unmerged column A in all three tables, which of course took more than 20 minutes for each table.I should probably have switched calculation to manual - every unmerge and replacement of data will have led to a recalculation of many cells, many with long and complicated formulae. By pointing this out you reminded me that there are lots of such formulae in your sheets which makes the whole workbook very resource-hungry. This is something we can address.

Regarding:

The summary sheet problem was partially solved.
Today I noticed a point that the information in the summary2 table is incomplete. This was because…
and:

Now there is another problem with using the previous code to sort and color I and Q cells that does not work properly in other tables.
We have not yet separated raw data from the reporting; we're still using the likes of the Comp_prix_Vrac sheets as both a source of data for reports AND for humans to look at. I think this needs to change.
1. Have raw data in tables which only contain raw data and NO formulae, no pretty colouring, no sorting, no merged cells, no bold, italics, colourful columns, no highlighted cells. Just boring data. Hardly ever seen by humans except to change that data.
2. Use the table(s) in 1 above to produce many reports in all sorts of formats, with added calculated columns, colours, bold, conditional formatting, whatever. This could be in the form of formulae, pivot tables and Power Query tables.


You may need to change the blah code.For the reasons above I don't think you will need to use blah ever again, the reports will do that sort of thing. (By the way, it's stopped working because there are no longer any merged cells.)

I've been preparing some raw data tables and some sample reports but they're not ready yet. I'll take into account what you say about the data in columns I, Q, R etc.
Some of the formulae we've been using are only available in some versions of Office 365, so may not be suitable for your friend. Power Query and pivot tables are available to him if he's using Exel 2016. We do need to know.

I'm going to be largely out of action until next Tuesday.

[Javid]

Hi and thanks again,
You mensioned to have table with no formulas this is good idea and i think we have it now exept some simple calculations we need to.
Since I do not want to bother you too much and also because I asked my friend to prepare a new version of Office, so we will not have a problem with the formulas. If there is another way to display the correct information with the lowest price at the top of each section of Venice with a yellow background, I welcome it because this is an emphasis from my friend. I connected the order sheet with a few simple formulas to the Summary1 table and the result is not bad, but it is still not interesting because it is far from what my friend requested. He wants this list to be created while patrolling the Summary1 table and clicking on his favorite cells !!! Which I do not think is interesting because the possibility of wrong clicks is very high. However, I think it would be better for him to cope with the same order format. The next step, and perhaps the last challenge for me, is to see the image of the same item in the box at the top of the page in the order sheet on each row of the order list we are working on. As for the power quary, I still think they are good ideas, but I have not worked with them much yet and I can not have a clear idea of ​​how they work.
I'll wait for your next coming.

[Javid]

Hi all:hi:
Thanks to all the friends who tried to help solve my problems in this conversation, and I have the best for each and every one of you.

I also have a special thanks to my dear friend "p45cal" who did not spare any effort to help me.

Yours sincerely
Javid

[p45cal]

There's more coming, just been doing other things...

[Javid]

Thanks friend i'll be thankful:beerchug:)