Need help recording max values an array changes going down a column

BoomerGooner

New Member
Joined
Apr 2, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
I am sure something like this has been answered numerous times but I cannot seem to find something similar. Basically, I need excel to record the max value in Column A in Column B as it goes down Column A. I have attached an image showing what I need from a sample set by manually solving my issue. The sample set is only 37 rows so its easy to eye ball the data and get the values. The actual data set is 37,000 rows and therefore I need a formula or vba code. Please ask questions if there is confusion. Thank you.

Sample Values:

1​
2​
3​
4​
5​
6​
7​
8​
5​
6​
5​
4​
3​
5​
6​
7​
10​
12​
9​
8​
7​
14​
13​
12​
18​
19​
22​
14​
13​
16​
17​
19​
21​
23​
18​
19​
20​
 

Attachments

  • Screenshot 2021-04-02 114647.png
    Screenshot 2021-04-02 114647.png
    11.6 KB · Views: 4

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

BoomerGooner

New Member
Joined
Apr 2, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Swear I thought about this for a while before posting and came up with nothing useful but of course, minutes after posting I figure it out haha. If a mod wants to delete the thread, be my guest, but I'll post my solution in case someone may want it. The way I did it is probably very clunky and im sure can be combined/made better but it works for me.

If Column A is the data set then Column B should have the formula =IF(A2>B1,A2,B1). This will find the max value as you move down the column. In Column C the use of the formula =IF(B3=B2, "", B3) will remove the duplicates.
 

Forum statistics

Threads
1,136,207
Messages
5,674,415
Members
419,508
Latest member
trinstrick

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top