How to Search, Count, Match and Post Values
 

Greetings All,

I am new to this forum, so excuse me if I stumble. I must find a way
to accomplish the following: Search below a cell for the next matching
cell, and post all of the "unique" values included in the search,
including the matched value, post these adjacent to the referenced
cell. For example, given the following row:

2 - (This is my reference cell, the one I want to match)
6
5
5
7
4
3
3
2 - (this is my matched cell)

In the example above, I passed the values 6,5,7,4 and 3 before finding
the match of 2. I would like to be able to post in the cell directly
to the right of the reference cell the values 6,5,7,4,3,2 (yes I need
to include the referenced value). I could also accept a cumulative
total of unique values in the adjacent cell. In this case that value
would be 6 (6 unique values - I do not want a total of values, which
would be 8, because some values are repeats like 5 and 3). The
cumulative total method would be preferable actually.

I would then need to copy the formula to each cell below, so that it
would in turn likewise. So after the 2 above, the formula would work
on the 6 then 5 then 5 etc. I would simply drag the formula downward.
So assuming the example cells above were in column A, the formula would
reside in column B.

I am not expecting the answer here, (unless it is simple), but I
appreciate any suggestions.

Thank you,

Vincent


--
vincentws
------------------------------------------------------------------------
vincentws's Profile: http://www.excelforum.com/member.php...o&userid=37563
View this thread: http://www.excelforum.com/showthread...hreadid=571944

How to Search, Count, Match and Post Values
 

This may not be the most elegant solution but here goes. If you paste
the function below into a module in your workbook you can use this
function in column B.


Code:
--------------------
Private Function MatchDupes(ByVal c As Range) As String
Dim a() As Integer
Dim i As Integer
Dim n As Integer
Dim x As Integer

i = 1
x = 0

Do While c.Value < c.Offset(i, 0).Value
If c.Offset(i, 0).Value < "" Then
Dim notDupe As Boolean
notDupe = True
If i < 1 Then
For n = 0 To UBound(a)
If c.Offset(i, 0).Value = a(n) Then
notDupe = False
Exit For
End If
Next n
If notDupe Then
ReDim Preserve a(x)
a(x) = c.Offset(i, 0).Value
x = x + 1
End If

Else
ReDim Preserve a(x)
a(x) = c.Offset(i, 0).Value
x = x + 1
End If
i = i + 1
Else
Exit Do
End If
Loop

If c.Value = c.Offset(i, 0).Value Then
ReDim Preserve a(x)
a(x) = c.Offset(i, 0).Value
End If


If i = 1 Then
MatchDupes = c.Offset(i, 0).Value
Else
Dim s As String
For n = 0 To UBound(a)
If n = 0 Then
s = a(n)
Else
s = s & ", " & a(n)
End If
Next n
MatchDupes = s
End If
End Function

--------------------


--
Excelenator


------------------------------------------------------------------------
Excelenator's Profile: http://www.excelforum.com/member.php...o&userid=36768
View this thread: http://www.excelforum.com/showthread...hreadid=571944

How to Search, Count, Match and Post Values
 

Here's a formula that returns the unique count:

Entered as an array using the key combination of CTRL,SHIFT,ENTER:

Assuming your range is A2:A10 and there are no empty cells within that
range.

=IF(COUNTIF(A2:A$10,A2)=1,0,SUM(1/COUNTIF(A2:INDEX(A2:A$10,MATCH(A2,A3:A$10,0)+1),A2 :INDEX(A2:A$10,MATCH(A2,A3:A$10,0)+1))))

Copy down as needed.

Based on your sample, returns:

6;0;1;0;0;0;1;0;0

Biff

"vincentws" wrote in
message ...

Greetings All,

I am new to this forum, so excuse me if I stumble. I must find a way
to accomplish the following: Search below a cell for the next matching
cell, and post all of the "unique" values included in the search,
including the matched value, post these adjacent to the referenced
cell. For example, given the following row:

2 - (This is my reference cell, the one I want to match)
6
5
5
7
4
3
3
2 - (this is my matched cell)

In the example above, I passed the values 6,5,7,4 and 3 before finding
the match of 2. I would like to be able to post in the cell directly
to the right of the reference cell the values 6,5,7,4,3,2 (yes I need
to include the referenced value). I could also accept a cumulative
total of unique values in the adjacent cell. In this case that value
would be 6 (6 unique values - I do not want a total of values, which
would be 8, because some values are repeats like 5 and 3). The
cumulative total method would be preferable actually.

I would then need to copy the formula to each cell below, so that it
would in turn likewise. So after the 2 above, the formula would work
on the 6 then 5 then 5 etc. I would simply drag the formula downward.
So assuming the example cells above were in column A, the formula would
reside in column B.

I am not expecting the answer here, (unless it is simple), but I
appreciate any suggestions.

Thank you,

Vincent


--
vincentws
------------------------------------------------------------------------
vincentws's Profile:
http://www.excelforum.com/member.php...o&userid=37563
View this thread: http://www.excelforum.com/showthread...hreadid=571944

How to Search, Count, Match and Post Values
 

Needs tweaked.

If there is no matching value it still returns the uniques. For example, in
the posted sample data 6 has no match but it still returns all the uniques
below the 6 but excludes the 6 as a unique.

Biff

"Excelenator"
wrote in message
...

This may not be the most elegant solution but here goes. If you paste
the function below into a module in your workbook you can use this
function in column B.


Code:
--------------------
Private Function MatchDupes(ByVal c As Range) As String
Dim a() As Integer
Dim i As Integer
Dim n As Integer
Dim x As Integer

i = 1
x = 0

Do While c.Value < c.Offset(i, 0).Value
If c.Offset(i, 0).Value < "" Then
Dim notDupe As Boolean
notDupe = True
If i < 1 Then
For n = 0 To UBound(a)
If c.Offset(i, 0).Value = a(n) Then
notDupe = False
Exit For
End If
Next n
If notDupe Then
ReDim Preserve a(x)
a(x) = c.Offset(i, 0).Value
x = x + 1
End If

Else
ReDim Preserve a(x)
a(x) = c.Offset(i, 0).Value
x = x + 1
End If
i = i + 1
Else
Exit Do
End If
Loop

If c.Value = c.Offset(i, 0).Value Then
ReDim Preserve a(x)
a(x) = c.Offset(i, 0).Value
End If


If i = 1 Then
MatchDupes = c.Offset(i, 0).Value
Else
Dim s As String
For n = 0 To UBound(a)
If n = 0 Then
s = a(n)
Else
s = s & ", " & a(n)
End If
Next n
MatchDupes = s
End If
End Function

--------------------


--
Excelenator


------------------------------------------------------------------------
Excelenator's Profile:
http://www.excelforum.com/member.php...o&userid=36768
View this thread: http://www.excelforum.com/showthread...hreadid=571944

How to Search, Count, Match and Post Values
 

So if there is no matching value you do not want to return anything?


--
Excelenator


------------------------------------------------------------------------
Excelenator's Profile: http://www.excelforum.com/member.php...o&userid=36768
View this thread: http://www.excelforum.com/showthread...hreadid=571944