| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need program logic to sort fieldA & delete duplicates
Hello
I have a worksheet that I am building data in. I would like to than sort on that data and than delete and duplicates such that the next non dup would appear as the next line. Does someone have some logic for this? FIELD A Field B K-0000002673-970728-NHO9 ASFOA_US_Healthcare3 K-0000002673-970728-NHO9 ASFOA_US_Healthcare3 K-0000002673-970728-NHO9 ASFOA_US_Healthcare3 K-0000002673-970728-NHO9 ASFOA_US_Healthcare3 K-0000002673-970728-NHO9 ASFOA_US_Healthcare3 K-0000002673-970728-NHO9 ASFOA_US_Healthcare3 K-0000002673-970728-NHO9 ASFOA_US_Healthcare3 K-0000002673-970728-NHO9 ASFOA_US_Healthcare3 K-0000002673-970728-NHO9 ASFOA_US_Healthcare3 K-0000002673-970728-NHO9 ASFOA_US_Healthcare3 K-0000002673-970728-NHO9 ASFOA_US_Healthcare3 K-0000002673-970728-NHO9 ASFOA_US_Healthcare3 K-0000002673-970728-NHO9 ASFOA_US_Healthcare3 K-0000007526-000500-BSMG ASFOA_US_Healthcare1 K-0000007526-000705-MAIN ASFOA_US_Healthcare1 K-0000007526-000705-MAIN ASFOA_US_Healthcare1 K-0000007526-000705-MAIN ASFOA_US_Healthcare1 K-0000007526-000705-MINC ASFOA_US_Healthcare1 K-0000007526-000705-MINC ASFOA_US_Healthcare1 K-0000007526-000705-MINC ASFOA_US_Healthcare1 K-0000007526-000705-MSOO ASFOA_US_Healthcare1 K-0000007526-000705-MSOO ASFOA_US_Healthcare1 K-0000007526-000705-MSOO ASFOA_US_Healthcare1 K-0000007526-000705-PROB ASFOA_US_Healthcare1 K-0000007526-000705-PROB ASFOA_US_Healthcare1 K-5677-000701-CONS ASFOA_US_Healthcare1 K-5677-000701-CONS ASFOA_US_Healthcare1 K-5677-000701-CONS ASFOA_US_Healthcare1 K-5677-000701-CONS ASFOA_US_Healthcare1 Here is LOGIC I have to build the data Dim br As Long br = Cells(Rows.Count, "b").End(xlUp).Row Cells.Select ' First Clear all contents of worksheet ActiveSheet.Cells.ClearContents ' Second Add a line that describes the data fields ' Also add the formulas for obtaining the data Cells(1, "a") = "WBSE Number" Cells(1, "b") = "WBSE Description" Cells(1, "c") = "Project Cost Centre" Cells(2, "a") = "='SWIM Time Data'!F2" 'WBSE Number Cells(2, "b") = "='SWIM Time Data'!I2" 'Project name ' Thirdly Auto fill down for the number rows we obtained from the SAP-Simulation Cells(2, "a").AutoFill Destination:=Range(Cells(2, "a"), Cells(br, "a")) Cells(2, "b").AutoFill Destination:=Range(Cells(2, "b"), Cells(br, "b")) Columns("A:A").ColumnWidth = 24.75 Columns("B:B").ColumnWidth = 20.7 Range("A2").Select Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal 'Start Deleting duplicates for the WBSE Details worksheet to have only one 'End Deleting duplicates for the WBSE Details worksheet to have only one |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need program logic to sort fieldA & delete duplicates
Try something like the following.
Sub AAA() Dim LastRow As Long Dim RowNdx As Long Dim StartRow As Long Dim WS As Worksheet Dim RR As Range Dim ColumnLetter As String StartRow = 1 '<<< CHANGE AS REQUIRED ColumnLetter = "A" '<<< CHANGE AS REQUIRED Set WS = ActiveSheet With WS LastRow = .Cells(.Rows.Count, ColumnLetter).End(xlUp).Row For RowNdx = LastRow To StartRow + 1 Step -1 Set RR = .Range(.Cells(StartRow, ColumnLetter), _ .Cells(RowNdx - 1, ColumnLetter)) If Application.CountIf(RR, .Cells(RowNdx, ColumnLetter)) < 0 Then .Rows(RowNdx).Delete End If Next RowNdx End With End Sub Change the StartRow value to the row number at which the sorted data starts. Change ColumnLetter to the column which will be examined for duplicates. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Sun, 11 Jan 2009 08:06:01 -0800, Bud wrote: Hello I have a worksheet that I am building data in. I would like to than sort on that data and than delete and duplicates such that the next non dup would appear as the next line. Does someone have some logic for this? FIELD A Field B K-0000002673-970728-NHO9 ASFOA_US_Healthcare3 K-0000002673-970728-NHO9 ASFOA_US_Healthcare3 K-0000002673-970728-NHO9 ASFOA_US_Healthcare3 K-0000002673-970728-NHO9 ASFOA_US_Healthcare3 K-0000002673-970728-NHO9 ASFOA_US_Healthcare3 K-0000002673-970728-NHO9 ASFOA_US_Healthcare3 K-0000002673-970728-NHO9 ASFOA_US_Healthcare3 K-0000002673-970728-NHO9 ASFOA_US_Healthcare3 K-0000002673-970728-NHO9 ASFOA_US_Healthcare3 K-0000002673-970728-NHO9 ASFOA_US_Healthcare3 K-0000002673-970728-NHO9 ASFOA_US_Healthcare3 K-0000002673-970728-NHO9 ASFOA_US_Healthcare3 K-0000002673-970728-NHO9 ASFOA_US_Healthcare3 K-0000007526-000500-BSMG ASFOA_US_Healthcare1 K-0000007526-000705-MAIN ASFOA_US_Healthcare1 K-0000007526-000705-MAIN ASFOA_US_Healthcare1 K-0000007526-000705-MAIN ASFOA_US_Healthcare1 K-0000007526-000705-MINC ASFOA_US_Healthcare1 K-0000007526-000705-MINC ASFOA_US_Healthcare1 K-0000007526-000705-MINC ASFOA_US_Healthcare1 K-0000007526-000705-MSOO ASFOA_US_Healthcare1 K-0000007526-000705-MSOO ASFOA_US_Healthcare1 K-0000007526-000705-MSOO ASFOA_US_Healthcare1 K-0000007526-000705-PROB ASFOA_US_Healthcare1 K-0000007526-000705-PROB ASFOA_US_Healthcare1 K-5677-000701-CONS ASFOA_US_Healthcare1 K-5677-000701-CONS ASFOA_US_Healthcare1 K-5677-000701-CONS ASFOA_US_Healthcare1 K-5677-000701-CONS ASFOA_US_Healthcare1 Here is LOGIC I have to build the data Dim br As Long br = Cells(Rows.Count, "b").End(xlUp).Row Cells.Select ' First Clear all contents of worksheet ActiveSheet.Cells.ClearContents ' Second Add a line that describes the data fields ' Also add the formulas for obtaining the data Cells(1, "a") = "WBSE Number" Cells(1, "b") = "WBSE Description" Cells(1, "c") = "Project Cost Centre" Cells(2, "a") = "='SWIM Time Data'!F2" 'WBSE Number Cells(2, "b") = "='SWIM Time Data'!I2" 'Project name ' Thirdly Auto fill down for the number rows we obtained from the SAP-Simulation Cells(2, "a").AutoFill Destination:=Range(Cells(2, "a"), Cells(br, "a")) Cells(2, "b").AutoFill Destination:=Range(Cells(2, "b"), Cells(br, "b")) Columns("A:A").ColumnWidth = 24.75 Columns("B:B").ColumnWidth = 20.7 Range("A2").Select Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal 'Start Deleting duplicates for the WBSE Details worksheet to have only one 'End Deleting duplicates for the WBSE Details worksheet to have only one |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need program logic to sort fieldA & delete duplicates
Worked perfect! Thanks Chip
"Chip Pearson" wrote: Try something like the following. Sub AAA() Dim LastRow As Long Dim RowNdx As Long Dim StartRow As Long Dim WS As Worksheet Dim RR As Range Dim ColumnLetter As String StartRow = 1 '<<< CHANGE AS REQUIRED ColumnLetter = "A" '<<< CHANGE AS REQUIRED Set WS = ActiveSheet With WS LastRow = .Cells(.Rows.Count, ColumnLetter).End(xlUp).Row For RowNdx = LastRow To StartRow + 1 Step -1 Set RR = .Range(.Cells(StartRow, ColumnLetter), _ .Cells(RowNdx - 1, ColumnLetter)) If Application.CountIf(RR, .Cells(RowNdx, ColumnLetter)) < 0 Then .Rows(RowNdx).Delete End If Next RowNdx End With End Sub Change the StartRow value to the row number at which the sorted data starts. Change ColumnLetter to the column which will be examined for duplicates. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Sun, 11 Jan 2009 08:06:01 -0800, Bud wrote: Hello I have a worksheet that I am building data in. I would like to than sort on that data and than delete and duplicates such that the next non dup would appear as the next line. Does someone have some logic for this? FIELD A Field B K-0000002673-970728-NHO9 ASFOA_US_Healthcare3 K-0000002673-970728-NHO9 ASFOA_US_Healthcare3 K-0000002673-970728-NHO9 ASFOA_US_Healthcare3 K-0000002673-970728-NHO9 ASFOA_US_Healthcare3 K-0000002673-970728-NHO9 ASFOA_US_Healthcare3 K-0000002673-970728-NHO9 ASFOA_US_Healthcare3 K-0000002673-970728-NHO9 ASFOA_US_Healthcare3 K-0000002673-970728-NHO9 ASFOA_US_Healthcare3 K-0000002673-970728-NHO9 ASFOA_US_Healthcare3 K-0000002673-970728-NHO9 ASFOA_US_Healthcare3 K-0000002673-970728-NHO9 ASFOA_US_Healthcare3 K-0000002673-970728-NHO9 ASFOA_US_Healthcare3 K-0000002673-970728-NHO9 ASFOA_US_Healthcare3 K-0000007526-000500-BSMG ASFOA_US_Healthcare1 K-0000007526-000705-MAIN ASFOA_US_Healthcare1 K-0000007526-000705-MAIN ASFOA_US_Healthcare1 K-0000007526-000705-MAIN ASFOA_US_Healthcare1 K-0000007526-000705-MINC ASFOA_US_Healthcare1 K-0000007526-000705-MINC ASFOA_US_Healthcare1 K-0000007526-000705-MINC ASFOA_US_Healthcare1 K-0000007526-000705-MSOO ASFOA_US_Healthcare1 K-0000007526-000705-MSOO ASFOA_US_Healthcare1 K-0000007526-000705-MSOO ASFOA_US_Healthcare1 K-0000007526-000705-PROB ASFOA_US_Healthcare1 K-0000007526-000705-PROB ASFOA_US_Healthcare1 K-5677-000701-CONS ASFOA_US_Healthcare1 K-5677-000701-CONS ASFOA_US_Healthcare1 K-5677-000701-CONS ASFOA_US_Healthcare1 K-5677-000701-CONS ASFOA_US_Healthcare1 Here is LOGIC I have to build the data Dim br As Long br = Cells(Rows.Count, "b").End(xlUp).Row Cells.Select ' First Clear all contents of worksheet ActiveSheet.Cells.ClearContents ' Second Add a line that describes the data fields ' Also add the formulas for obtaining the data Cells(1, "a") = "WBSE Number" Cells(1, "b") = "WBSE Description" Cells(1, "c") = "Project Cost Centre" Cells(2, "a") = "='SWIM Time Data'!F2" 'WBSE Number Cells(2, "b") = "='SWIM Time Data'!I2" 'Project name ' Thirdly Auto fill down for the number rows we obtained from the SAP-Simulation Cells(2, "a").AutoFill Destination:=Range(Cells(2, "a"), Cells(br, "a")) Cells(2, "b").AutoFill Destination:=Range(Cells(2, "b"), Cells(br, "b")) Columns("A:A").ColumnWidth = 24.75 Columns("B:B").ColumnWidth = 20.7 Range("A2").Select Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal 'Start Deleting duplicates for the WBSE Details worksheet to have only one 'End Deleting duplicates for the WBSE Details worksheet to have only one |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Find duplicates, sum column then delete duplicates | Excel Programming | |||
| Remove Duplicates logic | Excel Worksheet Functions | |||
| compiling excel logic into C program | Excel Discussion (Misc queries) | |||
| Sort data then delete duplicates | Excel Programming | |||
Linear Mode
