How to identify two specific values in one column

Hi everyone!

I've got two question on how to 'find' some values in a specific data file, namely:
1. the '52-Week High' of the share price (highest value of the share in that specific year)
2. the value of the share price of the last trading day of that specific year (this isn't always 12/31/20..)

Simplified, my data 'Before' looks as follows: Before.xlsx

-In column 'A' I've a created a line of unique identifiers (company CUSIP + year -> combined column B and C), in my original set this column consists of 405 companies over 6 years of daily data -> aprox 460000 rows
-In column 'B' the year is noted
-In column 'C' the company's CUSIP (identifier for specific company)
-In column 'D' the date is shown -> its daily trading data
-In column 'E' name of the company


My final data set 'After' has to look something like this: After.xlsx

-In column 'A' the unique identifiers (company CUSIP + year)
-In column 'B' the year is noted
-In column 'C' the company's CUSIP (identifier for specific company)
-In column 'D' name of the company
-In column 'E' the '52-Week High' of the share price (highest value of the share in that specific year)
-In column 'F' the value of the share price of the last trading day of that specific year (this isn't always 12/31/20..)

The big question is how do I manage to identify an link the following data from the 'Before'-sheet to the 'After'-sheet:
1. '52-Week High' of the share price (highest value of the share in that specific year)
2. value of the share price of the last trading day of that specific year (this isn't always 12/31/20..)

Regarding to the '52-Week High' I've already tried with the following functions:
- VLOOKUP: When I use this one I only get the most upper value, for example in the case of '8668102001' in the sheet 'Before' this would become '13.04'
- Combined INDEX/MATCH: The same problem as with VLOOKUP
- IF-function:In this case I get the max value of the entire 'C'-column, in the example of sheet 'Before': this would be '69.86'

Regarding the value of the share price of the last trading day of a specific year, I couldn't manage to come up with one.

Since I'm already stuck on the same point because of these problems, I really hope someone could help me with these questions !!

Regards,
Floris

Attachments:
- Attachment 303689
- Attachment 303690

I don't find any workbook in your post.

See the attached file.

I made them with an pivot table.

If you have questions, just ask.

Thx Oeldere for your swift reply! The pivot table surely shows the max value per company per year.

However, because of the fact I'm modifying this data set so I can use it in statistical programs, I wonder if there is a solution to fit/link the requested values from the 'Before'-workbook to the format in the 'After'-workbook?

Which other data you want to analyze.

Please also show it in the file.

Maybe this one could be usefull to you.

And maybe this gives you an idea what you can do with excel.

See the attached file with an pivot table and a graph.