Is it possible to give the count of maximum columns used by a particular row(s)

Question

I have a Excel matrix as below:

PID#      T1    T2   T3    T4    T5   T6    T7

11        1                1      
14        1     1          1     
21                   1     1     
41        1          1     1     1
71                   1     
88        1          1           1

PID# is nothing but the processes, all the processes has been composed of multiple tasks. But it is not mandatory that all processes should use all the T1 - T5 tasks. In such a scenario is it possible to get the PID# which used maximum tasks. 1 used to indicate that a task has been used or not. here the PID# 41 and 88 used maximum tasks say it is 5. I need only the maximum used column count and any of the row# which used that number of columns.

NOTE

here i have used 1 to tell there is data,but in reality there are different types of data. I need to find out which row used maximum columns.But one thing if any cells for a row is blank and it is to the left,should be in the count. say for example --

<> 1 <> 1 gives the count as 4

<> <> 1 <> will give the count as 3

1 1 <> will give the count as 2 ' here I used <> used to represent the no values

EDIT

Option Explicit

Dim ArrayListTaskDetails : Set ArrayListTaskDetails = CreateObject("System.Collections.ArrayList")
Dim i,colcount

i=2
Do while i < = objExcel1.Application.WorksheetFunction.CountA(ob.Rows(1))

colcount=objExcel1.Application.WorksheetFunction.CountA(ob.Rows(i))
ArrayListTaskDetails.Add(colcount)

i=i+1
Loop

ArrayListTaskDetails.Sort()
i=ArrayListTaskDetails.Count
MsgBox("HighestColumnNumner:" & ArrayListTaskDetails(i-1))

Problem:

I can't count the blank columns for rows which don't have the contiguous value. Thus count is not produced by me correctly.

EDIT1

Here the problem is still i can't count the left blank cells if any,as those are also to be considered as used column,in which other rows can have values.Thus need to find out the the right most column which has been used by a row after which no columns has been used by any rows. Hope I am able to clear what I am looking for:

Option Explicit

Dim objExcel1
Dim strPathExcel1
Dim objSheet1,objWB,ColCount
Dim ArrayListTaskDetails : Set ArrayListTaskDetails = CreateObject("System.Collections.ArrayList")

Set objExcel1 = CreateObject("Excel.Application")
strPathExcel1 = "D:\AravoVB\.xlsx"
Set objWB = objExcel1.Workbooks.open(strPathExcel1)

Set objSheet1 = objExcel1.ActiveWorkbook.Worksheets(1)

Do Untill count > objExcel1.Application.WorksheetFunction.CountA(objSheet1.Rows(1)) 

Range = objSheet1.("count:count")
ColCount=objExcel1.Application.WorksheetFunction.CountIf(Range,<> "")
ArrayListTaskDetails.Add(ColCount)

Loop

ArrayListTaskDetails.Sort()
MsgBox(ArrayListTaskDetails(ArrayListTaskDetails.Count - 1))

Thanks,

@CodeLover - does that mean: As of now, all 21, 41, 71, and 88 are to be considered PIDs with max (5) tasks? — Ekkehard.Horner, Dec 31 '12 at 20:10
@Ekkehard.Horner please see my description,i just updated to show the variety! — CodeLover, Dec 31 '12 at 20:13
@Tukai it seems like you may use `CountIF()` or an `overloading version` of the method() — bonCodigo, Dec 31 '12 at 21:12
@bonCodigo see my **EDIT1** where the problem still can't left out! — CodeLover, Jan 01 '13 at 05:56
Now I recall, you asked the very similar question earlier about getting Row totals... — bonCodigo, Jan 01 '13 at 06:13
@Ekkehard.Horner Is it possible to find what i am looking for pro grammatically? — CodeLover, Jan 01 '13 at 07:04
@Tukai When someone ask you a question please do not direct them to the `EDIT` section of your question. It is easier if you could just tell them the extract or summary of it as it's too much to re-read again and again your updated texts...! Here are my questions. 1. If you have two `PID`s with same max value, do you want both of them? 2. Do you hae a `threshold` for max filter and find all `PID`s satisfy the threshold? — bonCodigo, Jan 01 '13 at 07:43
I am voting down for bad tagging. Please clarify or correct your tagging on all questions tagged both VBScript and Excel-VBA. — Fionnuala, Jan 02 '13 at 13:17
@Remou lots of effort we did here, too much concept also given.So if you down-vote it seems you loved to do so. otherwise I can't see here to down-vote! VBScript and VBA are not same but both are not too far! A VBA code can easily be converted to a VBScript if anyone want. — CodeLover, Jan 02 '13 at 13:28
If you are looking for a VBScript solution, tag with VBScript. You can add Excel as an additional tag. — Fionnuala, Jan 02 '13 at 13:42
@Remou Does `Excel` tagged users are aware that how to code excel related requirement in VBA? Does any guarantee that VBScript platform all users all able to solve excel related queries and do code? If Both answers are `NO` then How would I get help where I got stuck? — CodeLover, Jan 02 '13 at 13:46
This is a programming forum. Excel + VBScript = VBScript using Excel. — Fionnuala, Jan 02 '13 at 14:22
@Remou Leave it here!No one object,except you rather i got the suggestions from many to do tag the way I did. Otherwise people who has the solution couldn't come. You gave -1 hope you are fine, so no more discussion here for the same! — CodeLover, Jan 02 '13 at 14:25

