uberathlete
Board Regular
- Joined
- Jul 11, 2007
- Messages
- 117
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:
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:
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:
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!
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!