Adding code to highlight duplicate values in a auto transpose macro

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

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?

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!!

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?

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.

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,

Simi said:

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.

Click to expand...

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!

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!!

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.

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

hey Priya, sorry I haven't been on for a while.

after you posted your sample sheet I made some modifications and it looks like I forgot to include the A2 cell to copy.

change this section

'now we copy the first value in the list A2 to sheet 2
Sheets("Sheet2").Cells(lRow2, lCol2) = .Cells(lRow1, lCol1)

to

'now we copy the first values in the list A2 & A3 to sheet 2
Sheets("Sheet2").Cells(lRow2, lCol2) = .Cells(2, lCol1) & "," & .Cells(lRow1, lCol1)

Hello Simi,

Please dont be sorry, its absoultely okay

The macro is working fine..however, I observed that the total items is giving me duplicate+non duplicate values,but the total items should be nonduplicate values+Sheet1 A2 (which is the default), also, can you please help me by adding two more things :
1. Whenever we click on auto transpose, it should take the user to sheet 2, A2.
2.No matter on whichever cell the user has left the cursor, whenever anyone opens the sheet, the cursor should be on sheet1 A3.

Many thanks

I'm not at a computer to help with the total counting issue.

however 1 and 2 are pretty easy.

1. at the end of the code for the auto transpose, before the end sub. put Sheets("Sheet2").Range("A2").Select
2. in the code for ThisWorkbook open, put Sheets("Sheet1").Range("A3").Select

I'll be able to upload this in two days if you can't get it to work.

Hey Simi,ok..I tired what you asked me to it is giving me a visual basic error 400.So I can't get it to work. Sure, I will check back after 2 days..thanks!

Simi said:

I'm not at a computer to help with the total counting issue.

however 1 and 2 are pretty easy.

1. at the end of the code for the auto transpose, before the end sub. put Sheets("Sheet2").Range("A2").Select
2. in the code for ThisWorkbook open, put Sheets("Sheet1").Range("A3").Select

I'll be able to upload this in two days if you can't get it to work.

Click to expand...

Simi..it is giving me all the values in one single row on sheet2. it actually had to be first 1000 in one row on sheet 2(Sheet 2 A2), and next thousand on next row..(Sheet2 A3)

Priya,

can you upload the most recent version you are using?

Simi said:

Priya,

can you upload the most recent version you are using?

Click to expand...

Hello Simi,

Please find attached..
The first 1000 values i.e, from A2 to A1001 on sheet 1 should reflect on Row A2 of sheet 2 , the next thousand values on sheet 1 should reflect on A3 of sheet 2 and so on..in the current code, all the values are transposing on Sheet2 row A2 only..
Also, I have added the code that you mentioned for the sheet to go to sheet 2 on clicking auto transpose but it is giving me an error...

Priya,

I have modified this to meet what you need.
Sorry for the incorrect syntax for automatically going to sheet2 for the autotranspose button.
But that is now working correctly.

take a look at see if you need anything else.
I thought you would want the data split on rows for sheet2, thats why I included a row variable for sheet 2.
I simply added a counter variable to tick everytime we add data to sheet to and when it gets to 1000, it moves down a row.
This has been a fun project to help with. depending on how many rows of data you have in sheet1 I think there may be a more efficient way to do this, but with my limited knowledge thats what you get

Hi Simi,

Yes this is what I was looking for, thanks a lot..the only change I would need is "The first values from row A3 are not getting transposed correctly on sheet 2" That is after 999 on sheet 2 A2, from A3 the first few values are not transposing correctly..please find attached....for 1000,1001,1002 I get 100.1+2.34567 etc etc...

Priya, the problem this is having is since we are using numbers as the data to test, when it goes to row#3 and puts the first value 1,000 in it treats it as a number with the comma in it then just compounds the problem as we continue.
I ran the data as A1, and increment the number portion to A3501, and it works correctly.

Is the data you have for sheet 1 numbers, text, or a combination?