score 1 · Answer 1 · edited May 08 '18 at 21:49

Excel is very powerful in calculating Matrix. I would use the Excel Formula instead of Code in order to calculate it. I would add a column in the right, which would add the total number of tasks used by a process, as shown in the matrix below.

    A   B   C   D   E   F   G
1   PID T1  T2  T3  T4  T5  Total
2   #11 1                   1
3   #14     1   1   1       3
4   #21 1   1   1   1   1   5
5   #41     1   1           2

Then I will write two Array Formulas to calculate the maximum number of tasks used by a process and the name of that process.

Formula to calculate maximum tasks used in the example: =SUM(IF($G$2:$G$5=MAX($G$2:$G$5),G2:G5,0))

Formula to find the pricess which used the maximum tasks: =OFFSET(A1,SUM(IF($G$2:$G$5=MAX($G$2:$G$5),ROW(G2:G5)-1,0)),0,1,1)

Please note that I had mentioned that I used Array formulas. In order to add array formula in Excel, you need to enter formula and then press "Ctrl+Shift+Enter" to make that formula an array formula.

Hope this helps. Vikas B

-----------------EDIT-----------------------------------------------------

Adding the code here. I just used the sample, as show in matrix and produced the correct result.

    Sub FindMax()

'assuming column 1 is the task ID and Row one has the headings.

Const LastColumn As Integer = 7 ' you can use xl end to get the last used column in the range
Const LastRow As Integer = 5


Dim rowCounter As Integer
Dim prevValue As Integer
Dim rngToTotal As Range
Dim sht As Worksheet
Dim maxRowName As String
Dim value As Integer
Dim maxValue As Integer

Set sht = ActiveSheet 

For rowCounter = 2 To LastRow
    Set rngToTotal = sht.Range(sht.Cells(rowCounter, 2), sht.Cells(rowCounter, LastColumn))
    value = WorksheetFunction.Sum(rngToTotal)

    If value > prevValue Then
        maxRowName = sht.Cells(rowCounter, 1).value
        maxValue = value
    End If
    prevValue = value
Next rowCounter

MsgBox "Process name " & maxRowName & "  =  " & maxValue

End Sub

thanks for your help,But i have to do it dynamic,so I can't set up such formula on the excel columns.I have a VBScript which would run time calculate such things. So any idea how to do so in vbscript? — CodeLover, Dec 31 '12 at 17:43
I used `1` just to tell that columns has data, there can be any data,I need to find out which rows used maximum columns? — CodeLover, Dec 31 '12 at 19:56
Not sure why I didnt get any notification all the comments here. Anyways, even though it has already been answered, I would like to modify my code to ensure my code works as well. The function WorksheetFunction.Sum(rngToTotal) can be replaced by WorksheetFunction.CountA(rngToTotal). Glad the problem has already been resolved. — Vikas, Jan 02 '13 at 12:54
Can you look into the post of mine - http://stackoverflow.com/questions/14119936/i-am-not-able-to-clear-and-reset-the-background-color-of-a-cell/14120509#comment19544973_14120509 — CodeLover, Jan 02 '13 at 13:12

score 1 · Accepted Answer · answered Jan 01 '13 at 07:39

