Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
almost have it... need help with last bit of it.
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. -- Thank You in advance, Zab |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
almost have it... need help with last bit of it.
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" wrote: 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. -- Thank You in advance, Zab |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
almost have it... need help with last bit of it.
thanks Billy, i will see what i can do with this.
-zab "Billy Liddel" wrote: 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" wrote: 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. -- Thank You in advance, Zab |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
almost have it... need help with last bit of it.
Zab
Your welcome Peter "Zab" wrote: thanks Billy, i will see what i can do with this. -zab "Billy Liddel" wrote: 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" wrote: 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. -- Thank You in advance, Zab |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|