Multi-Argument Data Comparison using no formulas - Is this possible?

B

byoung70104

New Member
Joined
Jul 8, 2013
Messages
3
Hello,
I am working on a macro to compare two sets of data and return results based on what is found. I'm relatively savy with <ACRONYM title="visual basic for applications">VBA</ACRONYM> however am having a hard time with this comparison. I'm using 2007, however I have to write the code to be 2003 compatible.

I've been able to set it up to make comparisons between the sheets using concatenation and Vlookups and it works, however my data will be growing to thousands of lines and there are multiple levels of comparison taking place which means that it takes a very long time to complete.

Here's an idea of what it does...doing my best to make an example because the actual file contains confidential customer information.

Sheet 1
The combination Column I added for Concatenating cells together to use as comparison
[TABLE="class: cms_table_grid"]
<TBODY>[TR]
[TD]Combination</SPAN>
[/TD]
[TD]DATE</SPAN>
[/TD]
[TD]PART</SPAN>
[/TD]
[TD]QUANTITY</SPAN>
[/TD]
[TD]ACCOUNT</SPAN>
[/TD]
[TD]TYPE</SPAN>
[/TD]
[/TR]
[TR]
[TD]177664:100</SPAN>
[/TD]
[TD]9/17/2013</SPAN>
[/TD]
[TD]177664</SPAN>
[/TD]
[TD]-1</SPAN>
[/TD]
[TD]100</SPAN>
[/TD]
[TD]WIDGET_3</SPAN>
[/TD]
[/TR]
[TR]
[TD]932654:114</SPAN>
[/TD]
[TD]9/17/2013</SPAN>
[/TD]
[TD]932654</SPAN>
[/TD]
[TD]-1</SPAN>
[/TD]
[TD]114</SPAN>
[/TD]
[TD]WIDGET_2</SPAN>
[/TD]
[/TR]
[TR]
[TD]123456:399</SPAN>
[/TD]
[TD]9/17/2013</SPAN>
[/TD]
[TD]123456</SPAN>
[/TD]
[TD]-1</SPAN>
[/TD]
[TD]399</SPAN>
[/TD]
[TD]WIDGET_1</SPAN>
[/TD]
[/TR]
[TR]
[TD]188000:399</SPAN>
[/TD]
[TD]9/17/2013</SPAN>
[/TD]
[TD]188000</SPAN>
[/TD]
[TD]-2</SPAN>
[/TD]
[TD]399</SPAN>
[/TD]
[TD]WIDGET_2</SPAN>
[/TD]
[/TR]
[TR]
[TD]188000:406</SPAN>
[/TD]
[TD]9/17/2013</SPAN>
[/TD]
[TD]188000</SPAN>
[/TD]
[TD]-1</SPAN>
[/TD]
[TD]406</SPAN>
[/TD]
[TD]WIDGET_2</SPAN>
[/TD]
[/TR]
[TR]
[TD]188000:412</SPAN>
[/TD]
[TD]9/17/2013</SPAN>
[/TD]
[TD]188000</SPAN>
[/TD]
[TD]-1</SPAN>
[/TD]
[TD]412</SPAN>
[/TD]
[TD]WIDGET_2</SPAN>
[/TD]
[/TR]
[TR]
[TD]932654:465</SPAN>
[/TD]
[TD]9/17/2013</SPAN>
[/TD]
[TD]932654</SPAN>
[/TD]
[TD]-1</SPAN>
[/TD]
[TD]465</SPAN>
[/TD]
[TD]WIDGET_2</SPAN>
[/TD]
[/TR]
[TR]
[TD]123456:801</SPAN>
[/TD]
[TD]9/17/2013</SPAN>
[/TD]
[TD]123456</SPAN>
[/TD]
[TD]-4</SPAN>
[/TD]
[TD]801</SPAN>
[/TD]
[TD]WIDGET_1</SPAN>
[/TD]
[/TR]
[TR]
[TD]932654:802</SPAN>
[/TD]
[TD]9/17/2013</SPAN>
[/TD]
[TD]932654</SPAN>
[/TD]
[TD]-1</SPAN>
[/TD]
[TD]802</SPAN>
[/TD]
[TD]WIDGET_2</SPAN>
[/TD]
[/TR]
[TR]
[TD]932654:803</SPAN>
[/TD]
[TD]9/17/2013</SPAN>
[/TD]
[TD]932654</SPAN>
[/TD]
[TD]-2</SPAN>
[/TD]
[TD]803</SPAN>
[/TD]
[TD]WIDGET_2</SPAN>
[/TD]
[/TR]
[TR]
[TD]123456:804</SPAN>
[/TD]
[TD]9/17/2013</SPAN>
[/TD]
[TD]123456</SPAN>
[/TD]
[TD]-1</SPAN>
[/TD]
[TD]804</SPAN>
[/TD]
[TD]WIDGET_1</SPAN>
[/TD]
[/TR]
[TR]
[TD]932654:806</SPAN>
[/TD]
[TD]9/17/2013</SPAN>
[/TD]
[TD]932654</SPAN>
[/TD]
[TD]-1</SPAN>
[/TD]
[TD]806</SPAN>
[/TD]
[TD]WIDGET_2</SPAN>
[/TD]
[/TR]
[TR]
[TD]188000:833</SPAN>
[/TD]
[TD]9/17/2013</SPAN>
[/TD]
[TD]188000</SPAN>
[/TD]
[TD]-3</SPAN>
[/TD]
[TD]833</SPAN>
[/TD]
[TD]WIDGET_2</SPAN>
[/TD]
[/TR]
[TR]
[TD]190111:855</SPAN>
[/TD]
[TD]9/17/2013</SPAN>
[/TD]
[TD]190111</SPAN>
[/TD]
[TD]-3</SPAN>
[/TD]
[TD]855</SPAN>
[/TD]
[TD]WIDGET_1</SPAN>
[/TD]
[/TR]
[TR]
[TD]932654:855</SPAN>
[/TD]
[TD]9/17/2013</SPAN>
[/TD]
[TD]932654</SPAN>
[/TD]
[TD]-1</SPAN>
[/TD]
[TD]855</SPAN>
[/TD]
[TD]WIDGET_2</SPAN>
[/TD]
[/TR]
[TR]
[TD]123456:875</SPAN>
[/TD]
[TD]9/17/2013</SPAN>
[/TD]
[TD]123456</SPAN>
[/TD]
[TD]-1</SPAN>
[/TD]
[TD]875</SPAN>
[/TD]
[TD]WIDGET_1</SPAN>
[/TD]
[/TR]
[TR]
[TD]188000:875</SPAN>
[/TD]
[TD]9/17/2013</SPAN>
[/TD]
[TD]188000</SPAN>
[/TD]
[TD]-1</SPAN>
[/TD]
[TD]875</SPAN>
[/TD]
[TD]WIDGET_2</SPAN>
[/TD]
[/TR]
[TR]
[TD]123456:883</SPAN>
[/TD]
[TD]9/17/2013</SPAN>
[/TD]
[TD]123456</SPAN>
[/TD]
[TD]-1</SPAN>
[/TD]
[TD]883</SPAN>
[/TD]
[TD]WIDGET_1</SPAN>
[/TD]
[/TR]
[TR]
[TD]190111:891</SPAN>
[/TD]
[TD]9/17/2013</SPAN>
[/TD]
[TD]190111</SPAN>
[/TD]
[TD]-1</SPAN>
[/TD]
[TD]891</SPAN>
[/TD]
[TD]WIDGET_1</SPAN>
[/TD]
[/TR]
[TR]
[TD]123456:901</SPAN>
[/TD]
[TD]9/17/2013</SPAN>
[/TD]
[TD]123456</SPAN>
[/TD]
[TD]-1</SPAN>
[/TD]
[TD]901</SPAN>
[/TD]
[TD]WIDGET_1</SPAN>
[/TD]
[/TR]
[TR]
[TD]188000:901</SPAN>
[/TD]
[TD]9/17/2013</SPAN>
[/TD]
[TD]188000</SPAN>
[/TD]
[TD]-1</SPAN>
[/TD]
[TD]901</SPAN>
[/TD]
[TD]WIDGET_2</SPAN>
[/TD]
[/TR]
[TR]
[TD]188000:902</SPAN>
[/TD]
[TD]9/17/2013</SPAN>
[/TD]
[TD]188000</SPAN>
[/TD]
[TD]-1</SPAN>
[/TD]
[TD]902</SPAN>
[/TD]
[TD]WIDGET_2</SPAN>
[/TD]
[/TR]
[TR]
[TD]190111:903</SPAN>
[/TD]
[TD]9/17/2013</SPAN>
[/TD]
[TD]190111</SPAN>
[/TD]
[TD]-1</SPAN>
[/TD]
[TD]903</SPAN>
[/TD]
[TD]WIDGET_1</SPAN>
[/TD]
[/TR]
[TR]
[TD]190111:906</SPAN>
[/TD]
[TD]9/17/2013</SPAN>
[/TD]
[TD]190111</SPAN>
[/TD]
[TD]-1</SPAN>
[/TD]
[TD]906</SPAN>
[/TD]
[TD]WIDGET_1</SPAN>
[/TD]
[/TR]
[TR]
[TD]932654:932</SPAN>
[/TD]
[TD]9/17/2013</SPAN>
[/TD]
[TD]932654</SPAN>
[/TD]
[TD]-1</SPAN>
[/TD]
[TD]932</SPAN>
[/TD]
[TD]WIDGET_2</SPAN>
[/TD]
[/TR]
[TR]
[TD]123456:944</SPAN>
[/TD]
[TD]9/17/2013</SPAN>
[/TD]
[TD]123456</SPAN>
[/TD]
[TD]-1</SPAN>
[/TD]
[TD]944</SPAN>
[/TD]
[TD]WIDGET_1</SPAN>
[/TD]
[/TR]
[TR]
[TD]932654:944</SPAN>
[/TD]
[TD]9/17/2013</SPAN>
[/TD]
[TD]932654</SPAN>
[/TD]
[TD]-2</SPAN>
[/TD]
[TD]944</SPAN>
[/TD]
[TD]WIDGET_2</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]




