ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Need program logic to sort fieldA & delete duplicates (https://www.excelbanter.com/excel-programming/422295-need-program-logic-sort-fielda-delete-duplicates.html)

Bud
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

Chip Pearson
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

Bud
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


All times are GMT +1. The time now is 01:36 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com