Page 1 of 3 1 2 3 LastLast
Results 1 to 10 of 22

Thread: Adding code to highlight duplicate values in a auto transpose macro

  1. #1

    Adding code to highlight duplicate values in a auto transpose macro



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello,
    1
    I am using the below macro to auto transpose values from sheet 1 to sheet 2 .The macro already has values to remove duplicates and sort. Now, am looking for some help to add a code so that the duplicate values get highlighted and the total number of duplicate values show in D8 and the total number of non duplicate values show in D9.For example : out of 100 values, if 50 values are duplicate, then number "50" should show in D8 and Number "50"should show in D9..is this possible..please help

    Private Sub CommandButton1_Click() 'Transposed to Sheet2 with a Blank Row between each Row Dim rng As Range, j As Long j = 2 With Sheets("Sheet1") .Range(Range("A3"), Range("A3").End(xlDown)).RemoveDuplicates Columns:=1, Header:=xlNo .Range("A3:A" & .Range("A" & .Rows.Count).End(xlUp).Row).Sort Key1:=.Range("A3"), Order1:=xlAscending, Header:=xlNo For i = 2 To .Range("A" & Rows.Count).End(xlUp).Row Step 1000 If i = .Range("A" & Rows.Count).End(xlUp).Row Then Exit Sub Set rng = .Range(Cells(i, 1), Cells(i + 1000, 1)) Sheets("Sheet2").Cells(j, 1).Resize(1, rng.Count - 1) = Application.Transpose(rng) j = j + 2 Next i End With Sheets("Sheet2").Select End Sub

  2. #2
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0
    First off I think this thread should be in the VBA forum, you may get more responses.

    Next why report the duplicates in D8 and D9, and of what sheet?
    Also what sheet do you want the values to be highlighted? to highlight yellow you can use .Range("A1").Interior.ColorIndex = 6

    Could you upload a sample file?

  3. #3

    Hi Simi,

    I dint realise I posted in the wrong forum.
    I have attached the workbook that am using, there are two sheets on the workbook, Sheet 1 and sheet 2.
    Whenever I paste values in sheet i under the "Paste the company ids " option, it is transposing all the values to sheet 2.
    Now, what am looking for is, the total number of duplicate values on sheet one should get highlighted and show on column D8 on sheet 1 and the number of non duplicate values should show on D9 of sheet 1.
    For example :

    Apple
    Mango
    Banana
    Apple
    Grapes
    Pumpkin
    Strawberry
    Then, On sheet 1, apple should get highlighted and D8 should have 1 as there is only one duplicate value(apple) and D9 should have 5 (non duplicate values)
    I just need the code for it be it D8 D9 or anything else, I can change that..Thank you very much in advance!!
    Attached Files Attached Files

  4. #4
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0
    ok real quick question for you, you want all the rows from sheet 1, to bet put in cell A1 with a comma separating the values?
    Do you want all the ,,,,,,, at the end or just stop when you have the last value from sheet1?

  5. #5
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0
    Well either way, I made an option before you reposted, and I have modified it slightly.
    the sub Simi_Transpose2 is what data is shown in this file.
    I also moved the reporting of the duplicates to row 11 so I could see them.
    Hope this helps.
    Attached Files Attached Files

  6. #6
    Hello Simi,

    The macro is not working on my system.
    Ok let me tell you the whole project.
    We work on thousands of values on a daily basis, so every time we receive a sheet with thousands of value, our job requires us to transpose the first thousand values with comas and copy it from there, and then take next thousand and so on...so on the existing sheet I can get the first thousand values on sheet 1 to second row of sheet two, and the next thousand in A3 of sheet 2 and so on.

    I have two questions actually :

    Whenever there are any duplicate values that we copy on to sheet 1 from our datasheet, I want the values to get highlighted and the total number of duplicate values and non duplicate to be displayed in any adjacent row, without having to manually count on the basis of values that get highlited(I mean like in my previous example 7 should be automatically counted by the macro)
    Also, the color in the duplicate values is not getting deleted , so the macro is showing yellow colour even after the duplicate values are deleted.
    My second question is : Yes, you are right, my comas should stop with the last value in sheet 2's rows, with the maximum value being the 999th value(one value is hidden on sheet1 A2 which should always be there..)
    Am sure you made the macro but am somehow not able to use it, am re reading it to understand as much as I can..

    I somehow managed to get the auto transpose sheet with my friend's help,

  7. #7
    Quote Originally Posted by Simi View Post
    Well either way, I made an option before you reposted, and I have modified it slightly.
    the sub Simi_Transpose2 is what data is shown in this file.
    I also moved the reporting of the duplicates to row 11 so I could see them.
    Hope this helps.
    So, when I click on the auto transpose button,it should sort the values,highlight the duplicate values,(I think highlighted font is enough) , the total number of duplicate values and non duplicate values should show on sheet 1..and the transposed values should show on sheet 2 with coma ending with the last value.. the values should get transposed to sheet 2, with first thousand on sheet 2 A2, next thousand on sheet 2 A3 and so on..

    Or if we can have a seperate macro button which on clicking gives e the total number of dup and non dup also will be perfect!

  8. #8
    hey Simi!!! Am so sorry, I am able to use it!! I was making a mistake while assigning the macro,however, can we do something so that the cell that is getting highlighted to yellow should change back to white on clicking "Clear all'" Many many thanks!!

  9. #9
    I added Selection.Interior
    .Pattern = xlNone
    .TintAndShade = 0
    .PatternTintAndShade = 0
    Range("A3").Select

    End With
    End Sub to the clear all macro and it is working...please let me know if I need to change something.

  10. #10
    Am using simi_transpose 2, however it is not giving me.. the value in sheet 1 A2, svcdlvcpo on to sheet 2 A2...otherwise it is exactly what I was looking for..

Page 1 of 3 1 2 3 LastLast

Tags for this Thread

Posting Permissions

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