+ Reply to Thread
Results 1 to 18 of 18

Particular sorting of data groups using three criteria

  1. #1
    Forum Contributor
    Join Date
    04-04-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    204

    Particular sorting of data groups using three criteria

    Hi all,

    I have a large datasheet that is composed of 6 columns (A:G) and nearly 37000 rows. Data sets are collated into groups separated by one blank row.
    In order to accurately anlayze the data, I need to sort all data groups only by the first row in each group.
    Sorting that first row of each group will have three ascending criteria in the following order:
    1. ColB,
    2. ColF,
    3. the order of the first word in red in ColD.

    As per attached example, data groups from Sheet1 are sorted out in the required manner in Sheet2:
    - the first group is sorted by ColB (value 2), ColF (value 4) and the order of the first string in red which is "we"; the fifth in order.
    - the last group is sorted by ColB (value 8), ColF (value 5) and the order of the first string in red which is "think"; the fourth in order.

    I really hope I could get some valuable assistance with this question

    Many thanks in advance

    T.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Particular sorting of data groups using three criteria

    Can you explain how in your example ( a very good one ) col B with Red data is making a difference?
    It seems that only acting on col A and F is enough ..!
    Last edited by PCI; 04-19-2019 at 08:32 AM.
    - Battle without fear gives no glory - Just try

  3. #3
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Particular sorting of data groups using three criteria

    As a first run , see next code
    To be launched from sheet1
    Please Login or Register  to view this content.
    Last edited by PCI; 04-20-2019 at 07:37 AM.

  4. #4
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Particular sorting of data groups using three criteria

    A remake with Col B
    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    04-04-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    204

    Re: Particular sorting of data groups using three criteria

    Thanks very much PCI for your genius code. In fact, it ran smoothly and perfectly on a small sample (real) data of 603 rows.
    After which it halted with an error message as follows:
    HTML Code: 
    As I debugged it, it highlighted the following line in the code in yellow:
    Please Login or Register  to view this content.
    I'm not sure if I'm right, but I think there could be a duplicate group casuing such error. If you think I'm not wrong, then is there a way in which we find out if there's any duplicate groups in the entire data sheet?

    I'm more than obliged to you for your assistance, thanks very much.

    T.
    Last edited by terryhenderson; 04-19-2019 at 10:36 AM.

  6. #6
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Particular sorting of data groups using three criteria

    Which code are you using : The second one ???

  7. #7
    Forum Contributor
    Join Date
    04-04-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    204

    Re: Particular sorting of data groups using three criteria

    Yes, the second one.

  8. #8
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Particular sorting of data groups using three criteria

    So yes it seems like a duplicate so waht to do ??
    Can you send your file?

  9. #9
    Forum Contributor
    Join Date
    04-04-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    204

    Re: Particular sorting of data groups using three criteria

    Thanks very much.
    In fact, I cannot send the real data out as they are considered classified. This is why I had to spend a lot of time making up an example that would work like the real data.
    Can you use my example to duplicate one or two groups and work around it?
    Plus qu'obligé : )

  10. #10
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Particular sorting of data groups using three criteria

    Can you use my example to duplicate one or two groups and work around it?
    Houps ... yes of course,, but the question is what to do with the duplicate: Delete it, copy it just after ??
    How many duplicates it may exists: More than 10 ??

  11. #11
    Forum Contributor
    Join Date
    04-04-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    204

    Re: Particular sorting of data groups using three criteria

    I'm really enjoying this.
    Sure, due to the sensitivity of the data, I believe if you could 'move' the duplicate groups to another sheet for manual verification, that would be the best option.
    We can then run your magic code to sort all 'unique' data in the desired manner.
    Thanks very much indeed PCI.

    T.

  12. #12
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Particular sorting of data groups using three criteria

    See next code: Duplicates are copied just one after the other.
    Please Login or Register  to view this content.

  13. #13
    Forum Contributor
    Join Date
    04-04-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    204

    Re: Particular sorting of data groups using three criteria

    Genius code from a genius mind. Honestly, this is amazing PCI. I never thought I would get that quality outcome. The entire data sheet is meticulously organised and very clear and easy for analysing now.
    Thank you very much PCI. You do have a great value added to this world.

    Merci beaucoup

    T.

  14. #14
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Particular sorting of data groups using three criteria

    So it works, good news.
    Happy Easter
    PCI

  15. #15
    Forum Contributor
    Join Date
    04-04-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    204

    Re: Particular sorting of data groups using three criteria

    Hi PCI,

    Joyeuses Pâques à vous aussi : )
    In fact, I finished the first file which was in English. Now as I'm working on the second file, where data in ColD is in Arabic, I find the first red string criterion is the last string in the cell as it reads from right to left.
    I think we need to reverse the action of function RedPos, so it takes the position of the first red word from the right not the left.
    Is it possible to apply this modification if it is what's needed not something else?
    Very much appreciated.
    T.

  16. #16
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Particular sorting of data groups using three criteria

    we need to reverse the action of function RedPos, so it takes the position of the first red word from the right not the left

    Yes change
    Please Login or Register  to view this content.
    With

    Please Login or Register  to view this content.

  17. #17
    Forum Contributor
    Join Date
    04-04-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    204

    Re: Particular sorting of data groups using three criteria

    Thank you very much PCI .. really thank you ..
    Please accept my best personal regards.

    T.

  18. #18
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Particular sorting of data groups using three criteria

    You are welcome.
    It was a pleasure

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Sorting groups of data
    By Shakeel Ahmad in forum Excel General
    Replies: 4
    Last Post: 02-23-2018, 05:35 AM
  2. Replies: 4
    Last Post: 07-22-2015, 07:13 AM
  3. [SOLVED] Sorting data into ranges/groups
    By Bandicoot in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-16-2015, 10:08 AM
  4. Sorting large groups of data..
    By GroupStats in forum Excel General
    Replies: 3
    Last Post: 06-17-2013, 12:48 AM
  5. sorting data in groups at pivot table
    By ruleworld in forum Excel General
    Replies: 4
    Last Post: 01-07-2011, 10:31 PM
  6. Sorting Data in groups
    By elfnt in forum Excel General
    Replies: 7
    Last Post: 08-19-2010, 11:18 AM
  7. Sorting data in groups
    By baheck in forum Excel General
    Replies: 1
    Last Post: 03-30-2010, 05:39 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1