I'm not sure what constitutes a "repetitive record".
Since I would have thought that the last two rows of your
sample data repeat, yet you included all of them in the
sample output...
So, based on what little you've given us...
I put together this macro:
'----------------------------------------------------
Set Dict_Grp = CreateObject("Scripting.Dictionary")
Dict_Grp.RemoveAll
Set Dict_Sngl = CreateObject("Scripting.Dictionary")
Dict_Sngl.RemoveAll
'----------------------------------------------------
Sheets("Sheet1").Select
nRows =
Application.WorksheetFunction.CountA(Sheets("Sheet1").Range("A1:A6500"))
'Collect all Data and store in Dictionaries
For R = 1 To nRows
nCols =
Application.WorksheetFunction.CountA(Sheets("Sheet1").Range(Cells(R, 1),
Cells(R, 200)))
For C = 3 To nCols
Dat = Cells(R, "A").Value & ":" & Cells(R, "B").Value & ":" &
Trim(Cells(R, C).Value)
If (Not Dict_Sngl.exists(Dat)) Then
Dict_Sngl.Add Dat, Cells(R, "A").Value & ":" & Cells(R,
"B").Value
If (Not Dict_Grp.exists(Cells(R, "A").Value & ":" & Cells(R,
"B").Value)) Then
Dict_Grp.Add Cells(R, "A").Value & ":" & Cells(R,
"B").Value, Trim(Cells(R, C).Value)
Else
Dict_Grp.Item(Cells(R, "A").Value & ":" & Cells(R,
"B").Value) = _
Dict_Grp.Item(Cells(R, "A").Value & ":" & Cells(R,
"B").Value) & ":" & Trim(Cells(R, C).Value)
End If
End If
Next C
Next R
' Report Data From Dictionaries
Sheets("Sheet2").Select
Sheets("Sheet2").Cells.ClearContents
R = 1
DictKeys = Dict_Grp.keys
For inx = 0 To UBound(DictKeys)
R = R + 1
DatArray = Split(DictKeys(inx), ":")
Cells(R, 1) = DatArray(0)
Cells(R, 2) = DatArray(1)
DatArray = Split(Dict_Grp.Item(DictKeys(inx)), ":")
For C = 0 To UBound(DatArray)
Cells(R, C + 3) = DatArray(C)
Next C
Next inx
End Sub
you'll have to modify it to fit what you really need...
Paul
________________________________
From: vikalp <dostvik...@gmail.com>
To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
Sent: Fri, January 28, 2011 3:01:25 AM
Subject: $$Excel-Macros$$ removing multiple data and converting column into row
hellow everybody,
kindly help me with the problem i am facing i want to remove
repititive record from folloeing data and get useful data for the
perpose of mailmerge example is given bellow
example data I have
a b c d
1 x 20 35
1 x 25 35
1 x 26 145
1 x 35 2568
2 y 24 85
2 y 25 7431
2 y 620 556
3 z 25 451
3 z 25 451
data format I require
a d c1 d1 c2 d2 c3 d3 c4 d4
1 x 20 35 25 35 26 145 35 2568
2 y 24 85 25 7431 620 556
3 z 25 451 25 451
knly help me please
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