Sheet 2

I add Columns E-J for the comparison

[TABLE="class: cms_table_grid, align: center"]
<TBODY>[TR]
[TD]Date</SPAN>
[/TD]
[TD]Account Number</SPAN>
[/TD]
[TD]Type</SPAN>
[/TD]
[TD]QTY</SPAN>
[/TD]
[TD]New</SPAN>
[/TD]
[TD]Reman</SPAN>
[/TD]
[TD]COMBINED</SPAN>
[/TD]
[TD]Sheet 1</SPAN>
[/TD]
[TD]Result</SPAN>
[/TD]
[TD]Transacted</SPAN>
[/TD]
[/TR]
[TR]
[TD]9/17/2013</SPAN>
[/TD]
[TD]100</SPAN>
[/TD]
[TD]WIDGET_1</SPAN>
[/TD]
[TD]-1</SPAN>
[/TD]
[TD]123456</SPAN>
[/TD]
[TD]190111</SPAN>
[/TD]
[TD]100:-1</SPAN>
[/TD]
[TD]#N/A</SPAN>
[/TD]
[TD]Incorrect</SPAN>
[/TD]
[TD]177664</SPAN>
[/TD]
[/TR]
[TR]
[TD]9/17/2013</SPAN>
[/TD]
[TD]114</SPAN>
[/TD]
[TD]WIDGET_2</SPAN>
[/TD]
[TD]-1</SPAN>
[/TD]
[TD]932654</SPAN>
[/TD]
[TD]188000</SPAN>
[/TD]
[TD]932654:114</SPAN>
[/TD]
[TD]932654:114</SPAN>
[/TD]
[TD]Correct</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9/17/2013</SPAN>
[/TD]
[TD]399</SPAN>
[/TD]
[TD]WIDGET_1</SPAN>
[/TD]
[TD]-1</SPAN>
[/TD]
[TD]123456</SPAN>
[/TD]
[TD]190111</SPAN>
[/TD]
[TD]123456:399</SPAN>
[/TD]
[TD]123456:399</SPAN>
[/TD]
[TD]Correct</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9/17/2013</SPAN>
[/TD]
[TD]399</SPAN>
[/TD]
[TD]WIDGET_2</SPAN>
[/TD]
[TD]-2</SPAN>
[/TD]
[TD]932654</SPAN>
[/TD]
[TD]188000</SPAN>
[/TD]
[TD]932654:399</SPAN>
[/TD]
[TD]#N/A</SPAN>
[/TD]
[TD]REMAN</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9/17/2013</SPAN>
[/TD]
[TD]406</SPAN>
[/TD]
[TD]WIDGET_1</SPAN>
[/TD]
[TD]-1</SPAN>
[/TD]
[TD]123456</SPAN>
[/TD]
[TD]190111</SPAN>
[/TD]
[TD]123456:406</SPAN>
[/TD]
[TD]#N/A</SPAN>
[/TD]
[TD]Incorrect</SPAN>
[/TD]
[TD]188000</SPAN>
[/TD]
[/TR]
[TR]
[TD]9/17/2013</SPAN>
[/TD]
[TD]412</SPAN>
[/TD]
[TD]WIDGET_2</SPAN>
[/TD]
[TD]-1</SPAN>
[/TD]
[TD]188000</SPAN>
[/TD]
[TD]932654</SPAN>
[/TD]
[TD]188000:412</SPAN>
[/TD]
[TD]188000:412</SPAN>
[/TD]
[TD]Correct</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9/17/2013</SPAN>
[/TD]
[TD]465</SPAN>
[/TD]
[TD]WIDGET_2</SPAN>
[/TD]
[TD]-1</SPAN>
[/TD]
[TD]932654</SPAN>
[/TD]
[TD]188000</SPAN>
[/TD]
[TD]932654:465</SPAN>
[/TD]
[TD]932654:465</SPAN>
[/TD]
[TD]Correct</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9/17/2013</SPAN>
[/TD]
[TD]801</SPAN>
[/TD]
[TD]WIDGET_2</SPAN>
[/TD]
[TD]-4</SPAN>
[/TD]
[TD]932654</SPAN>
[/TD]
[TD]188000</SPAN>
[/TD]
[TD]932654:801</SPAN>
[/TD]
[TD]#N/A</SPAN>
[/TD]
[TD]Incorrect</SPAN>
[/TD]
[TD]123456</SPAN>
[/TD]
[/TR]
[TR]
[TD]9/17/2013</SPAN>
[/TD]
[TD]802</SPAN>
[/TD]
[TD]WIDGET_2</SPAN>
[/TD]
[TD]-1</SPAN>
[/TD]
[TD]932654</SPAN>
[/TD]
[TD]188000</SPAN>
[/TD]
[TD]932654:802</SPAN>
[/TD]
[TD]932654:802</SPAN>
[/TD]
[TD]Correct</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9/17/2013</SPAN>
[/TD]
[TD]803</SPAN>
[/TD]
[TD]WIDGET_2</SPAN>
[/TD]
[TD]-1</SPAN>
[/TD]
[TD]932654</SPAN>
[/TD]
[TD]188000</SPAN>
[/TD]
[TD]932654:803</SPAN>
[/TD]
[TD]932654:803</SPAN>
[/TD]
[TD]Quantity</SPAN>
[/TD]
[TD]-2</SPAN>
[/TD]
[/TR]
[TR]
[TD]9/17/2013</SPAN>
[/TD]
[TD]804</SPAN>
[/TD]
[TD]WIDGET_1</SPAN>
[/TD]
[TD]-1</SPAN>
[/TD]
[TD]123456</SPAN>
[/TD]
[TD]190111</SPAN>
[/TD]
[TD]123456:804</SPAN>
[/TD]
[TD]123456:804</SPAN>
[/TD]
[TD]Correct</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9/17/2013</SPAN>
[/TD]
[TD]806</SPAN>
[/TD]
[TD]WIDGET_1</SPAN>
[/TD]
[TD]-3</SPAN>
[/TD]
[TD]123456</SPAN>
[/TD]
[TD]190111</SPAN>
[/TD]
[TD]123456:806</SPAN>
[/TD]
[TD]#N/A</SPAN>
[/TD]
[TD]Incorrect</SPAN>
[/TD]
[TD]932654</SPAN>
[/TD]
[/TR]
[TR]
[TD]9/17/2013</SPAN>
[/TD]
[TD]833</SPAN>
[/TD]
[TD]WIDGET_2</SPAN>
[/TD]
[TD]-3</SPAN>
[/TD]
[TD]188000</SPAN>
[/TD]
[TD]932654</SPAN>
[/TD]
[TD]188000:833</SPAN>
[/TD]
[TD]188000:833</SPAN>
[/TD]
[TD]Correct</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9/17/2013</SPAN>
[/TD]
[TD]855</SPAN>
[/TD]
[TD]WIDGET_1</SPAN>
[/TD]
[TD]-1</SPAN>
[/TD]
[TD]190111</SPAN>
[/TD]
[TD]123456</SPAN>
[/TD]
[TD]190111:855</SPAN>
[/TD]
[TD]190111:855</SPAN>
[/TD]
[TD]Quantity</SPAN>
[/TD]
[TD]-3</SPAN>
[/TD]
[/TR]
[TR]
[TD]9/17/2013</SPAN>
[/TD]
[TD]855</SPAN>
[/TD]
[TD]WIDGET_2</SPAN>
[/TD]
[TD]-1</SPAN>
[/TD]
[TD]932654</SPAN>
[/TD]
[TD]188000</SPAN>
[/TD]
[TD]932654:855</SPAN>
[/TD]
[TD]932654:855</SPAN>
[/TD]
[TD]Correct</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9/17/2013</SPAN>
[/TD]
[TD]875</SPAN>
[/TD]
[TD]WIDGET_1</SPAN>
[/TD]
[TD]-1</SPAN>
[/TD]
[TD]123456</SPAN>
[/TD]
[TD]190111</SPAN>
[/TD]
[TD]123456:875</SPAN>
[/TD]
[TD]123456:875</SPAN>
[/TD]
[TD]Correct</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9/17/2013</SPAN>
[/TD]
[TD]875</SPAN>
[/TD]
[TD]WIDGET_2</SPAN>
[/TD]
[TD]-1</SPAN>
[/TD]
[TD]188000</SPAN>
[/TD]
[TD]932654</SPAN>
[/TD]
[TD]188000:875</SPAN>
[/TD]
[TD]188000:875</SPAN>
[/TD]
[TD]Correct</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9/17/2013</SPAN>
[/TD]
[TD]883</SPAN>
[/TD]
[TD]WIDGET_1</SPAN>
[/TD]
[TD]-1</SPAN>
[/TD]
[TD]123456</SPAN>
[/TD]
[TD]190111</SPAN>
[/TD]
[TD]123456:883</SPAN>
[/TD]
[TD]123456:883</SPAN>
[/TD]
[TD]Correct</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9/17/2013</SPAN>
[/TD]
[TD]891</SPAN>
[/TD]
[TD]WIDGET_2</SPAN>
[/TD]
[TD]-1</SPAN>
[/TD]
[TD]932654</SPAN>
[/TD]
[TD]188000</SPAN>
[/TD]
[TD]932654:891</SPAN>
[/TD]
[TD]#N/A</SPAN>
[/TD]
[TD]Incorrect</SPAN>
[/TD]
[TD]190111</SPAN>
[/TD]
[/TR]
[TR]
[TD]9/17/2013</SPAN>
[/TD]
[TD]901</SPAN>
[/TD]
[TD]WIDGET_1</SPAN>
[/TD]
[TD]-1</SPAN>
[/TD]
[TD]123456</SPAN>
[/TD]
[TD]190111</SPAN>
[/TD]
[TD]123456:901</SPAN>
[/TD]
[TD]123456:901</SPAN>
[/TD]
[TD]Correct</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9/17/2013</SPAN>
[/TD]
[TD]901</SPAN>
[/TD]
[TD]WIDGET_2</SPAN>
[/TD]
[TD]-1</SPAN>
[/TD]
[TD]188000</SPAN>
[/TD]
[TD]932654</SPAN>
[/TD]
[TD]188000:901</SPAN>
[/TD]
[TD]188000:901</SPAN>
[/TD]
[TD]Correct</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9/17/2013</SPAN>
[/TD]
[TD]902</SPAN>
[/TD]
[TD]WIDGET_2</SPAN>
[/TD]
[TD]-2</SPAN>
[/TD]
[TD]188000</SPAN>
[/TD]
[TD]932654</SPAN>
[/TD]
[TD]188000:902</SPAN>
[/TD]
[TD]188000:902</SPAN>
[/TD]
[TD]Quantity</SPAN>
[/TD]
[TD]-1</SPAN>
[/TD]
[/TR]
[TR]
[TD]9/17/2013</SPAN>
[/TD]
[TD]903</SPAN>
[/TD]
[TD]WIDGET_1</SPAN>
[/TD]
[TD]-2</SPAN>
[/TD]
[TD]190111</SPAN>
[/TD]
[TD]123456</SPAN>
[/TD]
[TD]190111:903</SPAN>
[/TD]
[TD]190111:903</SPAN>
[/TD]
[TD]Quantity</SPAN>
[/TD]
[TD]-1</SPAN>
[/TD]
[/TR]
[TR]
[TD]9/17/2013</SPAN>
[/TD]
[TD]906</SPAN>
[/TD]
[TD]WIDGET_1</SPAN>
[/TD]
[TD]-1</SPAN>
[/TD]
[TD]190111</SPAN>
[/TD]
[TD]123456</SPAN>
[/TD]
[TD]190111:906</SPAN>
[/TD]
[TD]190111:906</SPAN>
[/TD]
[TD]Correct</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9/17/2013</SPAN>
[/TD]
[TD]932</SPAN>
[/TD]
[TD]WIDGET_2</SPAN>
[/TD]
[TD]-1</SPAN>
[/TD]
[TD]932654</SPAN>
[/TD]
[TD]188000</SPAN>
[/TD]
[TD]932654:932</SPAN>
[/TD]
[TD]932654:932</SPAN>
[/TD]
[TD]Correct</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9/17/2013</SPAN>
[/TD]
[TD]944</SPAN>
[/TD]
[TD]WIDGET_1</SPAN>
[/TD]
[TD]-2</SPAN>
[/TD]
[TD]123456</SPAN>
[/TD]
[TD]190111</SPAN>
[/TD]
[TD]123456:944</SPAN>
[/TD]
[TD]123456:944</SPAN>
[/TD]
[TD]Correct</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9/17/2013</SPAN>
[/TD]
[TD]944</SPAN>
[/TD]
[TD]WIDGET_2</SPAN>
[/TD]
[TD]-2</SPAN>
[/TD]
[TD]188000</SPAN>
[/TD]
[TD]932654</SPAN>
[/TD]
[TD]188000:944</SPAN>
[/TD]
[TD]#N/A</SPAN>
[/TD]
[TD]REMAN</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9/17/2013</SPAN>
[/TD]
[TD]602</SPAN>
[/TD]
[TD]WIDGET_2</SPAN>
[/TD]
[TD]-2</SPAN>
[/TD]
[TD]188000</SPAN>
[/TD]
[TD]932654</SPAN>
[/TD]
[TD]188000:602</SPAN>
[/TD]
[TD]#N/A</SPAN>
[/TD]
[TD]NOT ISSUED</SPAN>
[/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]



