Need Urgent Help 2 - Return Values based on Condition

[uberathlete]

Hi everyone. This is like a follow up to a question I posed earlier (http://www.mrexcel.com/board2/viewtopic.php?t=288093&highlight=) but is a bit different. I have the following worksheet:

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Date	Symbol	Price	Volume		DATE	SYMBOL	PRICE	VOLUME
2	01/04/2000	CA2000F	94.82	31261		01/04/2000
3	01/04/2000	CA2000G	94.71	35163		01/05/2000
4	01/04/2000	CA2000H	94.55	5000		01/06/2000
5	01/04/2000	CA2000M	94.11	4500		01/07/2000
6	01/04/2000	CA2000U	93.81	31261		01/10/2000
7	01/04/2000	CA2000Z	93.61	31261		01/11/2000
8	01/04/2000	CA2001H	93.42	31261		01/12/2000
9	01/04/2000	CA2001M	93.33	31261		01/13/2000
10	01/04/2000	CA2001U	93.27	31261		01/14/2000
11	01/04/2000	CA2001Z	93.22	65900		01/17/2000
12	01/04/2000	CA2002H	93.17	31261		01/18/2000
13	01/04/2000	CA2002M	93.12	31261		01/19/2000
14	01/04/2000	CA2002U	93.07	31261		01/20/2000
15	01/04/2000	CA2002Z	93.02	31261		01/21/2000
16	01/05/2000	CA2000F	94.79	47994		01/24/2000
17	01/05/2000	CA2000G	94.64	47994		01/25/2000
18	01/05/2000	CA2000H	94.44	47994		01/26/2000
19	01/05/2000	CA2000M	93.99	47994		01/27/2000
20	01/05/2000	CA2000U	93.69	47994		01/28/2000
21	01/05/2000	CA2000Z	93.46	47994		01/31/2000
22	01/05/2000	CA2001H	93.3	47994		02/01/2000
23	01/05/2000	CA2001M	93.21	47994		02/02/2000
24	01/05/2000	CA2001U	93.15	47994		02/03/2000
25	01/05/2000	CA2001Z	93.1	47994		02/04/2000
26	01/05/2000	CA2002H	93.05	47994		02/07/2000
27	01/05/2000	CA2002M	93	47994		02/08/2000
28	01/05/2000	CA2002U	92.95	47994		02/09/2000
29	01/05/2000	CA2002Z	92.9	47994		02/10/2000
Sheet1

I would like to fill columns G to I. The condition is that what should be in those columns correspond to the date in column F and must be for the symbol that has the highest volume (from column D). The data array to refer to is columns A to D, which are sorted by date.

So for example, for the date 01/04/2000, the symbol with the highest volume is CA2001Z, so the info in row 11 must be used and therefore must look like:

Excel Workbook
	F	G	H	I
1	DATE	SYMBOL	PRICE	VOLUME
2	01/04/2000	CA2001Z	93.22	65900
3	01/05/2000
4	01/06/2000
5	01/07/2000
6	01/10/2000
7	01/11/2000
8	01/12/2000
9	01/13/2000
10	01/14/2000
11	01/17/2000
12	01/18/2000
13	01/19/2000
14	01/20/2000
15	01/21/2000
16	01/24/2000
17	01/25/2000
18	01/26/2000
19	01/27/2000
20	01/28/2000
21	01/31/2000
22	02/01/2000
23	02/02/2000
24	02/03/2000
25	02/04/2000
26	02/07/2000
27	02/08/2000
28	02/09/2000
29	02/10/2000
Sheet1

If for a certain date, the volume is all the same for all symbols, then the symbol to use is the first symbol for that date. So in this case, for the date 01/05/2000 wherein all volumes are the same, the symbol to look at is CA2000F because it comes first before all of the other symbols. So, we must have:

Excel Workbook
	F	G	H	I
1	DATE	SYMBOL	PRICE	VOLUME
2	01/04/2000	CA2001Z	93.22	65900
3	01/05/2000	CA2000F	94.79	47994
4	01/06/2000
5	01/07/2000
6	01/10/2000
7	01/11/2000
8	01/12/2000
9	01/13/2000
10	01/14/2000
11	01/17/2000
12	01/18/2000
13	01/19/2000
14	01/20/2000
15	01/21/2000
16	01/24/2000
17	01/25/2000
18	01/26/2000
19	01/27/2000
20	01/28/2000
21	01/31/2000
22	02/01/2000
23	02/02/2000
24	02/03/2000
25	02/04/2000
26	02/07/2000
27	02/08/2000
28	02/09/2000
29	02/10/2000
Sheet1

This must then be done for the ret of the dates. I hope this isn't too much of a request but any help woul be great. If you can recommend some sort of procedure or macro or anything it would be much appreciated. Thanks!

 

[Aladin Akyurek]

Ack! Aladin, faceofthegod, I really appreciate your suggestions but for some reason, the formulas don't work for certain cells. I don't know why. For certain cells, the second condition doesn't work. This is asking a bit much but it would be really really great if you could take a look at the sheet I'm using which has the full dataset. This is driving me nuts.

Would you post the bit of data for which the system fails?

 

[uberathlete]

Ack! Aladin, faceofthegod, I really appreciate your suggestions but for some reason, the formulas don't work for certain cells. I don't know why. For certain cells, the second condition doesn't work. This is asking a bit much but it would be really really great if you could take a look at the sheet I'm using which has the full dataset. This is driving me nuts.

Would you post the bit of data for which the system fails?

Oh I would but Excel Jeanie only accepts up to 500 cells.

 

[Aladin Akyurek]

Ack! Aladin, faceofthegod, I really appreciate your suggestions but for some reason, the formulas don't work for certain cells. I don't know why. For certain cells, the second condition doesn't work. This is asking a bit much but it would be really really great if you could take a look at the sheet I'm using which has the full dataset. This is driving me nuts.

Would you post the bit of data for which the system fails?

Oh I would but Excel Jeanie only accepts up to 500 cells.

Just 5 records where the formulas fail...

 

[uberathlete]

Ack! Aladin, faceofthegod, I really appreciate your suggestions but for some reason, the formulas don't work for certain cells. I don't know why. For certain cells, the second condition doesn't work. This is asking a bit much but it would be really really great if you could take a look at the sheet I'm using which has the full dataset. This is driving me nuts.

Would you post the bit of data for which the system fails?

Oh I would but Excel Jeanie only accepts up to 500 cells.

Just 5 records where the formulas fail...

Ack! Aladin, faceofthegod, I really appreciate your suggestions but for some reason, the formulas don't work for certain cells. I don't know why. For certain cells, the second condition doesn't work. This is asking a bit much but it would be really really great if you could take a look at the sheet I'm using which has the full dataset. This is driving me nuts.

Would you post the bit of data for which the system fails?

Oh I would but Excel Jeanie only accepts up to 500 cells.

Just 5 records where the formulas fail...

Hi Aladin. I decided to show the whole sheet so that you could get a better idea of what it looks like. It's located here:

http://bakafool.bravehost.com/index3.htm

The first 4 columns is the data array.

If you look at the DATE column which is column F ...

- for 7/16/02 , the symbol is CA2004M . However, in the data array the first symbol for that date is CA2002N .

- for 2/23/06 , the symbol is CA2006M. However, in the data array the first symbol for that date is CA2006H .

There are actually some more errors in certain cells and the data array as well as columns F to I are longer but I cut it to save space. Those errors basically only occur if the whole data array is used or referred to by the formula. I hope this can be remedied.

 

[facethegod]

On your list that you sent i didn't see those dates those dates that you referenced, but try these revised formula's:

J2 down w/ Ctrl + shift + Enter

=MIN(IF(CODE(RIGHT(IF($D$1:$D$1522=MAX(IF($F2=$A$1:$A$1522,$D$1:$D$1522)),$B$1:$B$1522,"[")))=MIN(CODE(RIGHT(IF($D$1:$D$1522=MAX(IF($F2=$A$1:$A$1522,$D$1:$D$1522)),$B$1:$B$1522,"[")))),ROW($A$1:$A$1522)))

G2 to I2 copied accross and then down

=IF($J2=1,"",INDEX(C:C,$J2,1))

 

[uberathlete]

Hi facethegod. Unfortunately, I get the same errors. The link I provided has the dates.

 

[Aladin Akyurek]

...

If you look at the DATE column which is column F ...

- for 7/16/02 , the symbol is CA2004M . However, in the data array the first symbol for that date is CA2002N .
...

Just checked this date... The Max volume on this date (on your link) is:

33116

The first symbol that corresponds to this value on 7/16/02 is:

CA2004M