Urgent! Help! duplicate MATCH
 

I have a column of numbers in which the same may be repeated (they were found
using the LARGE function from a larger list). In the next column I use MATCH
to find the position of each number in the larger list. but I get the same
number for the duplicate numbers. I want MATCH to find their corresponding
positions in the larger list. here is a sample list:

84.16666667 332
79.16666667 364
78.33333333 203
78.33333333 203
77.91666667 125
75.83333333 46
74.16666667 123
74.16666667 123
74.16666667 123
74.16666667 123

As you see, the last 4 numbers are the same, but in reality they belong to
different postions in a previous larger list. 123 is just the first position.
why is it repeating the same position for all others?
I want a formula to give me the corresponding positions of these in the
larger list.

Or, if you have any other solution.
Thanks anyone who answers.

Urgent! Help! duplicate MATCH
 

Try something like this example:

With
The large list in G1:G40
The culled list in A1:A5

This ARRAY FORMULA* returns the position of the nth item in A1, where "n" is
the instance number of the value in Col_A

B1: =SMALL(IF($G1:$G40=A1,ROW($G1:$G40 ),10^99),COUNTIF($A$1:A1,A1))

Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

Copy B1 into B2 and down as far as you need.

Adjust range references to suit your situation.

If your data lists start on Row_2:
The large list in G2:G40
The culled list in A2:A5

Try this variation of that ARRAY FORMULA, which adjusts automatically:
B2:
=SMALL(IF($G$2:$G$40=A2,ROW($G$2:$G$40)-ROW($G$2)+1,10^99),COUNTIF($A$1:A2,A2))

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Berj" wrote:

I have a column of numbers in which the same may be repeated (they were found
using the LARGE function from a larger list). In the next column I use MATCH
to find the position of each number in the larger list. but I get the same
number for the duplicate numbers. I want MATCH to find their corresponding
positions in the larger list. here is a sample list:

84.16666667 332
79.16666667 364
78.33333333 203
78.33333333 203
77.91666667 125
75.83333333 46
74.16666667 123
74.16666667 123
74.16666667 123
74.16666667 123

As you see, the last 4 numbers are the same, but in reality they belong to
different postions in a previous larger list. 123 is just the first position.
why is it repeating the same position for all others?
I want a formula to give me the corresponding positions of these in the
larger list.

Or, if you have any other solution.
Thanks anyone who answers.

Urgent! Help! duplicate MATCH
 

Thanks Ron,
I tried it. It works fine. But for some values it gives 1E+99
Namely, when the same numbers repeat, the position of the last one gives 1E+99

Thanks again Ron for your quick reply

Berj

Urgent! Help! duplicate MATCH
 

Dear Ron (again),

Sorry for my previous note. Your second more general formula works just fine.

Thanks man for assisting me

Berj