Ranking based on earliest data input

[ExcelNewbie2020]

Experts,

Please see table below;

	Value	Rank
Name 1	10	5
Name 2
Name 3	200	1
Name 4
Name 5
Name 6
Name 7	1000	2
Name 8
Name 9
Name 10	500	3

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..

 

[maabadi]

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

=IF(A1 = 0,TODAY(),A1)

 

[maabadi]

Sorry try this at C1:

=IF(A1<>"",IF(C1="",NOW(),C1),"")

 

[Peter_SSs]

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).

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]

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

=IF(A1 = 0,TODAY(),A1)

Sorry try this at C1:

=IF(A1<>"",IF(C1="",NOW(),C1),"")

it gives me circular reference error..thanks

 

[maabadi]

Are you do this.
In File\Options\Formulas check Enable iterative calculation (add tick mark) Then

 

[ExcelNewbie2020]

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]

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).

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]

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.

 

[maabadi]

This is Example:

TEST.xlsx
	A	B	C	D	E
1	Names	Value	InPut Time	Rank
2	Nam1	10	1/31/2021 16:13	1
3	Nam2
4	Nam3	800	1/31/2021 16:15	5
5	Nam4
6	Nam5
7	Nam6	600	1/31/2021 16:15	4
8	Nam7
9	Nam8	1000	1/31/2021 16:14	3
10	Nam9
11	Nam10	200	1/31/2021 16:13	2
12
Sheet1

Cell Formulas
Range	Formula
C2:C11	C2	=IF(AND(A2<>"",B2<>""),IF(C2="",NOW(),C2),"")
D2:D11	D2	=IFERROR(RANK.AVG(C2,$C$2:$C$11,1),"")