almost have it... need help with last bit of it.

[Zab]

i am currently using:
=MATCH(FIXED(B11,0,TRUE),LEFT('[Insulspan Schedule
CURRENT.xls]SCHEDULE'!$F$1:$F$400,4),0)

to find the row number in which column B value is located in another
workbook. this works great. the only thing that i need it to do is find the
next occurence if there is a duplicated value in column B. right now if there
is a duplicated value it returns the fisrt instance for all.
this function is in column A looking at the value in column B.

 

[Billy Liddel]

Zab
That what MATCH does, Metch(ref,Array,1) finds the last value providing the
array is sorted. if the array is unsorted then only the first match can be
listed.

You might need to have a loop in code - the following works after a fashion
if the array workbook has been saved: It lists 1, 4, 7 on the following data:

Ref: a

a
b
c
a
b
c
a
b
c

entered as:
=listmatch(A6,'C:\Documents and Settings\Peter\My
Documents\[Book2a.xls]Sheet1'!$A$1:$A$9)

Function code:

Function ListMatch(ref, data As Variant) 'As String
Dim i As Integer, x As Integer, count As Integer
x = data.count
For i = 1 To x
If data(i) = ref Then
count = count + 1
If count = 1 Then
ListMatch = i
count = count + 1
Else
ListMatch = ListMatch & ", " & i
End If
End If
Next

end sub

Perhaps you can use is as a workaround.
Peter

 

[Zab]

thanks Billy, i will see what i can do with this.
-zab

Zab
That what MATCH does, Metch(ref,Array,1) finds the last value providing the
array is sorted. if the array is unsorted then only the first match can be
listed.

You might need to have a loop in code - the following works after a fashion
if the array workbook has been saved: It lists 1, 4, 7 on the following data:

Ref: a

a
b
c
a
b
c
a
b
c

entered as:
=listmatch(A6,'C:\Documents and Settings\Peter\My
Documents\[Book2a.xls]Sheet1'!$A$1:$A$9)

Function code:

Function ListMatch(ref, data As Variant) 'As String
Dim i As Integer, x As Integer, count As Integer
x = data.count
For i = 1 To x
If data(i) = ref Then
count = count + 1
If count = 1 Then
ListMatch = i
count = count + 1
Else
ListMatch = ListMatch & ", " & i
End If
End If
Next

end sub

Perhaps you can use is as a workaround.
Peter

i am currently using:
=MATCH(FIXED(B11,0,TRUE),LEFT('[Insulspan Schedule
CURRENT.xls]SCHEDULE'!$F$1:$F$400,4),0)

to find the row number in which column B value is located in another
workbook. this works great. the only thing that i need it to do is find the
next occurence if there is a duplicated value in column B. right now if there
is a duplicated value it returns the fisrt instance for all.
this function is in column A looking at the value in column B.

 

[Billy Liddel]

Zab

Your welcome

Peter

thanks Billy, i will see what i can do with this.
-zab

Zab
That what MATCH does, Metch(ref,Array,1) finds the last value providing the
array is sorted. if the array is unsorted then only the first match can be
listed.

You might need to have a loop in code - the following works after a fashion
if the array workbook has been saved: It lists 1, 4, 7 on the following data:

Ref: a

a
b
c
a
b
c
a
b
c

entered as:
=listmatch(A6,'C:\Documents and Settings\Peter\My
Documents\[Book2a.xls]Sheet1'!$A$1:$A$9)

Function code:

Function ListMatch(ref, data As Variant) 'As String
Dim i As Integer, x As Integer, count As Integer
x = data.count
For i = 1 To x
If data(i) = ref Then
count = count + 1
If count = 1 Then
ListMatch = i
count = count + 1
Else
ListMatch = ListMatch & ", " & i
End If
End If
Next

end sub

Perhaps you can use is as a workaround.
Peter

i am currently using:
=MATCH(FIXED(B11,0,TRUE),LEFT('[Insulspan Schedule
CURRENT.xls]SCHEDULE'!$F$1:$F$400,4),0)

to find the row number in which column B value is located in another
workbook. this works great. the only thing that i need it to do is find the
next occurence if there is a duplicated value in column B. right now if there
is a duplicated value it returns the fisrt instance for all.
this function is in column A looking at the value in column B.