Index/Match

Hi Scott,

I really hope someone proves me wrong, but I've been monitoring this board for a while now and I've seen this question come up in various scenarios. It comes down to find the nth iteration of a duplicate.

Most functions in Excel are designed to find the first instance, all others being considered invalid duplicates.

In your case, I would strongly recommend redesigning the database. You need to key on a unique identifier. earlier this week, another poster wrote about a similiar problem--two vendors using the same part number for totally different items. The logical design in that case would have been to key on Vendor AND ProductCode. In your case, the design is confusing. though I appreciate that it is possible to have 3 brands of hammers or 3 quality grades of hammer or whatever, designing the data on this non-unique description is not helping you.

As I mentioned, I have been monitoring this board for while and have yet to see this resolved.

Sorry,
Kevin

Solution, but caution

Scott,

I designed a spreadsheet to illustrate how to find the nth instance of record just because of the recurring requests. While it works, it also illustrates the inherent dangers of poor database design. Look at both tabs.

You can download it at:
(broken link)

The file is: InstanceFinder.xls

Kevin

Re: Solution, but caution

But look what happens if you change A1:A4(on your sheet "Sorted") to Alan instead of Bart

Re: Solution, but caution

Here's an alternative way :-

Using the sample data in your workbook, enter in C1 and fill down to C7 :-
=IF(A1=$G$14,B1,"")

Select cells D1:D7 and array enter (Ctrl+Shift+Enter) :-
=IF(ISERR(SMALL(IF($C$1:$C$10<>"",ROW(INDIRECT("1:"&ROWS($C$1:$C$10)))),ROW(INDIRECT("1:"&ROWS($C$1:$C$10))))),"",INDEX($C$1:$C$10,SMALL(IF($C$1:$C$10<>"",ROW(INDIRECT("1:"&ROWS($C$1:$C$10)))),ROW(INDIRECT("1:"&ROWS($C$1:$C$10))))))

Column C can be kept hidden.

Easier way

Use autofilter to filter by "Bart" and then select Column B visible cells only and copy/paste to another column.
Also, a macro could be written to do this.

Scott, how about a custom function.

Hi Scott

As you have been shown you can use a mega array for this, but try this Custom function.

Function FindNth(rTable As Range, vVal1 As Variant, _
vVal2 As Variant, iv2LookinCol As Integer, _
RetrnCol As Integer, iOccurence As Integer)

'Written by OzGrid Business Applications
'www.ozgrid.com

Dim i As Integer
Dim iCount As Integer
Dim rCol As Range

For i = 1 To rTable.Rows.Count

If rTable.Cells(i, 1) = vVal1 And _
rTable.Cells(i, iv2LookinCol) = vVal2 Then
iCount = iCount + 1
End If

If iCount = iOccurence Then
FindNth = rTable.Cells(i, RetrnCol)
Exit For
End If

Next i

End Function

...To use it, push Alt+F11 then go to Insert>Module and paste it in. Push Alt+Q to return to Excel and save.

Now put this in any cell:
=FindNth(A1:C4,"hammers",10,3,2,2)

Where A1:C4 is the entire table.

"hammers" is the value to find in column 1 of A1:C4

10 is the second value to find.

3 is the Column in A1:C4 to find 10

2 is the Column in A1:C4 to return the result from.

2 is the occurence number of "hammers".

You can see this better if you push Shift+F3 and scroll down to "User defined" and then select "FindNth".

If you run into any problems let me know.

Dave

OzGrid Business Applications

CM, hi

My goodness, I hope you didn't get a brain cramp over that.

I realized that the suggestion had inherent flaws. But given Scott's real situation, lets hope he's not changing "hammer" to "M.C. Hammer"

I'm impressed that you worked so hard for a solution. What really needs to be done here is for the user to redesign their database. One problem I've had with many messages left here is that we (solution posters) keep trying to make sense out of bad data. Personally, I wasn't willing to work that hard.

Take care