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

byoung70104 · Sep 30, 2013

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

Combination</SPAN>	DATE</SPAN>	PART</SPAN>	QUANTITY</SPAN>	ACCOUNT</SPAN>	TYPE</SPAN>
177664:100</SPAN>	9/17/2013</SPAN>	177664</SPAN>	-1</SPAN>	100</SPAN>	WIDGET_3</SPAN>
932654:114</SPAN>	9/17/2013</SPAN>	932654</SPAN>	-1</SPAN>	114</SPAN>	WIDGET_2</SPAN>
123456:399</SPAN>	9/17/2013</SPAN>	123456</SPAN>	-1</SPAN>	399</SPAN>	WIDGET_1</SPAN>
188000:399</SPAN>	9/17/2013</SPAN>	188000</SPAN>	-2</SPAN>	399</SPAN>	WIDGET_2</SPAN>
188000:406</SPAN>	9/17/2013</SPAN>	188000</SPAN>	-1</SPAN>	406</SPAN>	WIDGET_2</SPAN>
188000:412</SPAN>	9/17/2013</SPAN>	188000</SPAN>	-1</SPAN>	412</SPAN>	WIDGET_2</SPAN>
932654:465</SPAN>	9/17/2013</SPAN>	932654</SPAN>	-1</SPAN>	465</SPAN>	WIDGET_2</SPAN>
123456:801</SPAN>	9/17/2013</SPAN>	123456</SPAN>	-4</SPAN>	801</SPAN>	WIDGET_1</SPAN>
932654:802</SPAN>	9/17/2013</SPAN>	932654</SPAN>	-1</SPAN>	802</SPAN>	WIDGET_2</SPAN>
932654:803</SPAN>	9/17/2013</SPAN>	932654</SPAN>	-2</SPAN>	803</SPAN>	WIDGET_2</SPAN>
123456:804</SPAN>	9/17/2013</SPAN>	123456</SPAN>	-1</SPAN>	804</SPAN>	WIDGET_1</SPAN>
932654:806</SPAN>	9/17/2013</SPAN>	932654</SPAN>	-1</SPAN>	806</SPAN>	WIDGET_2</SPAN>
188000:833</SPAN>	9/17/2013</SPAN>	188000</SPAN>	-3</SPAN>	833</SPAN>	WIDGET_2</SPAN>
190111:855</SPAN>	9/17/2013</SPAN>	190111</SPAN>	-3</SPAN>	855</SPAN>	WIDGET_1</SPAN>
932654:855</SPAN>	9/17/2013</SPAN>	932654</SPAN>	-1</SPAN>	855</SPAN>	WIDGET_2</SPAN>
123456:875</SPAN>	9/17/2013</SPAN>	123456</SPAN>	-1</SPAN>	875</SPAN>	WIDGET_1</SPAN>
188000:875</SPAN>	9/17/2013</SPAN>	188000</SPAN>	-1</SPAN>	875</SPAN>	WIDGET_2</SPAN>
123456:883</SPAN>	9/17/2013</SPAN>	123456</SPAN>	-1</SPAN>	883</SPAN>	WIDGET_1</SPAN>
190111:891</SPAN>	9/17/2013</SPAN>	190111</SPAN>	-1</SPAN>	891</SPAN>	WIDGET_1</SPAN>
123456:901</SPAN>	9/17/2013</SPAN>	123456</SPAN>	-1</SPAN>	901</SPAN>	WIDGET_1</SPAN>
188000:901</SPAN>	9/17/2013</SPAN>	188000</SPAN>	-1</SPAN>	901</SPAN>	WIDGET_2</SPAN>
188000:902</SPAN>	9/17/2013</SPAN>	188000</SPAN>	-1</SPAN>	902</SPAN>	WIDGET_2</SPAN>
190111:903</SPAN>	9/17/2013</SPAN>	190111</SPAN>	-1</SPAN>	903</SPAN>	WIDGET_1</SPAN>
190111:906</SPAN>	9/17/2013</SPAN>	190111</SPAN>	-1</SPAN>	906</SPAN>	WIDGET_1</SPAN>
932654:932</SPAN>	9/17/2013</SPAN>	932654</SPAN>	-1</SPAN>	932</SPAN>	WIDGET_2</SPAN>
123456:944</SPAN>	9/17/2013</SPAN>	123456</SPAN>	-1</SPAN>	944</SPAN>	WIDGET_1</SPAN>
932654:944</SPAN>	9/17/2013</SPAN>	932654</SPAN>	-2</SPAN>	944</SPAN>	WIDGET_2</SPAN>

