| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
CopyPasteSortCountMacro
Here is my code.
Columns("I:J").Select Selection.Copy Sheets.Add ActiveSheet.Paste Range("A1").Select Application.CutCopyMode = False Range("A1:B100").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:= _ xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal And this is what it does now. Copy Column I:J Open a new worksheet Paste Column I:J into Column A:B of new worksheet. Sort by/Column A/Ascending I want to add to the code I have above so that it takes these additional steps below. Put a 1 in every cell in Column C if Column A:B have contents inside it. (What is in Column A is associated with Column B). Review every row in Column A:B from A1:B1 to A100:B100. When the same contents appear two, three, or more times in any row in Column A:B, I want that row to be deleted and the total/tally for the 1st time those contents appeared in a row in Column A:B to be increased by 1 in Column C. In other words each time an exact duplicate is found in A:B that row gets deleted and the total/tally being kept in Column C for the first time those contents appeared gets increased by 1 The contents are typically 10 characters long and a mixture of numbers and letters which are sometimes connected by dashes (i.e) CF-88VBXWQ. There is no pattern to the letters, numbers, or dashes. Is this possible? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
CopyPasteSortCountMacro
Sub CopySheet()
Set OldSht = ActiveSheet Sheets.Add Set NewSht = ActiveSheet OldSht.Columns("I:J").Copy _ Destination:=NewSht.Columns("A") Application.CutCopyMode = False With NewSht Lastrow = .Range("A" & Rows.Count).End(xlUp).Row Set SortRange = .Rows("1:" & Lastrow) SortRange.Sort _ Key1:=.Range("A1"), _ Order1:=xlAscending, _ Header:=xlYes RowCount = 1 Do While .Range("A" & RowCount) < "" If .Range("C" & RowCount) = "" Then .Range("C" & RowCount) = 1 End If If .Range("A" & RowCount) = .Range("A" & (RowCount + 1)) And _ .Range("B" & RowCount) = .Range("B" & (RowCount + 1)) Then .Range("C" & RowCount) = .Range("C" & RowCount) + 1 Rows(RowCount + 1).Delete Else RowCount = RowCount + 1 End If Loop End With End Sub "MCheru" wrote: Here is my code. Columns("I:J").Select Selection.Copy Sheets.Add ActiveSheet.Paste Range("A1").Select Application.CutCopyMode = False Range("A1:B100").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:= _ xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal And this is what it does now. Copy Column I:J Open a new worksheet Paste Column I:J into Column A:B of new worksheet. Sort by/Column A/Ascending I want to add to the code I have above so that it takes these additional steps below. Put a 1 in every cell in Column C if Column A:B have contents inside it. (What is in Column A is associated with Column B). Review every row in Column A:B from A1:B1 to A100:B100. When the same contents appear two, three, or more times in any row in Column A:B, I want that row to be deleted and the total/tally for the 1st time those contents appeared in a row in Column A:B to be increased by 1 in Column C. In other words each time an exact duplicate is found in A:B that row gets deleted and the total/tally being kept in Column C for the first time those contents appeared gets increased by 1 The contents are typically 10 characters long and a mixture of numbers and letters which are sometimes connected by dashes (i.e) CF-88VBXWQ. There is no pattern to the letters, numbers, or dashes. Is this possible? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
CopyPasteSortCountMacro
Thank you. This is great.
"Joel" wrote: Sub CopySheet() Set OldSht = ActiveSheet Sheets.Add Set NewSht = ActiveSheet OldSht.Columns("I:J").Copy _ Destination:=NewSht.Columns("A") Application.CutCopyMode = False With NewSht Lastrow = .Range("A" & Rows.Count).End(xlUp).Row Set SortRange = .Rows("1:" & Lastrow) SortRange.Sort _ Key1:=.Range("A1"), _ Order1:=xlAscending, _ Header:=xlYes RowCount = 1 Do While .Range("A" & RowCount) < "" If .Range("C" & RowCount) = "" Then .Range("C" & RowCount) = 1 End If If .Range("A" & RowCount) = .Range("A" & (RowCount + 1)) And _ .Range("B" & RowCount) = .Range("B" & (RowCount + 1)) Then .Range("C" & RowCount) = .Range("C" & RowCount) + 1 Rows(RowCount + 1).Delete Else RowCount = RowCount + 1 End If Loop End With End Sub "MCheru" wrote: Here is my code. Columns("I:J").Select Selection.Copy Sheets.Add ActiveSheet.Paste Range("A1").Select Application.CutCopyMode = False Range("A1:B100").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:= _ xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal And this is what it does now. Copy Column I:J Open a new worksheet Paste Column I:J into Column A:B of new worksheet. Sort by/Column A/Ascending I want to add to the code I have above so that it takes these additional steps below. Put a 1 in every cell in Column C if Column A:B have contents inside it. (What is in Column A is associated with Column B). Review every row in Column A:B from A1:B1 to A100:B100. When the same contents appear two, three, or more times in any row in Column A:B, I want that row to be deleted and the total/tally for the 1st time those contents appeared in a row in Column A:B to be increased by 1 in Column C. In other words each time an exact duplicate is found in A:B that row gets deleted and the total/tally being kept in Column C for the first time those contents appeared gets increased by 1 The contents are typically 10 characters long and a mixture of numbers and letters which are sometimes connected by dashes (i.e) CF-88VBXWQ. There is no pattern to the letters, numbers, or dashes. Is this possible? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
CopyPasteSortCountMacro
For the code to weork properly you need to sort on columns A and B. Try this
change from SortRange.Sort _ Key1:=.Range("A1"), _ Order1:=xlAscending, _ Header:=xlYes to SortRange.Sort _ Key1:=.Range("A1"), _ Order1:=xlAscending, _ Key2:=.Range("B1"), _ Order2:=xlAscending, _ Header:=xlYes "MCheru" wrote: Thank you. This is great. "Joel" wrote: Sub CopySheet() Set OldSht = ActiveSheet Sheets.Add Set NewSht = ActiveSheet OldSht.Columns("I:J").Copy _ Destination:=NewSht.Columns("A") Application.CutCopyMode = False With NewSht Lastrow = .Range("A" & Rows.Count).End(xlUp).Row Set SortRange = .Rows("1:" & Lastrow) SortRange.Sort _ Key1:=.Range("A1"), _ Order1:=xlAscending, _ Header:=xlYes RowCount = 1 Do While .Range("A" & RowCount) < "" If .Range("C" & RowCount) = "" Then .Range("C" & RowCount) = 1 End If If .Range("A" & RowCount) = .Range("A" & (RowCount + 1)) And _ .Range("B" & RowCount) = .Range("B" & (RowCount + 1)) Then .Range("C" & RowCount) = .Range("C" & RowCount) + 1 Rows(RowCount + 1).Delete Else RowCount = RowCount + 1 End If Loop End With End Sub "MCheru" wrote: Here is my code. Columns("I:J").Select Selection.Copy Sheets.Add ActiveSheet.Paste Range("A1").Select Application.CutCopyMode = False Range("A1:B100").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:= _ xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal And this is what it does now. Copy Column I:J Open a new worksheet Paste Column I:J into Column A:B of new worksheet. Sort by/Column A/Ascending I want to add to the code I have above so that it takes these additional steps below. Put a 1 in every cell in Column C if Column A:B have contents inside it. (What is in Column A is associated with Column B). Review every row in Column A:B from A1:B1 to A100:B100. When the same contents appear two, three, or more times in any row in Column A:B, I want that row to be deleted and the total/tally for the 1st time those contents appeared in a row in Column A:B to be increased by 1 in Column C. In other words each time an exact duplicate is found in A:B that row gets deleted and the total/tally being kept in Column C for the first time those contents appeared gets increased by 1 The contents are typically 10 characters long and a mixture of numbers and letters which are sometimes connected by dashes (i.e) CF-88VBXWQ. There is no pattern to the letters, numbers, or dashes. Is this possible? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
