Solved: Comparing Array contents?

[cyclechris]

Hi, I've got 3 arrays:
index coordinates value

each one represents a record i.e.
index(0) = 1, coordinates(0) = 47,56 , value(0) = 10

what I want to do is loop through and for each equal coordinate, keep (or make note of) the one with the higher value.

so for a data set that looks like:
1 47,56 10
2 47,56 9
3 47,56 11
4 48,57 5
5 48,57 5

I would end up with
3 47,56 11
4 48,57 5 (this could be index 4 or 5 doesn't matter if values are equal)

I figured for a "hit" I would just store the corresponding index number to a results array. Anyway I am staring at my code, it occurs to me that this should not be that hard, but for some reason I am really having a tough time fleshing it out. Any ideas?

[mikerickson]

If 1 is in A1, "47,56" in B1 and 10 in C1

The formula =MAX(IF(B1:B10="47,56", C1:C10)) will return 11

Once you extract the unique elements of column B, this formula will bring the values you want.

[cyclechris]

Thanks Mike,

Well I'm actually working with arrays that I got by pulling the contents of columns in, but if there's a way to do this with inline formulas I'll be happy to do that!

With that in mind let's say I have a worksheet like this

colA(coord) colB(value) colC(index) colD(highest)
47,56 --------5 --------- 1--------- x
47.56 --------4 --------- 2
47,56 --------4 ----------3
47,56 --------3 --------- 4
48,56 --------5 ----------5
49.56 --------4 ---------- 6
50,56 -------- 4 ----------7
50,56 -------- 3 --------- 8 -------- x

Would I put something like this in D1 to get those flags in column D?
=IF(D1 = MAX(IF(A$1:A$10=A1,C$1:C$10)),"x","")

In case of 2 with the same ColB values, I could pick either, but would only want to flag one.

[cyclechris]

OK, so I tried that and it looks like it is referencing the max value for the whole range defined, instead of just between rows with the same coordinates. so I would only want the max value between rows with the same coordinates.

Alternately if you or anyone has an idea how I could step through the arrays in vba and figure that out, either one would help!

[cyclechris]

Here's what I was thinking while doing it in VB code:

Sub testLogic()

Dim index(13) As Variant
Dim coord(13) As Variant
Dim dups(13) As Variant
Dim tVAL(13) As Variant

Dim i As Long

Dim rIndex() As Variant
Dim rCoord() As Variant
Dim rDups() As Variant
Dim rtVal() As Variant

index(0) = "0"
index(1) = "1"
index(2) = "2"
index(3) = "3"
index(4) = "4"
index(5) = "5"
index(6) = "6"
index(7) = "7"
index(8) = "8"
index(9) = "9"
index(10) = "10"
index(11) = "11"
index(12) = "12"
index(13) = "13"

coord(0) = "1,57"
coord(1) = "1,57"
coord(2) = "1,57"
coord(3) = "1,58"
coord(4) = "1,59"
coord(5) = "1,60"
coord(6) = "1,61"
coord(7) = "1,61"
coord(8) = "1,62"
coord(9) = "1,63"
coord(10) = "1,64"
coord(11) = "1,65"
coord(12) = "1,65"
coord(13) = "1,65"

dups(0) = "x"
dups(1) = "x"
dups(2) = "x"
dups(3) = ""
dups(4) = ""
dups(5) = ""
dups(6) = "x"
dups(7) = "x"
dups(8) = ""
dups(9) = ""
dups(10) = ""
dups(11) = "x"
dups(12) = "x"
dups(13) = "x"

tVAL(0) = "5"
tVAL(1) = "5"
tVAL(2) = "4.5"
tVAL(3) = "5"
tVAL(4) = "5"
tVAL(5) = "5"
tVAL(6) = "3"
tVAL(7) = "4.5"
tVAL(8) = "5"
tVAL(9) = "5"
tVAL(10) = "4.5"
tVAL(11) = "3.4"
tVAL(12) = "5"
tVAL(13) = "5"

For i = 0 To UBound(dups) ' LOOP THROUGH TO SAVE A SUBSET OF DUPS STORED IN rDups
If dups(i) = "x" Then
If IsBounded(rDups) Then
ReDim Preserve rDups(0 To UBound(rDups) + 1)
rDups(UBound(rDups)) = i
Else
ReDim Preserve rDups(0)
rDups(0) = i
End If
End If
Next i

For i = 0 To UBound(rDups) ' LOOP THROUGH DUPS TO GET ALL DUP COORDS STORED IN rCoord
If IsBounded(rCoord) Then
ReDim Preserve rCoord(0 To UBound(rCoord) + 1)
rCoord(UBound(rCoord)) = coord(CLng(rDups(i)))
Else
ReDim Preserve rCoord(0)
rCoord(0) = coord(CLng(rDups(i)))
End If
Next i

For i = 0 To UBound(rDups) ' LOOP THROUGH DUPS TO GET ALL DUP'S tVALS STORED IN rtVAL
If IsBounded(rtVal) Then
ReDim Preserve rtVal(0 To UBound(rtVal) + 1)
rtVal(UBound(rtVal)) = tVAL(CLng(rDups(i)))
Else
ReDim Preserve rtVal(0)
rtVal(0) = tVAL(CLng(rDups(i)))
End If
Next i

' NOW WALK THROUGH COORDINATES rCoord AND FOR EVERY COORD THAT IS EQUAL, STORE THE INDEX NUMBER OF THE ONE WITH THE HIGHER rtVAL
' EITHER ONE IF THEY ARE THE SAME, SO FROM THE DATA ABOVE I SHOULD BE STORING THESE INDEX VALUES:
' 0 (or 1)
' 7
' 12 (or 13)

End Sub

[snb]

=ADDRESS(MATCH(MAX((B1:B5=B1)*(C1:C5)),C1:C5),1)

Array formula, so enter with ctrl-shft-enter

[cyclechris]

Thanks snb, OK so plugging that in, first I fixed the ranges like this: =ADDRESS(MATCH(MAX((B$1:B$5=B1)*(C$1:C$5)),C$1:C$5),1)
So I could drag the fomula down in D in the following table
http://www.yeowzers.com/images/table.jpg

And did use ctrl-shift-enter but the results are puzzling to me. I am trying to determine, for each duplicate, which one has the higher value in col C, or if the values are the same, just pick either, by putting an x in D.

[snb]

In D1:

=ADDRESS(MATCH(MAX((B1:B5=B1)*(C1:C5)),C1:C5),1)
In D2
=ADDRESS(MATCH(MAX((B1:B5=B2)*(C1:C5)),C1:C5),1)in D3
=ADDRESS(MATCH(MAX((B1:B5=B3)*(C1:C5)),C1:C5),1)
etc.

[cyclechris]

Thanks snb, as you can see from the picture below, that's what I've done here, with the dollar signs, so when I drag the formula onto other cells, only the cell we are checking against the range changes. However, what the output is in column D doesn't tell me anything about which of the duplicate coord values from column B has the higher value in column C, in comparing only to those with the same coordinate value.

http://www.yeowzers.com/images/table2.jpg

So what I would want this sheet to look like is:
http://www.yeowzers.com/images/table3.jpg

I changed one of the values (in C2) to show that what I am looking to do is mark the coordinate row with the highest value in column C, or if they are the same, just pick the first one. However, it can be the second occurrence, or it can be to mark the lowest values if that makes any difference in the complexity of the formula or code required.

[Bob Phillips]

Try this array formula

=IF(C1=MAX(IF(B1=$B$1:$B$13,$C$1:$C$13)),"x","")

[cyclechris]

Ah thanks for all the suggestions! I figured it out. You know I had sorted the data by the X and Y coordinates, to group the duplicates, prior to running everything, but it never occurred to me to add a level to the sort and then sort by values descending! Then all I had to do was step through in my code and grab the first or each group. That way I get one even if they're the same AND the first one is also always going to be the highest!

D-UH! I can't believe I spent so many hours trying to figure out how to look ahead and behind an indeterminate number of members, testing and re-saving in other variables, etc. Man my code was becoming more of a mess than usual! Here's the mess of a function I ended up using, which did the trick:
Function markDupsForRemoval(index() As Variant, coord() As Variant, dups() As Variant, daq() As Variant) As Variant ' RETURNS A LIST OF INDEXES/ROWS TO BE SAVED
Dim i As Long
Dim j As Long
Dim n As Long
Dim resIndex() As Variant
Dim resDups() As Variant
Dim resCoords() As Variant
Dim resDAQ() As Variant

For i = 0 To UBound(index) ' LOOP THROUGH TO SAVE A SUBSET OF DUPS
If dups(i) = "x" Then
If IsBounded(resDups) Then
ReDim Preserve resDups(0 To UBound(resDups) + 1)
resDups(UBound(resDups)) = i
Else
ReDim Preserve resDups(0)
resDups(0) = i
End If
End If
Next i

'For i = 0 To UBound(resDups)
' MsgBox "resDup " & i & " = " & resDups(i)
'Next i

For i = 0 To UBound(resDups) ' LOOP THROUGH DUPS TO GET ALL DUP COORDS
If IsBounded(resCoords) Then
ReDim Preserve resCoords(0 To UBound(resCoords) + 1)
resCoords(UBound(resCoords)) = coord(CLng(resDups(i)))
Else
ReDim Preserve resCoords(0)
resCoords(0) = coord(CLng(resDups(i)))
End If
Next i

For i = 0 To UBound(resDups) ' LOOP THROUGH DUPS TO GET ALL DUP'S DAQS
If IsBounded(resDAQ) Then
ReDim Preserve resDAQ(0 To UBound(resDAQ) + 1)
resDAQ(UBound(resDAQ)) = daq(CLng(resDups(i)))
Else
ReDim Preserve resDAQ(0)
resDAQ(0) = daq(CLng(resDups(i)))
End If
Next i

Dim arrHI() As Variant
Dim jlast As Long
Dim ckCoord As Variant
Dim bIn As Boolean
j = 0
bIn = False

ckCoord = resCoords(0)
For i = 0 To UBound(resCoords) ' LOOP THROUGH DUPS TO GET ALL DUP'S DAQS
If resCoords(i) = ckCoord Then ' SAME COORD GRAB FIRST DAQ VALUE INDEX, SINCE SORTED DESCENDING
If j = 0 Then
If Not bIn Then
ReDim Preserve arrHI(j)
arrHI(j) = resDups(i)
j = j + 1
bIn = True
End If
Else
If Not bIn Then
ReDim Preserve arrHI(0 To UBound(arrHI) + 1)
arrHI(j) = resDups(i - 1)
j = j + 1
bIn = True
End If
End If
Else ' DIFFERENT COORDS UPDATE CHECK VALUE
ckCoord = resCoords(i)
bIn = False
End If
Next i

For i = LBound(index) To UBound(index)

Next i

markDupsForRemoval = arrHI

Erase resDups
Erase resCoords
Erase resDAQ

End Function

[Bob Phillips]

Did you try my suggestion, it avoids sorts completely.

[cyclechris]

Hi, I did try it, but it did not do what I needed it to do. Take a look at the picture below:
http://www.yeowzers.com/images/table4.jpg
As I explained in earlier posts, I was looking to highlight either the top value of each set of duplicates, or highlight everything but the top values, for each set of duplicates. This does not do that, it evaluates all values against the very top value, not the top value for each set of duplicate coords.

[shrivallabha]

Hi, I did try it, but it did not do what I needed it to do. Take a look at the picture below:
http://www.yeowzers.com/images/table4.jpg
As I explained in earlier posts, I was looking to highlight either the top value of each set of duplicates, or highlight everything but the top values, for each set of duplicates. This does not do that, it evaluates all values against the very top value, not the top value for each set of duplicate coords.
It looks as if you want to discount the values which are appearing only once. So this is added condition on Bob's formula:

=IF(COUNTIF($B$1:$B$13,B1)>1,IF(C1=MAX(IF(B1=$B$1:$B$13,$C$1:$C$13)),"x",""),"")