Sheet 2

I add Columns E-J for the comparison

Date
Account Number
Type
QTY
New
Reman
COMBINED
Sheet 1
Result
Transacted

9/17/2013
100
WIDGET_1
-1
123456
190111
100:-1
#N/A
Incorrect
177664

9/17/2013
114
WIDGET_2
-1
932654
188000
932654:114
932654:114
Correct

9/17/2013
399
WIDGET_1
-1
123456
190111
123456:399
123456:399
Correct

9/17/2013
399
WIDGET_2
-2
932654
188000
932654:399
#N/A
REMAN

9/17/2013
406
WIDGET_1
-1
123456
190111
123456:406
#N/A
Incorrect
188000

9/17/2013
412
WIDGET_2
-1
188000
932654
188000:412
188000:412
Correct

9/17/2013
465
WIDGET_2
-1
932654
188000
932654:465
932654:465
Correct

9/17/2013
801
WIDGET_2
-4
932654
188000
932654:801
#N/A
Incorrect
123456

9/17/2013
802
WIDGET_2
-1
932654
188000
932654:802
932654:802
Correct

9/17/2013
803
WIDGET_2
-1
932654
188000
932654:803
932654:803
Quantity
-2

9/17/2013
804
WIDGET_1
-1
123456
190111
123456:804
123456:804
Correct

9/17/2013
806
WIDGET_1
-3
123456
190111
123456:806
#N/A
Incorrect
932654

9/17/2013
833
WIDGET_2
-3
188000
932654
188000:833
188000:833
Correct

9/17/2013
855
WIDGET_1
-1
190111
123456
190111:855
190111:855
Quantity
-3

9/17/2013
855
WIDGET_2
-1
932654
188000
932654:855
932654:855
Correct

9/17/2013
875
WIDGET_1
-1
123456
190111
123456:875
123456:875
Correct

9/17/2013
875
WIDGET_2
-1
188000
932654
188000:875
188000:875
Correct

9/17/2013
883
WIDGET_1
-1
123456
190111
123456:883
123456:883
Correct

9/17/2013
891
WIDGET_2
-1
932654
188000
932654:891
#N/A
Incorrect
190111

9/17/2013
901
WIDGET_1
-1
123456
190111
123456:901
123456:901
Correct

9/17/2013
901
WIDGET_2
-1
188000
932654
188000:901
188000:901
Correct

9/17/2013
902
WIDGET_2
-2
188000
932654
188000:902
188000:902
Quantity
-1

9/17/2013
903
WIDGET_1
-2
190111
123456
190111:903
190111:903
Quantity
-1

9/17/2013
906
WIDGET_1
-1
190111
123456
190111:906
190111:906
Correct

9/17/2013
932
WIDGET_2
-1
932654
188000
932654:932
932654:932
Correct

9/17/2013
944
WIDGET_1
-2
123456
190111
123456:944
123456:944
Correct

9/17/2013
944
WIDGET_2
-2
188000
932654
188000:944
#N/A
REMAN

9/17/2013
602
WIDGET_2
-2
188000
932654
188000:602
#N/A
NOT ISSUED

<TBODY>
</TBODY>

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.

First Argument

Check for exact Match, show "Correct" in column I

=CONCATENATE(E2,":",B2,":",D2)

Second Argument

Check for Reman item being used, show "Reman" in Column I

~=CONCATENATE(F2,":",B2,":",F2)

Third Argument

Check for Same part number but different quantity, enter quantity in column J

~=CONCATENATE(E2,":",B2)

Fourth Argument

Check for Reman with Different Quantity, Show "Reman/QTY" in Column I

~=CONCATENATE(F2,":",B2)

Fifth Argument

Check for any item being used, Show Incorrect in Column I and part # of item in J

~=CONCATENATE(B2,":",D2)

Sixth Argument

Anything without transaction on Sheet 1 Mark as "NOT ISSUED"

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

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

byoung70104

New Member

Excel Facts

Similar threads

Forum statistics

Share this page

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

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

byoung70104

New Member

Excel Facts

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock