Macro to align & compare multiple columns with several rows
 

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

Macro to align & compare multiple columns with several rows
 

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

Macro to align & compare multiple columns with several rows
 

Hi Patrick,

Excuse my ignorance. I am using V 2003 Professional and it does not
recognize Scripting.Dictionary as key word(s). It appears that some type of
an array is being set up for comparison purposes. I get a complie error, if I
attept to run the code, "User-defined type not defined." Neither Help F1
local or Help on line yielded much about this object. Can you point me in the
right direction to obtain information about the use of the
"Scripting.Dictionary", either a book or on line reference. I think a book
would be the best place to start. Thank you for your help.


"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

Macro to align & compare multiple columns with several rows
 

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

Macro to align & compare multiple columns with several rows
 

did you set a reference to this library unter Tools/references ?
Under Debug click Compile. It will fail if you have not referenced the dll.


"David" wrote in message
...
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