Fetching data based on conditions

[MoneyPlanters]

Please have a look at the image.
Col A contains names of stock exchanges.
Col B contains dates.
Col C, D, E and F contain data that we shall be using. Col G and H are not required.

Every day one such file shall be imported from the internet and the newer file will always keep adding (appending) data to the existing one. For example if the screenshot is considered to be taken from a file that was downloaded today, a file downloaded 7 days later will contain more data than this one plus whatever this one has.

Now we need to achieve the following :

If col A is 'CAC40' then pick the OHLC (stands for Open, High, Low, Close; present in Col C through F) values that correspond to the latest date available and paste them in L824, M824, N824, O824.

Similarly, If col A is 'COMP' then pick the OHLC (stands for Open, High, Low, Close; present in Col C through F) values that correspond to the latest date available and paste them in L825, M825, N825, O825.

And so on.. till all the markets are covered. There are 15 of them.

How can we achieve this ? Kindly help.
Thank you.

 

[MoneyPlanters]

You should be able to use the example below.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	Ticker	Date/Time	Open	High	Low	Close	*	*	Open	High	Low	Close
2	CAC40	01-Jan	2688	4140	3788	1774	*	CAC40	13632	10900	16202	16463
3	CAC40	02-Jan	3226	471	1800	3622	*	COMP	19092	12431	16222	20508
4	CAC40	03-Jan	737	615	82	877	*	*	*	*	*	*
5	CAC40	04-Jan	1770	1848	1584	481	*	*	*	*	*	*
6	CAC40	05-Jan	1210	535	3391	2992	*	*	*	*	*	*
7	COMP	01-Jan	4676	1749	198	3599	*	*	*	*	*	*
8	COMP	02-Jan	1898	2392	4968	3320	*	*	*	*	*	*
9	COMP	03-Jan	2949	1618	2966	878	*	*	*	*	*	*
10	COMP	04-Jan	2777	625	367	2269	*	*	*	*	*	*
11	COMP	05-Jan	2791	2756	2166	3725	*	*	*	*	*	*
Sheet1

#VALUE!

</td></tr></table></td></tr></table>

Cant really implement this.. i am pretty much ignorant. Please help.

 

[T. Valko]

cant it be automated ?

Probably, but it sounds like there's a lot going on with this file and you might want to go another route with this.

You might want to fully automate the entire process but it would need VBA programming.

Pull in the data
Remove old data
Sort or group as necessary
Produce the summary

I'm not much of a programmer so I can't be of much help in that regard.

 

[MoneyPlanters]

Probably, but it sounds like there's a lot going on with this file and you might want to go another route with this.

You might want to fully automate the entire process but it would need VBA programming.

Pull in the data
Remove old data
Sort or group as necessary
Produce the summary

I'm not much of a programmer so I can't be of much help in that regard.

I am indeed grateful to you for whatever help you have given me. I didnt really mean 'automation'. My use of words has not been correct. What I meant was the use of another formula to eliminate the unnecessary data and then use your formula for the sorting and grouping.

 

[T. Valko]

I am indeed grateful to you for whatever help you have given me. I didnt really mean 'automation'. My use of words has not been correct. What I meant was the use of another formula to eliminate the unnecessary data and then use your formula for the sorting and grouping.

Ok, well, the formula I suggested requires that the data be grouped together as is shown in your screencap.

If you delete "old" data then you should delete the entire row of data so that the data still remains grouped in a contiguous block.

I would put this data on its own sheet with nothing else. In your screencap this data started on row ~810 so there's a ton of other stuff on the sheet. Also, put the summary in another separate sheet.

 

[MoneyPlanters]

You should be able to use the example below.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	Ticker	Date/Time	Open	High	Low	Close	*	*	Open	High	Low	Close
2	CAC40	01-Jan	2688	4140	3788	1774	*	CAC40	13632	10900	16202	16463
3	CAC40	02-Jan	3226	471	1800	3622	*	COMP	19092	12431	16222	20508
4	CAC40	03-Jan	737	615	82	877	*	*	*	*	*	*
5	CAC40	04-Jan	1770	1848	1584	481	*	*	*	*	*	*
6	CAC40	05-Jan	1210	535	3391	2992	*	*	*	*	*	*
7	COMP	01-Jan	4676	1749	198	3599	*	*	*	*	*	*
8	COMP	02-Jan	1898	2392	4968	3320	*	*	*	*	*	*
9	COMP	03-Jan	2949	1618	2966	878	*	*	*	*	*	*
10	COMP	04-Jan	2777	625	367	2269	*	*	*	*	*	*
11	COMP	05-Jan	2791	2756	2166	3725	*	*	*	*	*	*
Sheet1

#VALUE!

</td></tr></table></td></tr></table>

njimack :Can you please assist me with this post ? Its almost the same. I only wish I had enough knowledge to modify your suggestions to suit this.
Thank you.