Transpose and Removing Duplicates at same time
Closed
calflamesfann785
Blocked Profile - Jan 30, 2013 at 04:35 AM
- Posts
- 2
- Registration date
- Saturday November 10, 2012
- Status
- Member
- Last seen
- January 29, 2013
Blocked Profile - Jan 30, 2013 at 04:35 AM
Related:
- Transpose and Removing Duplicates at same time
- Excel duplicate rows n times ✓ - Forum - Excel
- Copy/paste a range 'n' times. ✓ - Forum - Excel
- Repeat row x times - Forum - Excel
- Copy row and insert n times ✓ - Forum - Office Software
- Duplicate rows multiple times based on cell value - Forum - Excel
2 replies
venkat1926
Jan 30, 2013 at 04:00 AM
- Posts
- 1864
- Registration date
- Sunday June 14, 2009
- Status
- Contributor
- Last seen
- August 7, 2021
Jan 30, 2013 at 04:00 AM
try this macro
Sub test() Dim id As Range, cid As Range, result As Range Dim idfind As Range Dim j As Integer, k As Integer Dim miles As Long Range("a1").CurrentRegion.Sort key1:=Range("A1"), Header:=xlYes Set id = Range(Range("A1"), Range("a1").End(xlDown)) Set result = Range("A1").End(xlDown).Offset(5, 0) MsgBox result.Address id.AdvancedFilter xlFilterCopy, , result, True Set result = Range(result.Offset(1, 0), result.End(xlDown)) For Each cid In result k = WorksheetFunction.CountIf(id, cid) Set idfind = id.Find(what:=cid, lookat:=xlWhole) For j = 1 To k miles = idfind.Offset(j - 1, 0).Offset(0, 1) Cells(cid.Row, Columns.Count).End(xlToLeft).Offset(0, 1) = miles Next j Next cid End Sub