Home |
Search |
Today's Posts |
#1
|
|||
|
|||
I need a Macro
I have one file with 2 different worksheets. Each worksheet contains a long
list of item id's. Worksheet 1 has 4,883 lines of data in column A. Workshhet 2 has 185 lines of data in column A. I would like a macro to run that will check each column for duplicate item id's and maybe color the cell to identify the item that occur on both worksheets. I am a novice with macros, so any help is greatly appreciated. thanks, Chris |
#2
|
|||
|
|||
Chris,
If you run a macro with loops that big, it will take a huge sum of time. (up to 4883^2 loops) I'd recommend creating a counter column that will retain the original positioning, then sorting on ID, then coloring your cells, then sorting back on the counter. Steve |
#3
|
|||
|
|||
What is a "counter column"?
Chris "stretch" wrote: Chris, If you run a macro with loops that big, it will take a huge sum of time. (up to 4883^2 loops) I'd recommend creating a counter column that will retain the original positioning, then sorting on ID, then coloring your cells, then sorting back on the counter. Steve |
#4
|
|||
|
|||
take cell A1; type counter as a label. In cell A2 type 1. In cell A3
type "=1+A2". Then copy cell A3 to the 4883 row and it will count from 1 to 4883 giving you the proper ordering. That way, once you sort on ID, the counter column will be sorted with it. Once you sort back on counter, your data is in the order it was before you sorted. |
#5
|
|||
|
|||
You could use a macro, but I'd just use an extra column with a bunch of
formulas: In B1 of sheet1: =isnumber(match(a1,sheet2!a:a,0)) in B1 of sheet2: =isnumber(match(a1,sheet1!a:a,0)) And drag each down the range. All of these formulas will return TRUE if there's a match, FALSE if no match. You could even add header rows and do Data|Filter|Autofilter to see just the ones you want. And by using that extra cell, you can use lots of builtin excel features. If you use colors, then it becomes more difficult to work with the data. Lucien wrote: I have one file with 2 different worksheets. Each worksheet contains a long list of item id's. Worksheet 1 has 4,883 lines of data in column A. Workshhet 2 has 185 lines of data in column A. I would like a macro to run that will check each column for duplicate item id's and maybe color the cell to identify the item that occur on both worksheets. I am a novice with macros, so any help is greatly appreciated. thanks, Chris -- Dave Peterson |
#6
|
|||
|
|||
Thanks, it worked great!
Chris "Dave Peterson" wrote: You could use a macro, but I'd just use an extra column with a bunch of formulas: In B1 of sheet1: =isnumber(match(a1,sheet2!a:a,0)) in B1 of sheet2: =isnumber(match(a1,sheet1!a:a,0)) And drag each down the range. All of these formulas will return TRUE if there's a match, FALSE if no match. You could even add header rows and do Data|Filter|Autofilter to see just the ones you want. And by using that extra cell, you can use lots of builtin excel features. If you use colors, then it becomes more difficult to work with the data. Lucien wrote: I have one file with 2 different worksheets. Each worksheet contains a long list of item id's. Worksheet 1 has 4,883 lines of data in column A. Workshhet 2 has 185 lines of data in column A. I would like a macro to run that will check each column for duplicate item id's and maybe color the cell to identify the item that occur on both worksheets. I am a novice with macros, so any help is greatly appreciated. thanks, Chris -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Make Alignment options under format cells available as shortcut | Excel Discussion (Misc queries) | |||
Help with macro looping and color query function | Excel Discussion (Misc queries) | |||
Playing a macro from another workbook | Excel Discussion (Misc queries) | |||
Date macro | Excel Discussion (Misc queries) | |||
Macro and If Statement | Excel Discussion (Misc queries) |