+ Reply to Thread
Results 1 to 3 of 3

How do i find first value and last value in 2 separate columns and store the values?

  1. #1
    Registered User
    Join Date
    02-25-2018
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1

    How do i find first value and last value in 2 separate columns and store the values?

    Hi I have the following data:

    stock name month open price close price
    A Jan 21.3 22
    A Feb 23 21
    A March 19 23.5
    A Dec 24 26
    B Jan 3 3
    B Feb 4 5.5
    B March 6 8.5
    B Dec 11 11.5

    How would i get the first value for open price and the last value for close price for each stock using excel vba? For example, stock name A has an open price of 21.3 and a close price of 26 for the full year. The data is already sorted by stock name and month. I have thousands of stock values and need to do it for each one.
    Attached Files Attached Files

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Seattle, WA, USA
    MS-Off Ver
    MS Office 365 Excel 2016
    Posts
    13,585

    Re: How do i find first value and last value in 2 separate columns and store the values?

    Hi xlrv and welcome to the forum,

    Instead of using VBA, how about a Pivot table to filter our the other months and only show Jan and Dec. I suggest this because, if you would have used dates instead of month name text, you would have an easy time sorting your data. When you use month names, sorting from earliest to latest is much harder.

    See the attached for a suggestion on a non VBA method.

    Jan and Dec Open Close prices.xlsm
    One test is worth a thousand opinions.
    Click the * below to say thanks.

  3. #3
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Old Country
    MS-Off Ver
    PC: 5F / 61 / 7E0 (PQ/PP)
    Posts
    8,240

    Re: How do i find first value and last value in 2 separate columns and store the values?

    Just filter you raw data by Jan & Dec
    sandy
    How to create an editor for Power Query with Notepad++ (tutorial)
    How to create timeline project with vertical today marker (2010, 2013, 2016 etc...) (examples)
    Excel can do everything but still doesn't read user's wishful thinking
    The ready made solution will not teach anyone anything
    A logical description of the problem is the basis of dialogue and a rapid solution. Nobody sits in your head to understand your way of thinking

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1