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

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
>

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
> >

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
> >

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>> >