Bill of Materials Comparison Macro

I am working on a macro that will compare multiple Bill of Materials for work.
I have included a small section of my spreadsheet after I have performed several operations on it already and this is where I get stuck on a decent way to proceed.

Background:
Each usage starts as a separate sheet with its corresponding BOM.
Copy each BOM onto the same (Master) Worksheet.
Sort by Part Number (ascending), UPC (Ascending), and Usage (Ascending)

I need to compare each line to ensure that the Part Number, UPC, FNA, and QTY are the same. If they are I want to condense the line up to have x marks in the line for the first occurrence of that Part/UPC/FNA/QTY combination.

I am attempting to rewrite this macro because it was only capable of comparing 10 usages in the beginning. I am trying to make it variable so that it can compare any number of usages. I currently have it set to determine how many usages there are by performing a count of the worksheets contained in the workbook.

Any help would be greatly appreciated.
If need be, I can provide the code that was used to get this far, and I can also provide the code for how it used to be compared and condensed.

I Have attached a before and after example of what I am looking at and what I am trying to do.
Writing this in Excel 2003 BTW.

Thanks
Rick

Thanks for the Code, but I have a few issues with it.
I went ahead and tired it, and ran into a few errors.

The third part in my list, the usage on it is only #'s 19,20,23 and 24.
However when it copied the row over it copied "x" into all of the columns.

Some of my parts have the same number, and the same qty, but different UPC and FNA. These need to have seperate rows to indicate that the same part is used in more than one place in the process. I think this is what you are getting at with the comment about usage 3 and usage 5. I need to make sure I catch these parts in multiple locations.

Also, I don't see an easy way to make this work for a variable number of usage columns. That may be becuase I am new to coding VBA, or it may be because I haven't explored the use of arrays enough yet.

What if I were to copy the usage column to column A and the numbered usage columns to columns B to however many I have. Then I can count the number of uniqe parts, count the amount of each duplicate part number and loop through each using offsets to copy the x up to the appropriate row and column. Then I can just format the sheet back to what it is supposed to look like.

This solution, may not be quite as efficient, but I think it should work.

Thanks for the help. I will take a closer look at this code too and see If I can make it work for my purposes. I need to do some research so I understand how your code works before I try to change it.

Rick

Here is the code I used to get this far. The original code only allowed for the comparison of 10 assemblies. This code (updated by me) allowes for variable amounts of assemblies.

Please Login or Register  to view this content.