Search code examples
excelvbaplotcharts

Plot a Chart: selecting maximum of multiple Y values (by the thrid category) for a same date point

i know how to plot a time series via vba. However, i have a speical case. I have below data to plot: enter image description here

I want to plot a line chart (X axis: sell date, y axis: price). However, as highlighted, there are two same sell dates with different prices, for which i want to select the latest price by productioin time.

I do not have idea how to achieve it via VBA.

Selecting the latest values from the multiple values corresponding to one x-axis point.

Solution

  • With using macro recording:

    • Select E3:G8
    • Click Sort. Select Sort by: Column H Order:Newest to Oldest
    • Click Remove Duplicates and check only Column F. Click OK.
    • Click Sort. Select Sort by: Column H Order:Oldest to Newest.

    Stop macro recording.

    You get the unique values only and can create the chart.