find 2nd instance of a duplicated value in an index/match formula?

Hi bobbo,

Can you upload a sample workbook indicating your expected results ? thanks


Regards,
DILIPandey
<click on below * if this helps>

Okay..use below formula which need to be entered with key combination: ctrl shift enter

Formula:

Please Login or Register  to view this content.

example.xlsx

Regards,
DILIPandey
<click on below * if this helps>

since dilip is offline and you are waiting.
try to hit ctrl + shift + enter not just enter you'll notice the curly braces around the formula {formula} if done correctly

Originally Posted by Bobbo Jones

I haven't understood what CTRL SHIFT enter does, ill google it now.

See this:

http://www.cpearson.com/Excel/ArrayFormulas.aspx

Ok so far this may be too cpu intensive, I have my spreadsheet set to manually calculate, but after hitting calculate it took 5 minutes to calculate 2000 records. it seems to be working smoothly once finished, ill persevere a bit more before trying it with all 14000

2 things about this formula.

It returns a result for the 1st time a track is used, as well as the 2nd. (will it for the 3rd?)
this is messy. If I already have an answer for the 1st, how can this formula return a 0 in the 2nd column instead of repeating the answer?

Also, how can this return an empty cell instead of a 0?

thanks!
Evan

What formula are you using?

Well, I was hoping you would post the formula.

If you're using an array formula then DON'T use entire columns as range references. Use smaller specific ranges.

Originally Posted by Bobbo Jones

in AF1 =INDEX($AJ:$AJ,MAX(IF($AT:$AT=$M$1,ROW($AT:$AT),""))) with ctrl shift enter

OK, that's the formula that's causing the problem.

Do not use entire columns as range references. Use smaller specific ranges.

Originally Posted by Bobbo Jones

OK I have just realised that the formula provided by dilipandey will NOT work.

This is because it returns the LAST instance of $m$1 NOT the 2nd.

I need the 2nd instance ONLY

For the 2nd instance use something like this array formula**:

=INDEX($AJ:$AJ,SMALL(IF($AT$2:$AT$100=$M$1,ROW($AT$2:$AT$100)),2))

Adjust the ranges to suit.

Note that I am not using entire columns as range references in the IF function.

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

I'm logging off for the day. I'll check back tomorrow to see how you made out.

@Tony:
i have always wanted to ask you this, but you have chosen not to receive PMs. is there a source where someone has tested or a way to find out if referring to the whole column like you suggested outside the IF portion would be faster than this?
=INDEX($AJ$2:$AJ$100,SMALL(IF($AT$2:$AT$100=$M$1,ROW($AT$2:$AT$100)-ROW($AJ$2)+1),2))

i also played around with non-array ways of doing, but never got to verify which ways are faster

hope the moderators are ok with me asking this here as this is related to OP's performance issue

I have tested those variations of the formulas which is what led me to start referring to the entire column in the INDEX function.

I used to do it the same way as everyone else:

=INDEX($AJ$2:$AJ$100,SMALL(IF($AT$2:$AT$100=$M$1,ROW($AT$2:$AT$100)-ROW($AJ$2)+1),n))

But then I got curious and started experimenting.

If you index the entire column there is no need to calculate an "offset correction". In my testing, indexing the entire column had no negative effect on performance.

Try this...

If you don't like indexing the entire column then at least move the offset correction outside the SMALL function.

Using this syntax:

=INDEX($AJ$2:$AJ$100,SMALL(IF($AT$2:$AT$100=$M$1,ROW($AT$2:$AT$100)-ROW($AJ$2)+1),n))

You calculate an ARRAY of offset corrections:

ROW($AT$2:$AT$100)-ROW($AJ$2)+1

That's not necessary. If you put the offset correction outside the SMALL function then you calculate just a SINGLE offset correction:

=INDEX($AJ$2:$AJ$100,SMALL(IF($AT$2:$AT$100=$M$1,ROW($AT$2:$AT$100)),n)-ROW($AJ$2)+1)

Wrap the formula inside the IFERROR function:

To return a blank:

=IFERROR(INDEX($AJ:$AJ,SMALL(IF($AT$2:$AT$100=$M$1,ROW($AT$2:$AT$100)),2)),"")

To return 0:

=IFERROR(INDEX($AJ:$AJ,SMALL(IF($AT$2:$AT$100=$M$1,ROW($AT$2:$AT$100)),2)),0)

Still array entered.

Originally Posted by benishiryo

@Tony:
i have always wanted to ask you this, but you have chosen not to receive PMs.

From past experience at other forums...

I was getting too many PMs asking for help so I had to disable PMs.

If you're wanting to find the 1st instance then the INDEX/MATCH version is more efficient.

You're welcome!