Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compare 2 columns and align duplicates into same row | Excel Worksheet Functions | |||
Compare multiple rows for duplicated values in individual columns | Excel Worksheet Functions | |||
Align & compare data | Excel Discussion (Misc queries) | |||
Align & Compare row with column | New Users to Excel | |||
Macro to align and compare multiple rows and columns | New Users to Excel |