How to get unique sorted indices of a list?

[JenniferMurphy]

Is there a way to get a set of unique (integer) indices into a list of numbers with no duplicates?

In this minisheet, the Values row has the list of numbers. There are duplicates. There are 2 2's and 3 3's.

The Rank.Eq row has the sorted indices, but there are duplicates. The Rank.Avg has the sorted indices, but there are fractions and duplicates. The Solution row has the indices I would like to get, but I would also be happy with the alternate indces on the next row. Is there a way to get it?

Sorting.xlsx
	B	C	D	E	F	G	H	I	J
8	Values	3	6	2	3	1	2	8	3
9	Rank.Eq	3	2	6	3	8	6	1	3
10	Rank.Avg	4	2	6.5	4	8	6.5	1	4
11	Solution	4	7	2	5	1	3	8	6
12		/5/6		/3	/4/6		/2		/4/5
Sheet2

Cell Formulas
Range	Formula
C9:J9	C9	=RANK.EQ(Values,Values)
C10:J10	C10	=RANK.AVG(Values,Values)

Named Ranges
Name	Refers To	Cells
Values	=Sheet2!$C$8:$J$8	C9:J10

Thanks

 

[kevin9999]

One suggestion Jennifer, a bit clunky but seems to match your desired result.

Book1
	B	C	D	E	F	G	H	I	J
8	Values	3	6	2	3	1	2	8	3
9	Rank.Eq	3	2	6	3	8	6	1	3
10	Rank.Avg	4	2	6.5	4	8	6.5	1	4
11
12	Solution	4	7	2	5	1	3	8	6
13	Solution	4	7	2	5	1	3	8	6
14		/5/6		/3	/4/6		/2		/4/5
Sheet2

Cell Formulas
Range	Formula
C13:J13	C13	=RANK(C8,Values,1)+COUNTIF($C$8:C8,C8)-1

Named Ranges
Name	Refers To	Cells
Values	=Sheet2!$C$8:$J$8	C13:J13

 

[JenniferMurphy]

One suggestion Jennifer, a bit clunky but seems to match your desired result.

Hey, clunky is good if it works and anything more elegant is not available.

Thank you

I updated it to make use of the named range, but I couldn't figure out how to replace that $C$8.

Sorting.xlsx
	B	C	D	E	F	G	H	I	J
8	Values	3	6	2	3	1	2	8	3
9	Rank.Eq	3	2	6	3	8	6	1	3
10	Rank.Avg	4	2	6.5	4	8	6.5	1	4
11	Solution	4	7	2	5	1	3	8	6
12		/5/6		/3	/4/6		/2		/4/5
13	kevin	4	7	2	5	1	3	8	6
14	kevin'	4	7	2	5	1	3	8	6
Sheet2

Cell Formulas
Range	Formula
C9:J9	C9	=RANK.EQ(Values,Values)
C10:J10	C10	=RANK.AVG(Values,Values)
C13:J13	C13	=RANK(C8,Values,1)+COUNTIF($C$8:C8,C8)-1
C14:J14	C14	=RANK.EQ(@Values,Values,1)+COUNTIFS($C$8:@Values,@Values)-1

Named Ranges
Name	Refers To	Cells
Values	=Sheet2!$C$8:$J$8	C13:J14, C9:J10

 

[kevin9999]

Thanks Jennifer, glad it worked for you

 

[JenniferMurphy]

I'm sorry. I guess I have my head on backwards. What I need is list of indices into the Values list that will let me access them in ascending order. I want to be able to step through the Solution row and access the value in the Values row at that index. This values I will access will be in the order in the Result row. I will step through the Solution row using each index to access a value in the Values row. I will first select Solution(1)=5 and use it to access Values(5)=1. Then I'll select Solution(2=3 and use that to access Values(3)=2. Does that make any sense?

I will be doing this in VBA, so the Values will be an array and I can use the Solutions number as indices.

Sorry for the confusion.

Sorting.xlsx
	B	C	D	E	F	G	H	I	J
7	Index	1	2	3	4	5	6	7	8
8	Values	3	6	2	3	1	2	8	3
9	Rank.Eq	3	2	6	3	8	6	1	3
10	Rank.Avg	4	2	6.5	4	8	6.5	1	4
11	Solution	5	3	6	1	4	8	2	7
12	Result	1	2	2	3	3	3	6	8
Sheet2

Cell Formulas
Range	Formula
C9:J9	C9	=RANK.EQ(Values,Values,0)
C10:J10	C10	=RANK.AVG(Values,Values)

Named Ranges
Name	Refers To	Cells
Values	=Sheet2!$C$8:$J$8	C9:J10

 

[kevin9999]

What I need is list of indices into the Values list

How do you currently get those values?

I think I'm starting to lose track of what it is exactly that you're looking for Jennifer. The way you describe the VBA part of your project is straightforward enough and I know well within your abilities - so is it purely a means of generating the Values list you're after? If so, I'm not sure what exactly they're representing

 

[JenniferMurphy]

How do you currently get those values?

I think I'm starting to lose track of what it is exactly that you're looking for Jennifer. The way you describe the VBA part of your project is straightforward enough and I know well within your abilities - so is it purely a means of generating the Values list you're after? If so, I'm not sure what exactly they're representing

The values can be a few different things. It can be frequencies or tallies. I am writing some code to be used by another party. It will be their data, so I have little control over what it is. My code needs to go through their list of values in acscending order. It will calculate some probabilities. Maybe something like this.

Sorting.xlsx
	B	C	D	E	F	G	H	I	J
8	Values	3	6	2	3	1	2	8	3
9	Prob	0.071	0.004	0.177	0.071	0.427	0.177	0.000	0.071
Sheet2

I can demand that they sort the list before calling my code, but I thought it would be more general if I could handle unsorted data.

I could do the sort in my code, but I would need to write out the results in the original order.

Is that clear? I need a way to generate the Solution row below or equivalent in VBA so that stepping through it, I see the values in the Result row.

Sorting.xlsx
	B	C	D	E	F	G	H	I	J
7	Index	1	2	3	4	5	6	7	8
8	Values	3	6	2	3	1	2	8	3
9	Prob	0.071	0.004	0.177	0.071	0.427	0.177	0.000	0.071
10	Solution	5	3	6	1	4	8	2	7
11	Result	1	2	2	3	3	3	6	8
Sheet2

 

[kevin9999]

I understand what you're looking for now Jennifer, unfortunately I can't think of a solution at present. Need a clear head on this tomorrow.

 

[JenniferMurphy]

I understand what you're looking for now Jennifer, unfortunately I can't think of a solution at present. Need a clear head on this tomorrow.

OK, thanks for trying. I feel less useless is someone, like you, who came up with the other clever solution is also having difficulty.

 

[JenniferMurphy]

Here's another minisheet that might make it clearer. Row 7 is just for illustration. It contains the indices of the values. Row 8 contains the values. Row 9 contains the solution I would like to find. Row 10 shows that using Row 9, I can access the values in ascending order.

Sorting.xlsx
	B	C	D	E	F	G	H	I	J
7	Index	1	2	3	4	5	6	7	8
8	Values	3	6	2	3	1	2	8	3
9	Solution wanted	5	3	6	1	4	8	2	7
10	Access via solution	1	2	2	3	3	3	6	8
Sheet2

Cell Formulas
Range	Formula
C10:J10	C10	=INDEX(Values,1,C9)

Named Ranges
Name	Refers To	Cells
Values	=Sheet2!$C$8:$J$8	C10:J10