Ranking based on earliest data input

ExcelNewbie2020

New Member
Joined
Dec 3, 2020
Messages
19
Office Version
  1. 365
Platform
  1. Windows
Experts,

Please see table below;

ValueRank
Name 1105
Name 2
Name 32001
Name 4
Name 5
Name 6
Name 710002
Name 8
Name 9
Name 105003


Is there any excel function that will rank the values based on the first data inputted?
on the above table rank # 1 goes to Name3 because i inputted the value 200 1st, rank #2 is Name7 because i inputted 1000 2nd and so on..

I am looking for a function that once i input a value, the rank/sequence will update automatically. Thank you very much..
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,829
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
You Sholud add Time & Date input and then Sort based on
You can Use now Function for this also. and try this method.
In File\Options\Formulas check Enable iterative calculation Then, assuming that A1 is currently blank, enter

Excel Formula:
=IF(A1 = 0,TODAY(),A1)
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,829
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Sorry try this at C1:
Excel Formula:
=IF(A1<>"",IF(C1="",NOW(),C1),"")
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,548
Office Version
  1. 365
Platform
  1. Windows
Assuming that you won't ever delete or change a value in column B, & column C at least contains a heading to start with, you could try this worksheet change event code with a copy of your workbook.
To implement ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test.
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range
  Dim NextRank As Long
 
  Set Changed = Intersect(Target, Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row))
  If Not Changed Is Nothing Then
    NextRank = Columns("C").SpecialCells(xlConstants).Count
    Application.EnableEvents = False
    For Each c In Changed
      c.Offset(, 1) = NextRank
    Next c
    Application.EnableEvents = True
  End If
End Sub
 

ExcelNewbie2020

New Member
Joined
Dec 3, 2020
Messages
19
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

You Sholud add Time & Date input and then Sort based on
You can Use now Function for this also. and try this method.
In File\Options\Formulas check Enable iterative calculation Then, assuming that A1 is currently blank, enter

Excel Formula:
=IF(A1 = 0,TODAY(),A1)

Sorry try this at C1:
Excel Formula:
=IF(A1<>"",IF(C1="",NOW(),C1),"")
it gives me circular reference error..thanks
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,829
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Are you do this.
In File\Options\Formulas check Enable iterative calculation (add tick mark) Then
 

ExcelNewbie2020

New Member
Joined
Dec 3, 2020
Messages
19
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Are you do this.
In File\Options\Formulas check Enable iterative calculation (add tick mark) Then
sorry, after enabling still not working. I guess i did not make my explanation clear. The value and rank column is initially empty. the data in the rank column will depends on data that will be inputted (in random) in value column.
 

ExcelNewbie2020

New Member
Joined
Dec 3, 2020
Messages
19
Office Version
  1. 365
Platform
  1. Windows
Assuming that you won't ever delete or change a value in column B, & column C at least contains a heading to start with, you could try this worksheet change event code with a copy of your workbook.
To implement ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test.
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range
  Dim NextRank As Long

  Set Changed = Intersect(Target, Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row))
  If Not Changed Is Nothing Then
    NextRank = Columns("C").SpecialCells(xlConstants).Count
    Application.EnableEvents = False
    For Each c In Changed
      c.Offset(, 1) = NextRank
    Next c
    Application.EnableEvents = True
  End If
End Sub
this actually works using vba. However, Is there any chance this can be done thru excel formula? thank you so much
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,548
Office Version
  1. 365
Platform
  1. Windows
Enable iterative calculation
Just be aware that this is an application-wide setting so applies to all formulas in all sheets in all workbooks while the setting is in place.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,993
Messages
5,639,423
Members
417,089
Latest member
jonstr101

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