The Macro currentlysets up concatenates the Part #: Account #: Quantity on SHEET1
It does the same in the "COMBINED" column on SHEET2 and then using a VLOOKUP in the "Sheet 1" column it looks to see if there are any matches on SHEET1. If there are, it marks Correct in the result column

It then changes the concatenation on SHEET2 to inclue the Reman Part#: Account#: Quantity. Then vlookup compares that with SHEET1, if there are any matches it shows "REMAN" in the result column

Each step it changes something about the concatenation to create a new argument, check for a match, and then display the result in the Result column.

Here are some of the arguments I have it go through.

[TABLE="class: cms_table"]
<TBODY>[TR]
[TD]First Argument</SPAN>
[/TD]
[/TR]
[TR]
[TD]Check for exact Match, show "Correct" in column I</SPAN>
[/TD]
[/TR]
[TR]
[TD]=CONCATENATE(E2,":",B2,":",D2)</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]


[TABLE="class: cms_table"]
<TBODY>[TR]
[TD]Second Argument</SPAN>
[/TD]
[/TR]
[TR]
[TD]Check for Reman item being used, show "Reman" in Column I</SPAN>
[/TD]
[/TR]
[TR]
[/TR]
[TR]
[TD]~=CONCATENATE(F2,":",B2,":",F2)</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]


