Sorting sections within large table

[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
See you next

[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...f=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...f=true&sd=true

[p45cal]

the file above had some problem and i fixed it today

Could 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 N^o. 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
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)