Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
HOW TO LOCATE DUPLICATES ON TWO COLUMNS
I have this:
A B C 1 189 xxx $1.5 2 001 xxx $8.0 3 189 xxx $1.5 4 189 xxx $2.0 I just want to know when only column A and column C have the same numbers, not just one column;... but both have to match. On this case would be Row # 3 the one that is duplicate and I wanted to find it. Thank you so much!!!!! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
HOW TO LOCATE DUPLICATES ON TWO COLUMNS
hi Would this be acceptable (put this in a module and run macro "main") Sub main() 'Concatenate col a and col c rowcount = Cells(Cells.Rows.Count, "a").End(xlUp).Row For i = 1 To rowcount Range("a" & i).Select val1 = ActiveCell.Value Range("c" & i).Select val2 = ActiveCell.Value Range("d" & i).Select ActiveCell.Value = val1 & val2 Next Call sort_cold Call hightligh_duplicate Call clean_up End Sub Sub sort_cold() rowcount = Cells(Cells.Rows.Count, "a").End(xlUp).Row Range("a1:" & "d" & rowcount).Select Selection.Sort Key1:=Range("D1"), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Range("a1").Select End Sub Sub hightligh_duplicate() Range("d1").Select FirstItem = ActiveCell.Value seconditem = ActiveCell.Offset(1, 0).Value offsetcount = 1 Do While ActiveCell < "" If FirstItem = seconditem Then ActiveCell.Offset(0, -3).Select ActiveCell.Offset(offsetcount, 0).Interior.Color = RGB(255, 0, 0) ActiveCell.Offset(0, 3).Select offsetcount = offsetcount + 1 seconditem = ActiveCell.Offset(offsetcount, 0).Value Else ActiveCell.Offset(offsetcount, 0).Select FirstItem = ActiveCell.Value seconditem = ActiveCell.Offset(1, 0).Value offsetcount = 1 End If Loop ScreenUpdating = True End Sub Sub clean_up() Columns("D:D").Select Selection.ClearContents Range("A1").Select End Sub -- jetted ------------------------------------------------------------------------ jetted's Profile: http://www.excelforum.com/member.php...o&userid=17532 View this thread: http://www.excelforum.com/showthread...hreadid=563805 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
HOW TO LOCATE DUPLICATES ON TWO COLUMNS
In cell D1 put in the formula:
="A"&A1&"C"&C1 Copy the formula down as far as you have data. Then with column D selected, go to Format | Conditional Formatting. Select Formula Is for the dropdown box. Put =COUNTIF(D:D,D1)1 in the formula box. Click Format button and pick a color in the Patterns tab. All the duplicte ones should then be highlighted in column D. -Simon "HERNAN" wrote: I have this: A B C 1 189 xxx $1.5 2 001 xxx $8.0 3 189 xxx $1.5 4 189 xxx $2.0 I just want to know when only column A and column C have the same numbers, not just one column;... but both have to match. On this case would be Row # 3 the one that is duplicate and I wanted to find it. Thank you so much!!!!! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
HOW TO LOCATE DUPLICATES ON TWO COLUMNS
Thank you so much,... but is not working I did it exactly as you told me to.
"SimonCC" wrote: In cell D1 put in the formula: ="A"&A1&"C"&C1 Copy the formula down as far as you have data. Then with column D selected, go to Format | Conditional Formatting. Select Formula Is for the dropdown box. Put =COUNTIF(D:D,D1)1 in the formula box. Click Format button and pick a color in the Patterns tab. All the duplicte ones should then be highlighted in column D. -Simon "HERNAN" wrote: I have this: A B C 1 189 xxx $1.5 2 001 xxx $8.0 3 189 xxx $1.5 4 189 xxx $2.0 I just want to know when only column A and column C have the same numbers, not just one column;... but both have to match. On this case would be Row # 3 the one that is duplicate and I wanted to find it. Thank you so much!!!!! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
HOW TO LOCATE DUPLICATES ON TWO COLUMNS
Hmm, not really sure what's wrong. Do you get any error messages? Or maybe
no messages at all but just no highlights in column D? Can you post a few sample resulting values for column D? Also try opening the condition format pop up again while you're in one of the cells in column D, what do you see? That's all the questions I can think of for now. -Simon "HERNAN" wrote: Thank you so much,... but is not working I did it exactly as you told me to. "SimonCC" wrote: In cell D1 put in the formula: ="A"&A1&"C"&C1 Copy the formula down as far as you have data. Then with column D selected, go to Format | Conditional Formatting. Select Formula Is for the dropdown box. Put =COUNTIF(D:D,D1)1 in the formula box. Click Format button and pick a color in the Patterns tab. All the duplicte ones should then be highlighted in column D. -Simon "HERNAN" wrote: I have this: A B C 1 189 xxx $1.5 2 001 xxx $8.0 3 189 xxx $1.5 4 189 xxx $2.0 I just want to know when only column A and column C have the same numbers, not just one column;... but both have to match. On this case would be Row # 3 the one that is duplicate and I wanted to find it. Thank you so much!!!!! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
HOW TO LOCATE DUPLICATES ON TWO COLUMNS
just nothing happens, no messages, do I have to copy and paste that formula
just in D1? "SimonCC" wrote: Hmm, not really sure what's wrong. Do you get any error messages? Or maybe no messages at all but just no highlights in column D? Can you post a few sample resulting values for column D? Also try opening the condition format pop up again while you're in one of the cells in column D, what do you see? That's all the questions I can think of for now. -Simon "HERNAN" wrote: Thank you so much,... but is not working I did it exactly as you told me to. "SimonCC" wrote: In cell D1 put in the formula: ="A"&A1&"C"&C1 Copy the formula down as far as you have data. Then with column D selected, go to Format | Conditional Formatting. Select Formula Is for the dropdown box. Put =COUNTIF(D:D,D1)1 in the formula box. Click Format button and pick a color in the Patterns tab. All the duplicte ones should then be highlighted in column D. -Simon "HERNAN" wrote: I have this: A B C 1 189 xxx $1.5 2 001 xxx $8.0 3 189 xxx $1.5 4 189 xxx $2.0 I just want to know when only column A and column C have the same numbers, not just one column;... but both have to match. On this case would be Row # 3 the one that is duplicate and I wanted to find it. Thank you so much!!!!! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
HOW TO LOCATE DUPLICATES ON TWO COLUMNS
is the conditional format exactly as simon said, ii has not put " " around the formula? you have picked a colour to highlight as well! Technically what u asked for is in cell d1 Put =COUNTIF($D$1:D1,D1)1 in the formula box. as you said you only wanted the second case highlighted. the first way will highlight both. you then need to copy the format down to the other cells. paste special format Regards Dav -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=563805 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
HOW TO LOCATE DUPLICATES ON TWO COLUMNS
i told you to copy it down, simon told you to select the column to start with, both have a similar effect. go to a cell where it is not working and check the formats, conditional formating, are there any there? Regards Dav -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=563805 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to compare two columns and remove duplicates? | Excel Discussion (Misc queries) | |||
How to swap rows and columns? | Excel Discussion (Misc queries) | |||
locate duplicates by sing conditional formatting--doesn't work for | Excel Discussion (Misc queries) | |||
Removing Near-Duplicate Rows, Leaving Those w/Most Data in Specific Columns | Excel Discussion (Misc queries) |