Find data in separate worksheet

[[email protected]]

Good morning, Gurus.

I would like to put together a macro that would compare a list of part numbers against a database, and recommend alternative vendors for the part. The actual VBA I think I can handle, my problem is with the formula, array or whatever is needed to return the results I am looking for.

I have attached a sample workbook to this message that contains two worksheets. The "Sample Database" is an example of the format of the database (in Excel format) that I will be looking against. This data may be a hundred columns wide, and thousands of rows long.

The "Vendor Reference" worksheet shows an example of the data that would be returned if the "perfect" formula were entered into the cells. (I'll explain what I mean by "perfect" in a moment.) It also has an area designated for testing formulas to see what is returned. It's blank, because none of my tests returned anything but errors.

You will notice the column headings on the "Sample Database" worksheet. Columns A and B will always contain the part number and product description. The remaining columns will contain vendor specific data, with perhaps 25 different vendors, and 3 columns per vendor. These columns would be "PV", (for Primary Vendor), the vendor number, (010299, for example), and the Vend Part#.

If there is an asterisk, "*", under the "PV" column for that vendor, that means they are the "Primary" vendor. The cost from that vendor is listed under each vendor number. There may be instances where 2 different vendors are listed as primary. If that's the case, the formula can simply return the FIRST one found.

Here's what the "perfect" formula would return:
1. It would search the row beside the part number for the "*", if found it would return the vendor number for that vendor under "Prim Vend #". Then it would put the cost for that vendor under "Cost".
2. If no asterisk is found, those columns would be left blank.
3. It would then search for reference vendors, based on cost, lowest to highest, and list them under the "Ref Vendor" columns, and show the associated cost from each vendor.
4. If there is no entry in the database for cost from that vendor, then no entry should be made in the vendor reference.
5. If there is no Vend Part# for that vendor, then that vendor should also be ignored.

I say that's the "perfect" formula because it seems like too many arguments, but I would be happy with something close that didn't sort based on cost, that would simply return the first five results found with cost.

Here are some other points that may make this possible:
1. I'm sure there is no single formula that would return the results I am looking for, but perhaps a different formula in each columns coule.
2. Although the format of the database is set, the format of the results is still flexible, so if you know of a better way to format it to get the desire result, feel free to experiment.
3. If we could get the formula to return the cost in ascending order, as mentioned above, then I wouldn't need to have the Primary Vendor listed first, just list the cost from 5 different vendors, (if there are 5 attached to that part), lowest to highest.

I'm sorry for such a long post, and I don't even know if this is possible, but thanks in advance for any help you can offer. The sample workbook is attached to this message.

Have a good one.

Hutch.

[StephenR]

The actual VBA I think I can handle, my problem is with the vlookup, array or whatever is needed to return the results I am liiking for.

What is the distinction you're drawing between VBA and the vlookup? This could all be done with VBA. Are you after a custom function?

[[email protected]]

How's it going, Stephen?

I probably said it wrong. By Vlookup, I meant formula, becasue that's how I thought the data would have to be searched in the database.


By VBA, I meant that, if I had the formula, I could probably figure out how to automate adding it to my reference sheet.

As far as a custom function, I'm hoping this can all be done via VBA. I have zero experience with custom functions.

Thanks for the interest!

[[email protected]]

Perhaps entering a formula via VBA is the wrong way to go. What is the best way to get this data?

[ravishankar]

Hi
Try the macro by clicking the button in the attached file.
Ravi

[Krishnakumar]

Hi,

Run this VBA and create a pivot table. Basically the VBA would reformat your database so that you could create a PT.

Please Login or Register  to view this content.

Kris

[[email protected]]

Sorry so slow to respond, guys. Been out of the office for awhile.

In my limited experiments with your suggestions, it looks like either will work. I'll have the opportunity to test them on the full database next week, so I'll know more then as to which solution will work best for my needs.

Thanks for all your help!

Hutch