Moving data from one list to another 1

[Jeanie]

I am not sure that Excel is the best place to do this - but I seem to be able to figure things out in Excel better than Access.

I have a list (A) that has ID numbers and dates of service. Each date of service also has a provider number. I have another list (B) with the same ID numbers. This list has dates of assignment and an assigned number. The assigned numbers in list B have a date range. I would like to move the assigned number from list B to the correct dates of service on list A. An example follows:

List A
ID # DOS Prov #
12345 1/2/2005 999
12345 2/3/2005 998
12345 4/16/2006 997
67890 4/3/2005 996
67890 5/15/2005 996

List B
ID # Begin Date End Date Assign #
12345 5/1/2004 2/28/2005 888
12345 3/1/2005 4/30/2006 887
67890 4/1/2005 4/30/2005 888
67890 5/1/2005 5/31/2005 886

I want List A to now look like this

List A
ID # DOS Prov # Assign #
12345 1/2/2005 999 888
12345 2/3/2005 998 888
12345 4/16/2006 997 887
67890 4/3/2005 996 888
67890 5/15/2005 996 886

Is there anyway to do this?

Thanks for your help!

Jeanie

 

[bluedragon2]

This sumproduct assumes list 1 is on sheet 1 and list 2 is on sheet 2. It searches the list for your requirements and returns the assign # that is matches.

=SUMPRODUCT((sheet2!A1:A100=sheet1!A1)*(sheet2!B1:B100<sheet1!B1)*(sheet2!C1:C100>sheet1!B2)*(sheet2!D1100))



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[Jeanie]

Thanks, Blue - that worked perfectly!