How to Search, Count, Match and Post Values

**vincentws** · 08-15-2006, 04:25 PM

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

**Excelenator** · 08-15-2006, 05:47 PM

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.

Please Login or Register  to view this content.

Biff · 08-15-2006, 06:15 PM

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" <vincentws.2clm05_1155673813.977@excelforum-nospam.com> wrote in
message news:vincentws.2clm05_1155673813.977@excelforum-nospam.com...
>
> 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
>

Biff · 08-15-2006, 06:45 PM

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" <Excelenator.2clppj_1155678613.744@excelforum-nospam.com>
wrote in message
news:Excelenator.2clppj_1155678613.744@excelforum-nospam.com...
>
> 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
>