[TABLE="class: cms_table"]
<TBODY>[TR]
[TD]Third Argument</SPAN>
[/TD]
[/TR]
[TR]
[TD]Check for Same part number but different quantity, enter quantity in column J</SPAN>
[/TD]
[/TR]
[TR]
[/TR]
[TR]
[TD]~=CONCATENATE(E2,":",B2)</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]



[TABLE="class: cms_table"]
<TBODY>[TR]
[TD]Fourth Argument</SPAN>
[/TD]
[/TR]
[TR]
[TD]Check for Reman with Different Quantity, Show "Reman/QTY" in Column I</SPAN>
[/TD]
[/TR]
[TR]
[/TR]
[TR]
[TD]~=CONCATENATE(F2,":",B2)


[TABLE="class: cms_table"]
<TBODY>[TR]
[TD]Fifth Argument</SPAN>
[/TD]
[/TR]
[TR]
[TD]Check for any item being used, Show Incorrect in Column I and part # of item in J</SPAN>
[/TD]
[/TR]
[TR]
[/TR]
[TR]
[TD]~=CONCATENATE(B2,":",D2)

[TABLE="class: cms_table"]
<TBODY>[TR]
[TD]Sixth Argument</SPAN>
[/TD]
[/TR]
[TR]
[TD]Anything without transaction on Sheet 1 Mark as "NOT ISSUED"</SPAN>
[/TD]
[/TR]
[TR]
[/TR]
</TBODY>[/TABLE]


</SPAN>[/TD]
[/TR]
</TBODY>[/TABLE]

</SPAN>[/TD]
[/TR]
</TBODY>[/TABLE]



Obviously all these formula's are killing my Macro when the data runs into thousands of lines. Is there a way I can write code to do the comparisons without all the formula's?

Hopefully there's enough info here to give you some ideas.

Thank's in advance for your help.

-Ben
 
You must log in or register to reply here.

Similar threads

B
  • Question Question
average based on time
Replies
1
Views
571
baqer
B
earthworm
  • Question Question
Split Data into separate worksheet based on Cell values
Replies
8
Views
605
earthworm
earthworm
A
  • Solved
transpose data int listbox on userform based on two dates or without dates
Replies
2
Views
374
Alaa mg
A
J
  • Question Question
Excel macro for Remove comma's and spaces from a cell and place the data in different cell
Replies
3
Views
1K
JEC
JEC
J
  • Question Question
Need some help with permutation code, convert from one cell to a range
Replies
3
Views
472
johnnyL
J

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top