1

Still not convinced why Vikas answer is not working for you. Try this code please. It highlights the last max value. Only flaw is that it doesn't track all the PID that has same max value. I could improve the code if you need that as well.

Code:

Option Explicit

Sub getRealUsedColumns()
Dim rngInput As Range
Dim arrInput As Variant, arrRowTotal As Variant
Dim i As Integer, j As Integer, counter As Integer, iTemp  As Integer
Dim iPID As Integer, maxRowNum As Integer

    arrInput = Application.WorksheetFunction.Transpose(Sheets(3).Range("B3:I8").Value2)
    ReDim arrRowTotal(LBound(arrInput, 2) To UBound(arrInput, 2))

    For i = LBound(arrInput, 2) To UBound(arrInput, 2)
        counter = 0
        For j = LBound(arrInput) + 1 To UBound(arrInput)
            If arrInput(j, i) <> "" Or Not IsEmpty(arrInput(j, i)) Then
                counter = counter + 1
            End If
        Next j

        '-- most recent max value (if you have two of the same, this doens't catch)
        '-- you need to save in a proper array to catch multiple PIDs with same max value
        If iTemp <= counter Then
            iTemp = counter
            iPID = arrInput(1, i)
            maxRowNum = i
        End If

        arrRowTotal(i) = counter
    Next i

    '-- Row total into the sheet output
    Sheets(3).Range("J3").Resize(UBound(arrRowTotal)) = _
                     Application.WorksheetFunction.Transpose(arrRowTotal)

    '-- highlight the max total row.
    With Sheets(3).Range("B3").Offset(maxRowNum - 1, 0).Resize(1, UBound(arrInput, 1) + 1)
        .Interior.Color = 200
    End With

End Sub

Results:

enter image description here

answered Jan 01 '13 at 07:39

bonCodigo

14,268
1
48
91

wowwwww!!! but vikas answer i didn't accept as he just took a `sum` of all `1's` in the range,which was not our target! there can be a different type of values! – CodeLover Jan 01 '13 at 07:43
`Sum` can't be helped here! Please answer the two questions I asked, so we can move forward. – bonCodigo Jan 01 '13 at 07:45
@boCodigo in my case the 2nd question is not needed. and in your first question Yes i need the PID# but anyone will be sufficient and would like to return the `maximum column count` value. – CodeLover Jan 01 '13 at 07:50
one more thing what I can see from your screenshot is that, after `T7` there is no column.but in my cases i have shown there could be more columns also. and those columns may not to be used by rows or may be. – CodeLover Jan 01 '13 at 07:55
Then set the columns according to your `used column count`. This is a sample @Tukai! What's the meaning of "..those columns may not to be used by rows or may be." Please do confirm if this works for you or not. – bonCodigo Jan 01 '13 at 08:36
Yes, it will work for me,but how would I get the maximum column count? means in the array where should be its position? – CodeLover Jan 01 '13 at 08:51
can the code be a bit more shorter for larger data volume work sheet? – CodeLover Jan 01 '13 at 09:39
1

CODE WILL BE THE SAME SIZE REGARDLESS OF YOUR COLUMN OR ROW COUNT. Perhaps the only addition would be an array/collection that will hold `PID` with same max row count. – bonCodigo Jan 01 '13 at 11:09
how this line carrying the logic could you explain? `arrInput(j, i) <> "" Or Not IsEmpty(arrInput(j, i))` – CodeLover Jan 01 '13 at 12:04
@Tukai can't you understand what that line says? You asked how to get the maximum column count, ARE YOU ASKING FOR ALL USED COLUMNS IN THE RANGE OR just the max columns that a PID has tasks? **If so, you can see that in my code PID and max column count are there.** You haven't told how you want to track it....!! – bonCodigo Jan 01 '13 at 15:07
the both are uniquely understandable but in together I am confused! – CodeLover Jan 01 '13 at 15:10
you are still not saving any time to help you out! can you answer the question? How do you want to track the **PID with max columns used**? – bonCodigo Jan 01 '13 at 15:32
i will add the PID with the count to the `dictionary` object and also to the `ArrayList`,so that I can sort it and get the desired one from the last! – CodeLover Jan 01 '13 at 15:37

Is it possible to give the count of maximum columns used by a particular row(s)

2 Answers2