#1   Report Post  
Lucien
 
Posts: n/a
Default 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   Report Post  
stretch
 
Posts: n/a
Default

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   Report Post  
Lucien
 
Posts: n/a
Default

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   Report Post  
stretch
 
Posts: n/a
Default

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   Report Post  
Dave Peterson
 
Posts: n/a
Default

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   Report Post  
Lucien
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Make Alignment options under format cells available as shortcut dforrest Excel Discussion (Misc queries) 1 July 14th 05 10:58 PM
Help with macro looping and color query function kevinm Excel Discussion (Misc queries) 10 May 26th 05 01:25 AM
Playing a macro from another workbook Jim Excel Discussion (Misc queries) 1 February 23rd 05 11:12 PM
Date macro Hiking Excel Discussion (Misc queries) 9 February 3rd 05 01:40 AM
Macro and If Statement SATB Excel Discussion (Misc queries) 2 December 3rd 04 05:46 PM


All times are GMT +1. The time now is 09:49 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"