Need to find the Relative Number from a List of Numbers

[silverlucky5]

<tbody>
[TD="class: xl65"]My problem is this:[/TD]

[TD="class: xl66"]I have a column of numbers that are not necessarily sequential and are not sorted numerically.[/TD]

[TD="class: xl66"]Using a formula, I would like to return the relative number of those numbers in a new column.[/TD]

[TD="class: xl66"]For example: I have a column of 4 numbers: 1, 1, 4, 2, 1, 1, 5. I want to return, respectively, in a new column: 1, 1, 3, 2, 1, 1, 4.[/TD]

[TD="class: xl65"]I have tried all kinds of formulas using INDEX, MATCH, SMALL, LARGE, MIN, MAX, but just can't seem to figure it out.[/TD]

[TD="class: xl65"]I have attached sample data in case needed. https://www.dropbox.com/s/1djt8sfv40acwmi/Absolute Reference.xlsx?dl=0 [/TD]

[TD="class: xl65"]Any help would be greatly appreciated![/TD]

[TD="class: xl65"]Thanks![/TD]

</tbody>

 

[Aladin Akyurek]

Given the following...

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][/tr][tr][td]

1​

[/td][td]

1​

[/td][/tr]
[tr][td]

2​

[/td][td]

1​

[/td][/tr]
[tr][td]

3​

[/td][td]

4​

[/td][/tr]
[tr][td]

4​

[/td][td]

2​

[/td][/tr]
[tr][td]

5​

[/td][td]

1​

[/td][/tr]
[tr][td]

6​

[/td][td]

6​

[/td][/tr]
[tr][td]

7​

[/td][td]

5​

[/td][/tr]
[/table]

would this below be the output?

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

B​

[/td][/tr][tr][td]

1​

[/td][td]

1​

[/td][/tr]
[tr][td]

2​

[/td][td]

1​

[/td][/tr]
[tr][td]

3​

[/td][td]

3​

[/td][/tr]
[tr][td]

4​

[/td][td]

2​

[/td][/tr]
[tr][td]

5​

[/td][td]

1​

[/td][/tr]
[tr][td]

6​

[/td][td]

5​

[/td][/tr]
[tr][td]

7​

[/td][td]

4​

[/td][/tr]
[/table]

 

[silverlucky5]

Hi Aladin, yes indeed...the output you show is what I am looking for!

 

[Aladin Akyurek]

Hi Aladin, yes indeed...the output you show is what I am looking for!

In B1 control+shift+enter, not just enter, and copy down:

=MATCH(A1,SMALL(IF(FREQUENCY($A$1:$A$7,$A$1:$A$7),$A$1:$A$7),
     ROW(INDIRECT("1:"&SUM(IF(FREQUENCY(MATCH($A$1:$A$7,$A$1:$A$7,0),
     MATCH($A$1:$A$7,$A$1:$A$7,0)),1))))),0)

 

[silverlucky5]

that works perfectly! Thank you SO much!

 

[Peter_SSs]

Here's a considerably shorter one, & without the volatile INDIRECT function, that I think also does the same job.
Also confirmed with Ctrl+Shift+Enter before copying down.

Excel Workbook
	A	B
1	1	1
2	1	1
3	4	3
4	2	2
5	1	1
6	6	5
7	5	4
Posn

 

[Aladin Akyurek]

that works perfectly! Thank you SO much!

You are welcome.

Since we have to do with numbers (integers), we can set up distinct count part accordingly, giving us a shorter formula.

In B1 control+shift+enter:

=MATCH(A1,SMALL(IF(FREQUENCY($A$1:$A$7,$A$1:$A$7),$A$1:$A$7),
    ROW(INDIRECT("1:"&SUM(IF(FREQUENCY($A$1:$A$7,$A$1:$A$7),1))))),0)

If we want it to be generic...

 

[Peter_SSs]

Since we have to do with numbers (integers), we can set up distinct count part accordingly, giving us a shorter formula.

But not recommending this over the non-volatile, more efficient (& shorter) alternative?

If we want it to be generic...

Meaning?

I have attached sample data in case needed.

It seems that you actually have a further 'grouping' criteria. Try this in cell C11 of the sample layout. Still confirmed with C+S+E before copying down.

=COUNT(IF(A$11:A$43=A11,IF(B$11:B$43<B11,IF(MATCH(B$11:B$43,IF(A$11:A$43=A11,B$11:B$43),0)=ROW(B$11:B$43)-ROW(B$11)+1,1,NA()))))+1

 

[silverlucky5]

Thank you Peter...I was using Aladin's formula and I was going to figure out how to make it work with the "2nd grouping"....but your new formula does it for me....Can't thank you both enough...you have saved me an inordinate amount of time as the spreadsheet I am working with is close to 200K rows!

 

[Peter_SSs]

Glad to help.