+ Reply to Thread
Results 1 to 6 of 6

Transposing & replicating data sets, and then highlighting matching cells

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

    Transposing & replicating data sets, and then highlighting matching cells

    I'm analysing a large datasheet of over 80k rows. Data in ColA are grouped into sets which are separated by one empty row. Data sets vary dramatically in size which is the number of their rows.

    What I need to do is:
    1. to transpose each data set of ColA, in ColB,
    2. to replicate the transposed cells down in the same number of cells of each set, and finally
    3. to highlight each corresponding/duplicate value/cell between ColA and ColB

    To make life much easier, I have attached an example file with two sheets: Sheet1 with data before processing, and Sheet2 as desired after processing

    Can I get some precious assitance with this question please?

    Many thanks in advance ..

    T.
    Attached Files Attached Files

  2. #2
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: Transposing & replicating data sets, and then highlighting matching cells

    Hi Terryhenderson,
    try this
    Please Login or Register  to view this content.

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

    Re: Transposing & replicating data sets, and then highlighting matching cells

    More than excellent nilem, really fantastic. You solved my problem with such masterpiece code.

    With all due respect, thank you very much indeed.

    T.

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

    Re: Transposing & replicating data sets, and then highlighting matching cells

    Hi Nilem,

    If possible, code modification requested, please:

    As per attached example:

    In Sheet1, during processing my data, I encountered a problem with repeated values in ColA, so I removed them and kept the unique ones only in ColB .

    In Sheet2, starting from ColC, all values in ColB are transposed and replicated down in the same number of cells of the original data set in ColA. Matching values in ColA and other columns starting from ColC are highlighted as we go down.

    Can I get your precious code modified to accommodate this change, please?

    My best regards,

    T.
    Attached Files Attached Files

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

    Cool Hi ! Try this !


    A starter demonstration according to the previous attachment but without the need of column B :

    PHP Code: 
    Sub Demo1()
             
    Dim Rg As RangeRc As Range
        With Sheet1
    .UsedRange
            
    .Offset(, 1).Clear
             Set Rg 
    = .Cells(1).CurrentRegion
        End With
             Application
    .ScreenUpdating False
        With CreateObject
    ("Scripting.Dictionary")
            While 
    Not IsEmpty(Rg(1))
              For 
    Each Rc In Rg
                
    If Not .Exists(Rc.Value2Then .Add Rc.Value2, .Count 2
                Rc
    (1, .Item(Rc.Value2)).Interior.Color vbYellow
              Next
                Rg
    .Offset(, 1).Resize(, .Count).Value2 = .Keys
               
    .RemoveAll
                Set Rg 
    Rg(Rg.Count).End(xlDown).CurrentRegion
            Wend
        End With
             Application
    .ScreenUpdating True
             Set Rg 
    Nothing
    End Sub 
    Do you like it ? So thanks to click on bottom left star icon « Add Reputation » !

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

    Re: Transposing & replicating data sets, and then highlighting matching cells

    Genius thinking leads to genius code. You did it again Marc L, the desired result is completely reached. You solved this problem.

    Je ne sais pas quoi dire sauf vraiment merci beaucoup avec mon profond respect et ma gratitude.

    All the best ..

    T.

+ 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. Replies: 1
    Last Post: 05-21-2013, 07:24 PM
  2. [SOLVED] Highlighting matching cells between many columns within a row
    By jackfjo3 in forum Excel General
    Replies: 2
    Last Post: 06-27-2012, 05:15 PM
  3. 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
  4. Transposing sets of data based on formatting
    By scrchrds in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-28-2011, 02:16 PM
  5. Transposing vertical data sets to horizontal positions
    By Ricker090 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-08-2011, 01:40 PM
  6. Transposing Row Data To Columnar Data, In Sets of Four
    By edgeblade in forum Excel General
    Replies: 2
    Last Post: 10-12-2010, 12:06 PM
  7. highlighting matching cells
    By stevesunfold in forum Excel General
    Replies: 11
    Last Post: 04-11-2008, 07:02 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