Lookup with constraints

[grev]

I have a table with 5 possible entries. The look up table with possible values has 150 entries. In other words I am trying to establish stements where if the date matches any of the 150 entries it pulls out the 5 (will always be 5) values and their corresponding column values. I am having trouble writing the statement in the 5 cells. How can I incorporate the entire table but get 5 different values.

Can you help? Sorry for the mumbo jumbo.

 

[Jim North]

I'm not following why you need to include all 5 values. Could you post a sample of the 5 entries to the board? If you begin you table with <pre> and end it with </pre> your spacing will be preserved and it will be easier to read.

 

[grev]

Here goes
Table 1 (The result Table) (sheet 1)
<pre> Date 9/11/02
Prt # Model Qty
x 1204 30
y 1305 40
z 5005 100
a 1005 100
b 2005 20
</pre>

The look up table (reference table)(sheet 2) has 150 of these values sorted by part# and date. From that table I want to pull only the part#'s and its respective model and qty that have the correct corresponding date for sheet 1. Basically it is two seperate worksheets.

Hope this helps.

 

[Jim North]

On 2002-09-13 08:32, grev wrote:
Here goes
Table 1 (The result Table) (sheet 1)<pre>
Date 9/11/02
Prt # Model Qty
x 1204 30
y 1305 40
z 5005 100
a 1005 100
b 2005 20</pre>

The look up table (reference table)(sheet 2) has 150 of these values sorted by part# and date. From that table I want to pull only the part#'s and its respective model and qty that have the correct corresponding date for sheet 1. Basically it is two seperate worksheets.

Hope this helps.

So you are trying to calculate the Qty field??

How is your Model field used, or do you only care about Prt#??

_________________
JRN

Excel 2000; Windows 2000
This message was edited by Jim North on 2002-09-13 08:44

 

[lenze]

ADVERTISEMENT

I thinking you are trying to pull the information from your main table for all items by Date. If so, you can use Advanced Filter. In the example, the yellow represents your main table. The Green is the criteria range, and the blue is the output range. I recorded a macro (FilterDate) to pull data from the main table to the output range. If you change the date in the criteria range, you can rerun the macro to display info for that date.

Book1
	A	B	C	D	E	F	G	H	I
1	Date	Prt #	Model	Qty		Date
2	9/11/02	x	1204	30		9/12/02
3	9/11/02	y	1305	40
4	9/11/02	z	5005	100		Date	Prt #	Model	Qty
5	9/11/02	a	1005	100		9/12/02	x	1204	40
6	9/11/02	b	2005	20		9/12/02	y	1305	60
7	9/12/02	x	1204	40		9/12/02	z	5005	90
8	9/12/02	y	1305	60		9/12/02	a	1005	110
9	9/12/02	z	5005	90		9/12/02	b	2005	30
10	9/12/02	a	1005	110
11	9/12/02	b	2005	30
12	9/13/02	x	1204	40
13	9/13/02	y	1305	65
14	9/13/02	z	5005	90
15	9/13/02	a	1005	100
16	9/13/02	b	2005	35
...

Here is the macro code:
Sub FilterDate()
'
' FilterDate Macro
' Macro recorded 9/13/2002 by lenze
'
Range("A1:D16").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
"F1:G2"), CopyToRange:=Range("F4"), Unique:=False
End Sub

Note: You can also use MSQuery for this.

HTH

 

[grev]

I think this is going to work. Will let you know. Thanks for the help.

 

[grev]

ADVERTISEMENT

I am somewhat confused with the code. Is F4 assigned to the macro. I have done Macro's before but your code seems to be above my level. Can you explain it a little more? Do I edit this in the Edit function in creating the Macro? Thanks for the help.

 

[grev]

Is there an easier way to do this? I am still having trouble with the code. Need help.

 

[s-o-s]

If your not happy with Macro's then you could try 1 of the following:
1) Doing it manually.

Select your whole table.
Insert an autofilter.
In the Date column select the date you are interested in.

2) Pivot Table.

Select your whole table.
Data | Pivot Table.
Next.
Next.
Layout.
Drag Date to Page
Drag Prt # to Row
Drag Model to Row
Drag Qty to Data

Double Click on Prt # field change Subtotals to None. (This stops doubling up the lines)

Click OK

You can now change the Top (Page) drop down to the date of your choice and this will display the information you require.

3) Regarding the macro by Lenze.

Sub FilterDate()
'
' FilterDate Macro
' Macro recorded 9/13/2002 by lenze
'
Range("A1:D16").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
"F1:G2"), CopyToRange:=Range("F4"), Unique:=False
End Sub

assuming your data is not in the format above.

Change the A1:D16 to include your entire Data table.

If you have a Target Date in cell Z2 make sure that the word Date appears in cell Z1 then change F1:G2 to Z1:Z2

Lastly if you want the output to appear in a table beginning in Z10.

Change the F4 to Z10.


_________________
Hope This Helps.
Sean.<A HREF= "http://website.lineone.net/~s-o-s/Index.html">

This message was edited by s-o-s on 2002-09-15 15:29