+ Reply to Thread
Results 1 to 3 of 3

Sorting data sets by first cell only and highlighting it in red

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

    Sorting data sets by first cell only and highlighting it in red

    Dear all,

    I'm anlaysing a large worksheet which contains nearly 80K or rows and extends over 26 columns (A:Z). Data sets are separated by one empty row, and vary in their number of rows, where the least set is just one row of data.

    I need to sort all data sets across all columns according to their FIRST cell ONLY of ColJ, and highlight that first cell in red font for further analysis.

    I have attached a made-up example which illustrates the question, where Sheet1 contains data sample before sorting, and Sheet2 contains data sets after sorting and highlighting ColJ each set's first cell in red font.

    I do hope I can get some precious assistance with this question, please.

    Many thanks in advance.

    T.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor saravnepali's Avatar
    Join Date
    01-14-2019
    Location
    Sydney Australia
    MS-Off Ver
    2010
    Posts
    447

    Re: Sorting data sets by first cell only and highlighting it in red

    Hi,
    I get what you want to highlight in red.

    But, with the sorting part its hard to figure out what you want as the sheet says all cells as data (as a text) - doesn't help much with how you want to sort as per ColJ in the question
    If you think someone helped you, click on the "* Add Reputation" as a way to say thank you.

    If your problem is solved, go to Thread Tools and select Mark This Thread Solved

  3. #3
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Cool Hi ! Try this ‼


    According to your attachment a beginner starter demonstration :

    PHP Code: 
    Sub Demo1()
          
    Dim Rg As RangeR&
          
    Application.ScreenUpdating False
          Set Rg 
    = [A1].CurrentRegion
        
    While Rg.Count 1
          R 
    1
          Cells
    (R29).Resize(, 2).Value2 = Array(Rg(110).Value2Rg(127).Resize(Rg.Rows.Count 1).Address)
          
    Rg(110).Font.Color vbRed
          Set Rg 
    Rg(Rg.Rows.Count)(3).CurrentRegion
        Wend
        With 
    [AC1].CurrentRegion
            
    .Sort .Cells(1), xlAscendingHeader:=xlNo
             
    For Each Rg In .Columns(2).Cells:  Range(Rg.Value2).Value2 Rg.Row:  Next
            
    .Clear
        End With
        With ActiveSheet
    .UsedRange
            
    .Sort [AA1]
            .
    Columns(27).Clear
        End With
          Application
    .ScreenUpdating True
    End Sub 
    ► Do you like it ? ► ► So thanks to click at least on bottom left star icon « Add Reputation » ‼
    Last edited by Marc L; 12-06-2019 at 10:49 AM. Reason: little optimization …

+ 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. [SOLVED] Transposing & replicating data sets, and then highlighting matching cells
    By terryhenderson in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-22-2019, 11:44 PM
  2. Sorting Data into two different Sets
    By santanicopandimonium in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-01-2018, 12:16 PM
  3. [SOLVED] Sorting two data sets on Cluster chart
    By inq80 in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 09-15-2015, 09:23 AM
  4. VBA: sorting sets of data
    By Grocson in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-14-2013, 12:27 AM
  5. Highlighting differences between two sets of data
    By Barking_Mad in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 11-02-2011, 11:44 AM
  6. Sorting sets of rows based on cell
    By mpkavanagh in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 02-17-2011, 11:35 AM
  7. sorting different sets of data
    By Armageddon85 in forum Excel General
    Replies: 4
    Last Post: 02-05-2008, 04:37 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