Sorting sections within large table

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:
Attachment 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.

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

Code:

---


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

Code:

---

Table = TitleCell.CurrentRegion

---

try running this which acts on the active sheet:

Code:

---

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

---

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.

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:

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

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!
Attachment 29758
Hope i did it right:)

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

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

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

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?

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?

Attachment 29759

If you amend your formula in column Q From:

Code:

---

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

---

To:

Code:

---

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

---

And are hell bent on keeping the merged cells then:

Code:

---

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

Thanks my friend:)

Quote:

---

Originally Posted by 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?

---

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:(

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

Quote:

---

Originally Posted by 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.

---

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:

Code:

---

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.

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

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.

try:

Code:

---

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.

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

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.

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.

Quote:

---

Originally Posted by 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.

---

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.

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).

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

Quote:

---

Originally Posted by 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).

---

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.

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.

Quote:

---

Originally Posted by 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.

---

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...tml#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.

What about at MrExcel.com?

Quote:

---

Originally Posted by Javid

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.

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)

Quote:

---

Originally Posted by p45cal

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/thread.../#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.

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/14Lj...ew?usp=sharing

Quote:

---

Originally Posted by Javid

Let me explain my request after you received my Workbook.

---

Received

Quote:

---

Originally Posted by p45cal

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.

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)?

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.

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 N^o, 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:

Code:

---

=FILTER(Compar_prix_Vrac2!A12:AG5012,Compar_prix_Vrac2!P12:P5012=MINIFS(Compar_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 N^os 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

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.

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.