lookup simultaneous 3 columns

[saurabh_sharma]

how can we look up values by lookup function if the data apoints are alongside in 3 diff columns- one after the other. Can we look up a no. of ids against a series of dates in the adjacent columns and their performance metrics in teh following columns.. an advanced for of step by step column filtering ?

[Jay Petrulis]

It is certainly possible. Depending on your data, either SUMPRODUCT, INDEX/MATCH, The Deatabase functions, or VBA seem to be likely candidates.

Please provide more details to get a specific response.

Bye,
Jay

[saurabh_sharma]

Thanks for the help.

I am facing an issue with the file below.
I need to draw a graph for GCVB ids for the diff. Dates for the diff parameters. I have tried using the pivot table but as the data is to be increased everyday so possibly a v lookup would help.
Pls. advise and help wit this…
calls contacts
4/25/02 GCVBMVT 10554 9497
4/25/02 GCVBSFT 7471 6483
4/25/02 GCVBMVT 10554 9497
4/24/02 GCVBIUT 607 537
4/24/02 GCVBIPT 11993 10859
4/24/02 GCVBKGT 5873 5085
4/24/02 GCVBIZT 10865 9958
4/23/02 GCVBIUT 607 537
4/23/02 GCVBIPT 11993 10859
4/23/02 GCVBKGT 5873 5085
4/23/02 GCVBIZT 10865 9958
4/23/02 GCVGSBT 4749 4255
4/23/02 GCVGVFT 9350 8428
4/23/02 GCVBIKT 8135 7319
4/23/02 GCVBPET 7954 7157
4/23/02 GCVBMZT 10426 9353
4/23/02 GCVBMWT 5935 5270
4/23/02 GCVBPAT 9740 8723
4/23/02 GCVBMVT 10554 9497

[Aladin Akyurek]

On 2002-04-27 06:22, saurabh_sharma wrote:
Thanks for the help.

I am facing an issue with the file below.
I need to draw a graph for GCVB ids for the diff. Dates for the diff parameters. I have tried using the pivot table but as the data is to be increased everyday so possibly a v lookup would help.
Pls. advise and help wit this…
calls contacts
4/25/02 GCVBMVT 10554 9497
4/25/02 GCVBSFT 7471 6483
4/25/02 GCVBMVT 10554 9497
4/24/02 GCVBIUT 607 537
4/24/02 GCVBIPT 11993 10859
4/24/02 GCVBKGT 5873 5085
4/24/02 GCVBIZT 10865 9958
4/23/02 GCVBIUT 607 537
4/23/02 GCVBIPT 11993 10859
4/23/02 GCVBKGT 5873 5085
4/23/02 GCVBIZT 10865 9958
4/23/02 GCVGSBT 4749 4255
4/23/02 GCVGVFT 9350 8428
4/23/02 GCVBIKT 8135 7319
4/23/02 GCVBPET 7954 7157
4/23/02 GCVBMZT 10426 9353
4/23/02 GCVBMWT 5935 5270
4/23/02 GCVBPAT 9740 8723
4/23/02 GCVBMVT 10554 9497

If you're going to create a pivot table from your data, you better use a dynamic name range and give this name as Range to the Wizard.

I'll assume the sample above to be in A1:D20 including labels/column headings in a worksheet called Data.

Activate Insert|Name|Define.
Enter Nrecs as name in the Names in Workbook box.
Enter as formula in the Refers to box:

=MATCH(9.99999999999999E+307,Data!$A:$A)

Note that this formula uses your dates column.

