Macro to compare 3 selected columns in the list and output if the last column is of different value

[willlow]

hi,
i need help on a macro that compare 3 selected columns in a list. namely, "Cust Name", "Material" and "Price".(as below "data" )
If "Cust Name" and "Material" match BUT "Price" did not match, output the list of rows (as "result" below).

Macro should cater for more columns and rows of information to be added.

Hope someone can help. thanks.

Data
Regions Sold To Name Cust Name Ship To Name Material Price
CHN WT GP SLTE PRODUCT 1 9.61
CHN RP OTHERS SYD PRODUCT 2 0.35
CHN RP OTHERS SLTE PRODUCT 2 0.40
CHN RP OTHERS SYD PRODUCT 3 0.60
CHN RP OTHERS SYD PRODUCT 3 0.60
CHN RP APT N/A PRODUCT 4 1.52
CHN RP APT N/A PRODUCT 4 1.60
CHN WT APT N/A PRODUCT 5 0.57
CHN RP APT N/A PRODUCT 5 0.57
CHN WT APT N/A PRODUCT 6 0.57
CHN RP APT N/A PRODUCT 6 0.57
CHN RP APT N/A PRODUCT 7 0.57
CHN RP APT N/A PRODUCT 7 0.57
CHN RP APT N/A PRODUCT 8 0.57
CHN RP APT N/A PRODUCT 8 0.57
TWN RP APT N/A PRODUCT 10 0.57
TWN RP APT N/A PRODUCT 10 0.57
TWN RP APT N/A PRODUCT 10 0.60
TWN RP APT N/A PRODUCT 10 0.57

Results :

Regions Sold To Name Cust Name Ship To Name Material Price
CHN RP APT N/A PRODUCT 4 1.52
CHN RP APT N/A PRODUCT 4 1.60
TWN RP APT N/A PRODUCT 10 0.57
TWN RP APT N/A PRODUCT 10 0.57
TWN RP APT N/A PRODUCT 10 0.60
TWN RP APT N/A PRODUCT 10 0.57

 

[mirabeau]

In your "results" you seem to have some cases where "Cust Name" and "Material" match AND "Price" DOES match.

Regions Sold To Name Cust Name Ship To Name Material Price
CHN RP APT N/A PRODUCT 4 1.52
CHN RP APT N/A PRODUCT 4 1.6
TWN RP APT N/A PRODUCT 10 0.57
TWN RP APT N/A PRODUCT 10 0.57
TWN RP APT N/A PRODUCT 10 0.6
TWN RP APT N/A PRODUCT 10 0.57


So should not these be excluded from "results"?

Or am I misreading you?

 

[willlow]

Once there is a price difference in one of them, the whole list of "Cust Name" and "Material" matches should be shown in the result as well.
Hope this clarify.

 

[mirabeau]

If "Cust Name" and "Material" match BUT "Price" did not match, output the list of rows (as "result" below).

You latest post suggests you include cases where 'If "Cust Name" and "Material" match BUT "Price" DOES match,' then you still include these in the results.

So why not just include the entire list in the results?

Guess I'm just not clear enough about what you're looking for to be able to resolve your question.

Just what are your criteria as to what should be left out?

 

[willlow]

sorry, i understand your queries now..the correct sample should be as below.
In this case, If "Cust Name" and "Material" match BUT "Price" DOES NOT match, the whole list of "Cust Name" and "Material" matches still be in the result because there are other different values in "Sold To Name" or "Ship To Name" or "Regions" though the "Cust Name" and "Material" matches.

Data
Regions Sold To Name Cust Name Ship To Name Material Price
CHN WT GP SLTE PRODUCT 1 9.61
CHN RP OTHERS SYD PRODUCT 2 0.35
CHN RP OTHERS SLTE PRODUCT 2 0.40
CHN RP OTHERS SYD PRODUCT 3 0.60
CHN RP OTHERS SYD PRODUCT 3 0.60
CHN RP APT N/A PRODUCT 4 1.52
CHN RP APT N/A PRODUCT 4 1.60
CHN WT APT N/A PRODUCT 5 0.57
CHN RP APT N/A PRODUCT 5 0.57
CHN WT APT N/A PRODUCT 6 0.57
CHN RP APT N/A PRODUCT 6 0.57
CHN RP APT N/A PRODUCT 7 0.57
CHN RP APT N/A PRODUCT 7 0.57
CHN RP APT N/A PRODUCT 8 0.57
CHN RP APT N/A PRODUCT 8 0.57
TWN RP APT TPA PRODUCT 10 0.57
TWN WT APT N/A PRODUCT 10 0.57
TWN RP APT N/A PRODUCT 10 0.60
CHN RP APT TPB PRODUCT 10 0.57

Results :

Regions Sold To Name Cust Name Ship To Name Material Price
CHN RP APT N/A PRODUCT 4 1.52
CHN RP APT N/A PRODUCT 4 1.60
TWN RP APT TPA PRODUCT 10 0.57
TWN WT APT N/A PRODUCT 10 0.57
TWN RP APT N/A PRODUCT 10 0.60
CHN RP APT TPB PRODUCT 10 0.57