# match and arrsum problem

#### oddworld

##### Board Regular
hi all i ahve a table with ranks in colum "e", each row has will have a number one in either column "h" through to "L"
i am trying to look at colum "e" count the rank that the number one in any of the columns "h-l" and summarise the data on sheet "summary"
i can not fo the life of me figure out the "C=MATCH or IF ARRSUM" part of the below code.
if anyone has any advice it would be appreciated.
Dim total(20, 4) As String
Dim row As Integer, c As Integer

total(1, 0) = "PTE"
total(2, 0) = "CFN"
total(3, 0) = "SPR"
toatl(4, 0) = "LCPL"
toatl(5, 0) = "LCPL"
toatl(6, 0) = "LBDR"
toatl(7, 0) = "CPL"
toatl(8, 0) = "BDR"
toatl(9, 0) = "SGT"
toatl(10, 0) = "WO2"
toatl(11, 0) = "WO1"
toatl(12, 0) = "LT"
toatl(13, 0) = "CAPT"
toatl(14, 0) = "MAJ"
toatl(15, 0) = "LTCOL"

For row = 1 To Range("e65000").End(xlUp).row
c = match (1,"c"& row&"L" & row)
Select Case Cells("e" & row)

Case "PTE", "CFN", "SPR"
total(1, c) = toatal(1, c) + 1
Case "LCPL", "LBDR"
total(2, c) = toatal(2, c) + 1
Case "BDR", "CPL"
total(3, c) = toatal(3, c) + 1
Case "SGT"
total(4, c) = toatal(4, c) + 1
Case "WO2"
total(5, c) = toatal(5, c) + 1
Case "WO1"
total(6, c) = toatal(6, c) + 1
Case "LT"
total(7, c) = toatal(7, c) + 1
Case "CAPT"
total(8, c) = toatal(8, c) + 1
Case "MAJ"
total(9, c) = toatal(9, c) + 1
Case "LTCOL"
total(10, c) = toatal(10, c) + 1

End Select
Next row

row = 40
For Rank = 1 To 20

IF ARRSUM (TOTAL(RANK))>0
Sheets("summary").Cells(ro, 1) = toatl(Rank, 0)
for svc = 1-4
Sheets
("summary").cells(row,svc+1)=total(rank,svc)
Next svc

End If

### Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

#### acw

##### MrExcel MVP
Hi

1) c=match...
the variable c gets the result of the match function. It is getting the place on row i, between columns C and L of the value 1.

2) Is there some code somewhere that has a function called ARRSUM? Or is it an external addin? My guess is that it is summing the values in the array TOTAL, for the nominated RANK.

So if RANK is 1 (first iteration of the loop), then it would sum total(1,1),total(1,2)....total(1,?) and if the result is >0, then it outputs the value of total(1,0) to Summary!A40.

HTH

Tony

Replies
1
Views
102
Replies
2
Views
173
Replies
1
Views
267
Replies
1
Views
232
Replies
0
Views
76

### Forum statistics

1,141,587
Messages
5,707,236
Members
421,498
Latest member
matinebi ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back