Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to find two consecutive identical cells (in a column).
My machine names are in the first column in a spreadsheet. I need to find or
flag in some way, those instances in which a machine name in one row is followed by the same name appearing again in the next row's first column. It would be fine (but not necessary) to reveal this by deleting all other rows that do not repeat in this way. Thanks, john c |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to find two consecutive identical cells (in a column).
sub RemoveNonDups()
Dim i as Long, lastrow as Long lastrow = cells(rows.count,1).End(xlup).row for i = lastrow to 2 step -1 if cells(i,1).value < cells(i-1).value then rows(i).Delete end if Next if cells(i,1).value < cells(i+1).value then rows(1).Delete End if End Sub -- Regards, Tom Ogilvy "How did I get here?" <How did I get wrote in message ... My machine names are in the first column in a spreadsheet. I need to find or flag in some way, those instances in which a machine name in one row is followed by the same name appearing again in the next row's first column. It would be fine (but not necessary) to reveal this by deleting all other rows that do not repeat in this way. Thanks, john c |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to find two consecutive identical cells (in a column).
John,
Use a helper column of formulas: in a cell in row 2, enter the formula =OR(A2=A1,A2=A3) and then copy down to match your data. Then filter on that column to show TRUE values only. HTH, Bernie MS Excel MVP "How did I get here?" <How did I get wrote in message ... My machine names are in the first column in a spreadsheet. I need to find or flag in some way, those instances in which a machine name in one row is followed by the same name appearing again in the next row's first column. It would be fine (but not necessary) to reveal this by deleting all other rows that do not repeat in this way. Thanks, john c |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to find two consecutive identical cells (in a column).
Thanks for your help. I worked with the next answer because it was simpler.
I will keep your script idea though in case I do need to delete non-dupe rows. Thanks, john c "How did I get here?" "Tom Ogilvy" wrote: sub RemoveNonDups() Dim i as Long, lastrow as Long lastrow = cells(rows.count,1).End(xlup).row for i = lastrow to 2 step -1 if cells(i,1).value < cells(i-1).value then rows(i).Delete end if Next if cells(i,1).value < cells(i+1).value then rows(1).Delete End if End Sub -- Regards, Tom Ogilvy "How did I get here?" <How did I get wrote in message ... My machine names are in the first column in a spreadsheet. I need to find or flag in some way, those instances in which a machine name in one row is followed by the same name appearing again in the next row's first column. It would be fine (but not necessary) to reveal this by deleting all other rows that do not repeat in this way. Thanks, john c |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to find two consecutive identical cells (in a column).
"How did I get here?" wrote: My machine names are in the first column in a spreadsheet. I need to find or flag in some way, those instances in which a machine name in one row is followed by the same name appearing again in the next row's first column. It would be fine (but not necessary) to reveal this by deleting all other rows that do not repeat in this way. Thanks, john c |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to find two consecutive identical cells (in a column).
"How did I get here?" <How did I get wrote
in message ... My machine names are in the first column in a spreadsheet. I need to find or flag in some way, those instances in which a machine name in one row is followed by the same name appearing again in the next row's first column. It would be fine (but not necessary) to reveal this by deleting all other rows that do not repeat in this way. Thanks, john c SourceRange is the Top Cell of your contiguous range ---------------------------------------- Public Sub FlagNonDups() Dim i As Long, SourceRange As Range Set SourceRange = [Sheet12!H90] If Not IsEmpty(SourceRange.Offset(1, 0)) Then Set SourceRange = SourceRange.Resize(SourceRange. _ End(xlDown).Row - SourceRange.Row + 1) End If For i = 1 To SourceRange.Rows.Count - 1 If SourceRange(i) < SourceRange(i + 1) Then SourceRange(i) = "*" Else i = i + 1 End If Next End Sub ----------------------------------------- Ciao Bruno |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to find two consecutive identical cells (in a column).
1. It worked when we changed it to: =OR(A1=A2)
2. Your idea also gave my co-worker the idea to do this: =IF(A1=A2,"duplicate","OK") (and dragged down...) Thanks! john "How did I get here?" "Bernie Deitrick" wrote: John, Use a helper column of formulas: in a cell in row 2, enter the formula =OR(A2=A1,A2=A3) and then copy down to match your data. Then filter on that column to show TRUE values only. HTH, Bernie MS Excel MVP "How did I get here?" <How did I get wrote in message ... My machine names are in the first column in a spreadsheet. I need to find or flag in some way, those instances in which a machine name in one row is followed by the same name appearing again in the next row's first column. It would be fine (but not necessary) to reveal this by deleting all other rows that do not repeat in this way. Thanks, john c |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to find two consecutive identical cells (in a column).
John,
If you need to delete non-dupe rows (with my solution rather than a macro), simply filter for FALSE, then select all the FALSES, and use edit / go to.. Special cells Visible cells only, and delete the rows. Then turn off the filter. HTH, Bernie MS Excel MVP "How did I get here?" wrote in message ... Thanks for your help. I worked with the next answer because it was simpler. I will keep your script idea though in case I do need to delete non-dupe rows. Thanks, john c "How did I get here?" "Tom Ogilvy" wrote: sub RemoveNonDups() Dim i as Long, lastrow as Long lastrow = cells(rows.count,1).End(xlup).row for i = lastrow to 2 step -1 if cells(i,1).value < cells(i-1).value then rows(i).Delete end if Next if cells(i,1).value < cells(i+1).value then rows(1).Delete End if End Sub -- Regards, Tom Ogilvy "How did I get here?" <How did I get wrote in message ... My machine names are in the first column in a spreadsheet. I need to find or flag in some way, those instances in which a machine name in one row is followed by the same name appearing again in the next row's first column. It would be fine (but not necessary) to reveal this by deleting all other rows that do not repeat in this way. Thanks, john c |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to find two consecutive identical cells (in a column).
Just to add a refinement.
You can skip the Edit/goto special cells, visible cells only part - it is redundant. When you delete rows in a filter, only the visible rows are deleted. As to your comment about simplicity, I am not sure how Edit=Macro=Macros then Select the macro name in the dialog and hit run is all that complex <g. -- Regards, Tom Ogilvy "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... John, If you need to delete non-dupe rows (with my solution rather than a macro), simply filter for FALSE, then select all the FALSES, and use edit / go to.. Special cells Visible cells only, and delete the rows. Then turn off the filter. HTH, Bernie MS Excel MVP "How did I get here?" wrote in message ... Thanks for your help. I worked with the next answer because it was simpler. I will keep your script idea though in case I do need to delete non-dupe rows. Thanks, john c "How did I get here?" "Tom Ogilvy" wrote: sub RemoveNonDups() Dim i as Long, lastrow as Long lastrow = cells(rows.count,1).End(xlup).row for i = lastrow to 2 step -1 if cells(i,1).value < cells(i-1).value then rows(i).Delete end if Next if cells(i,1).value < cells(i+1).value then rows(1).Delete End if End Sub -- Regards, Tom Ogilvy "How did I get here?" <How did I get wrote in message ... My machine names are in the first column in a spreadsheet. I need to find or flag in some way, those instances in which a machine name in one row is followed by the same name appearing again in the next row's first column. It would be fine (but not necessary) to reveal this by deleting all other rows that do not repeat in this way. Thanks, john c |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to find two consecutive identical cells (in a column).
Tom,
Just to add a refinement. You can skip the Edit/goto special cells, visible cells only part - it is redundant. When you delete rows in a filter, only the visible rows are deleted Too true, just a bad habit from having deleted the header row too many times. Bernie |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to find two consecutive identical cells (in a column).
John,
That formula won't work to show both rows, and would result in the deletion of half of each duplicate set if you use that to filter/delete rows. HTH, Bernie MS Excel MVP "How did I get here?" wrote in message ... 1. It worked when we changed it to: =OR(A1=A2) 2. Your idea also gave my co-worker the idea to do this: =IF(A1=A2,"duplicate","OK") (and dragged down...) Thanks! john "How did I get here?" "Bernie Deitrick" wrote: John, Use a helper column of formulas: in a cell in row 2, enter the formula =OR(A2=A1,A2=A3) and then copy down to match your data. Then filter on that column to show TRUE values only. HTH, Bernie MS Excel MVP "How did I get here?" <How did I get wrote in message ... My machine names are in the first column in a spreadsheet. I need to find or flag in some way, those instances in which a machine name in one row is followed by the same name appearing again in the next row's first column. It would be fine (but not necessary) to reveal this by deleting all other rows that do not repeat in this way. Thanks, john c |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Keep all cells in a column unique? No cells identical | Excel Worksheet Functions | |||
how to autofill a consecutive cells in a column? | Excel Worksheet Functions | |||
how to autofill a consecutive cells in a column? | Excel Discussion (Misc queries) | |||
count text in non consecutive cells in column | Excel Discussion (Misc queries) | |||
find the max values for cells in consecutive groups of 600 | Excel Discussion (Misc queries) |