Filtering several rows on different criteria to build a single row.

[QuickLearner]

I have a need to build a single row of data from several rows in a
different worksheet.
Here is the requirement:
********************************************************
In one worksheet there are records for items entered at different dates
with dfferent information for the same items.
For example
On 1/1/2005 , some pricing information was entered for an item (item
001) from supplier A.
On 12/1/2005 some repair information was added for the same part (item
001)
from supplier B.
On 2/2/2006 pricing information was entered for the same part from
supplier C.

*******************************************************
The result is three rows in worksheet 1.
From these 3 rows in worksheet 1, I want a single record in worksheet 2
of the latest but most appropriate information for that part.
The preferred supplier is A so I would like the most recent information
recorded in the single row unless the information is from a preferred
source.

I would like to automate this process. Is there a way of building the
rows cell by cell and saying " look at the rows in worksheet 1 and fill
the cell with the most recent matching value unless there is a value
that is older from a preferred supplier, if there is take the latest
one, and so on.

********************************************************
Has anyone any ideas on how to build this single row from VBA??

Any help wold be appreciated.

I can see how it would be easy to do using Access, but I have not that
luxury in this case.

Many thanks.
Jack of all trades - Master of none!!

 

[Guest]

I'd be glad to help, but I need to pick your brain about what you really want.

Do you have a list or 'preferred sources' or is it just one? Further, does
the list change from time to time?

When you say you want 3 rows put into a single record, do you mean one cell,
or one row? Is there anything besides the 'preferred source' that would make
one entry 'most appropriate' ?

Given your examples and the statement that A is the preferred source, give
an example of what the 'single record' would look like.


Mike