Searching and displaying records that match criteria.

[D Zandveld]

Hello again.

Experience level:
Good to Advanced in both Excel and VBA, i'm just stuck where to start!

I have 2 worksheets: Master Data and Supplier Data

Master Data contains information downloaded from our business warehouse:
Supplier no (key field 1)
Product no (key field 2)
Site no (key field 3)
Current price
Currency
Trading terms

Supplier Data contains pricing info from suppliers
Supplier no (key field 1)
Product no (key field 2)
Site no (key field 3)
Price - revised
Payment terms - revised
Currency
Trading Terms - revised

I want to produce a report based on the following logic:
IF
Supplier Data!Supplier no + Product no + Site no are found in Master Data,
THEN
compare based on price, terms (+ other criteria)
THEN
copy changes to 'Changes Report' worksheet.
(fields Supplier no, Product no, Site no, Old price, New price, Old terms, %
change, new terms)

The result would be a report that shows all the line items that have changed.

If more clarification is required, please let me know and I will deliver.

Thanks

 

[Otto Moehrbach]

D
Just off the top of my head, I would suggest a loop (Do or For loop),
something like the following:
First, make sure the data in the Master Data is sorted by the 3 fields you
site. All three. You could just add that to the beginning of the code.
Loop through all the Supplier Data and search for the first and last
instance of the Supplier No. in the Master Data range.
Set a range from this first to last Supplier No. to the variable SupplierNo.
Search the range SupplierNffset(,1) for the first and last instances of
Product No. and set that range to the variable ProductNo.
Search the range ProductNffset(,1) for the SiteNo.
If found, compare and copy as you wish.
Post back if this sounds promising and/or you need more. Otto

 

[Herbert Seidenberg]

This should get you started.
To automate, translate
Advanced Filter step into VBA
http://www.freefilehosting.net/download/3aki2

 

[leyhuong]

This should get you started.
To automate, translate
Advanced Filter step into VBAhttp://www.freefilehosting.net/download/3aki2

Hi,
I've read your reply to the topic owner. I was a little bit curious
about your methodology. Could you please let me know what you did in
order to find the exact match.

Thanks,
Huong L

 

[Herbert Seidenberg]

Huong L
The key is the column named "SPSmatch".
The SUMPRODUCT formula says in effect:
Look at the combination of "Supplier", "Product" and "Site"
at this row and find that combination on the other sheet,
then give me the row where you found it.
"MPrice" and "Mterms" use the row number
to find the rest of the data at the found row.
Advanced Filter whittles down the list to items
that do not match.