Hi Patrick,

Followup to the below, I have installed Scripting Run Time Library, but I
still receive the error messages and Excel does not recognize
Scripting.Dictionary as a keyword(s). Looked in add-ins and do not see any
new ones, so I am not sure what to do next.

Thanks,

"Patrick Molloy" wrote:

In th eIDE set a reference to Microsoft Scriptin Runtime. We shall use th
eScripting Dictionary as a collection to gather the data. The advantage over
a collection for this is that we cam use the EXISTS method to test for items
in a column.

Process: colect the items in each column into their own collections, add the
collections and add add the items into a unique collection of all items. we
can then test for each column if an item in the full list is in th ecolumn,
and puty it there if it is.

Option Explicit
' set ref to Microsoft Scripting Runtime

Sub resetcolumns()
Dim cALLItems As New Scripting.Dictionary
Dim cAllColumns As New Scripting.Dictionary
Dim cColItems As Scripting.Dictionary
Dim col As Long
Dim index As Long
Dim item As String
Dim ws As Worksheet
Const MAXCOL As Long = 4

For col = 1 To MAXCOL
Set cColItems = New Scripting.Dictionary

For index = 1 To Cells(65000, col).End(xlUp).Row
item = Cells(index, col).Value
If Not cColItems.Exists(item) Then
cColItems.Add item, item
End If
If Not cALLItems.Exists(item) Then
cALLItems.Add item, item
End If
Next
cAllColumns.Add "group" & col, cColItems
Next

Set ws = Worksheets.Add

With ws
For col = 1 To MAXCOL
Set cColItems = cAllColumns("group" & col)
For index = 1 To cALLItems.Count
item = cALLItems.Items(index - 1)
If cColItems.Exists(item) Then
.Cells(index, col).Value = item
End If
Next
Next

End With
End Sub


workbook: columncollections.xls available

Patrick Molloy
Microsoft Excel MVP




"Manav Ram via OfficeKB.com" wrote:

Hello,
Thanks in advance to those who would spend some time of their's to help me.
I am looking for a macro which can align and compare multiple columns, each
of several rows of data. Some rows(text in the cells) of each column are
identical with the next, which mean while "Apples" is present in all
columns,"bananas" in columns 2, 6, 13 and "oranges" is present only in
column 1,5, 7 and 11.....Then the formatted list should show "apples"
aligned A2, B2, C2, D2.......X2 so on,"bananas" in B4, F4,M4 while
"Oranges" will be shown only in A7, E7, G7, K7

I would highly appreciate any help......thanks again in advance

My email: culaterz2003[AT]yahoo.com

--
Message posted via http://www.officekb.com