Activate Add. (Don't leave yet the Define Name window.)

Enter Drange as name in the Names in Workbook box.
Enter as formula in the Refers to box:

=OFFSET(Data!$A$1,0,0,Nrecs,4)

Note that 4 indicates how many columns your data area covers.

Activate OK.

Now give Drange as value of Range in PivotTable Wizard.

The above allows you to add to or delete from your data area as many records as you wish. At refresh PivotTable will know the new range.

Aladin

[saurabh_sharma]

Thanks for teh help wit pivot table
can we also use the simpler verion step by stpe look up functions as well and link the graph input to them as well?

On 2002-04-27 06:52, Aladin Akyurek wrote:

If you're going to create a pivot table from your data, you better use a dynamic name range and give this name as Range to the Wizard.

I'll assume the sample above to be in A1:D20 including labels/column headings in a worksheet called Data.

Activate Insert|Name|Define.
Enter Nrecs as name in the Names in Workbook box.
Enter as formula in the Refers to box:

=MATCH(9.99999999999999E+307,Data!$A:$A)

Note that this formula uses your dates column.

Activate Add. (Don't leave yet the Define Name window.)

Enter Drange as name in the Names in Workbook box.
Enter as formula in the Refers to box:

=OFFSET(Data!$A$1,0,0,Nrecs,4)

Note that 4 indicates how many columns your data area covers.

Activate OK.

Now give Drange as value of Range in PivotTable Wizard.

The above allows you to add to or delete from your data area as many records as you wish. At refresh PivotTable will know the new range.

Aladin

[Aladin Akyurek]

On 2002-04-27 07:37, saurabh_sharma wrote:
Thanks for teh help wit pivot table
can we also use the simpler verion step by stpe look up functions as well and link the graph input to them as well?

You appear to want a formula-based approach. OK, but What is "GCVB ids for the diff. Dates for the diff parameters"? I mean it's not clear to me what the lookup values are? Care to elaborate on that?

[Yogi Anand]

Hi Saurabh:

Thanks for teh help wit pivot table
can we also use the simpler verion step by stpe look up functions as well and link the graph input to them as well?

It is not quite clear what exactly you are asking, you do not however necessarily need a VLOOKUP function. Let us look into your data a little bit ... if the first record started in cell A2, then

A2 will house 4/25/2002
B2 will house GCVBMVT
C2 will house 10554
D2 will house 9497

so your entire dataset will be housed in cells A2:D20

Now let us say you are interested in locating a record with ID of GCVBIUT (say in cell F2)... you can locate in which row it lies by using the following formula in cell G2 ...

=MATCH($F2,B2:B20,0) will give 4 as the row number where this recod is encountered first

then
=INDEX($A$2:$D$20,MATCH($F2,$B$2:$B$20,0),1) will give you 4/24/2002 as the date associated with this record the first time this record is encountered

so you can also locate the other two pareameters associated with this record by adjusting the above formula

if you want to count the number of times this ID is encountered in your dataset, use the formula ...

=COUNTIF($B$2:$B$20,$F2) ... to give 2

I believe the formulas that you will need are covered in the set of formulas delineated above. I hope this will help you in your project of charting the data and the associated parameters.

Please post back if it works for you ... otherwise explain a little further and let us take it from there!
_________________
Yogi Anand

Edit: Deleted inactive website from hardcoded signature

[ This Message was edited by: Yogi Anand on 2003-01-19 13:35 ]

[saurabh_sharma]

Thanks for seeking more info.
I need to automate reports where performance by differnt associates appears in a consolidated report for 1 day. The macro is copying all the data one below the other. I need a graph of 1 particular id for all dates as a run chart and for 1 parameter at a time.
Hope it clarifies...

On 2002-04-27 08:19, Aladin Akyurek wrote:

On 2002-04-27 07:37, saurabh_sharma wrote:
Thanks for teh help wit pivot table
can we also use the simpler verion step by stpe look up functions as well and link the graph input to them as well?

You appear to want a formula-based approach. OK, but What is "GCVB ids for the diff. Dates for the diff parameters"? I mean it's not clear to me what the lookup values are? Care to elaborate on that?

[Yogi Anand]

Hi Aladin:
I did not see your last post, before I posted mine -- as you stated, it is not clear what specifically Saurabh is asking.

Regards!

Yogi Anand

[saurabh_sharma]

Thanks for seeking more info.
I need to automate reports where performance by differnt associates appears in a consolidated report for 1 day. The macro is copying all the data one below the other. I need a graph of 1 particular id for all dates as a run chart and for 1 parameter at a time.
Hope it clarifies...

On 2002-04-27 08:33, Yogi Anand wrote:
Hi Aladin:
I did not see your last post, before I posted mine -- as you stated, it is not clear what specifically Saurabh is asking.

Regards!

Yogi Anand