Copy rows from one workbook to another based on criteria

[Huggheez]

I have been struggling to setup these two workbooks for a bit now, and I can't for the life of me figure out a formula to do what I need to do.

Essentially, I have one workbook that contains a list of purchase records for my company, sortable by Date, Vendor, Price, etc. and one workbook that has a sheet for every vendor. What I need is a formula that will search column B for a vendor, Allied Waste for example, and transfer all the information within the rows for every instance that vendor is found to the new workbook.

This is basically just a way where I can input information once in one workbook, where the sheets are divided by month, and the info will automatically transfer to another workbook, where the sheets are divided by vendor.

Any help?

[JBeaucaire]

If you want this automated and the "vendor" workbook(s) are separate and you wish to be able to open them without opening the original purchase workbook, then you'll need add a way for SIMPLE lookup in the closed workbook.

The most robust function (IMO) is INDEX/MATCH and its fast. But it requires a unique ID of some design for each row.

Let's say your vendor names are in column A and the purchases start at row 2 (row1 is titles). In an empty column in your purchases sheet (let's use column G), put this formula:
=IF(A2="", "", LEFT(A2,3) & "-" & COUNTIF($A$2:$A2,$A2))

If this is the first entry for "Jones Bottling", the value that appears is: Jon-1 and the when you copy that formula down each entry for Jones Bottling will increment the numeric portion (Jon-2,Jon-3, etc). Cool.

If you have two vendors with too similar names, you'll have to rethink the text part of that formula to create something unique. You could just use the entire cell value instead of the first 3 letters. Up to you.

Next, in your VENDOR sheet, you create an initial column of numbers: Jon-1,Jon-2,Jon-3 etc going down. Then you use an INDEX/MATCH formula to bring over the information for each column. Your first row of data is row2, column A has the value of Jon-1. To get the value of column C from the purchase book to appear, a formula like this:

=INDEX([Purchases.xls]Sheet1!C:C, MATCH(A2,[Purchases.xls]Sheet1!$G:$G, 0))

Does that get you started?

[Huggheez]

It definitely gets me started. It will take some time to adapt it to my workbooks. Thank you very much.