windows - How to count two different column?
2014-07
I really need your help regarding the below table, I need a formula to show me how many pending/closed for each project.
Project Status Project Pending Closed
VIVA closed VIVA 1 2
ZAIN closed ZAIN 1 1
VIVA PENDING WATANIA 1 0
WATANIA closed
ZAIN PENDING
VIVA closed
I only know VBa (I don't really know workbook functions) so since you don't specify which you want, this VBa should do what you want
Sub UpdateStatus()
Dim row As Integer
row = 2 ' sets the starting row
Dim statisticRow As Integer
statisticRow = 2
Do While (True) ' we must reset everything before we go on our quest. Be gone foul witch
If Range("F" & statisticRow).Value = "" Then
Exit Do
End If
Range("F" & statisticRow).Value = ""
Range("G" & statisticRow).Value = ""
Range("H" & statisticRow).Value = ""
statisticRow = statisticRow + 1
Loop
Do While (True)
Dim currentValue As String
currentValue = Range("A" & row).Value
Dim otherValue As String
If currentValue = "" Then
Exit Do
End If
Dim otherRow As Integer
otherRow = 2 ' sets the starting row where the results are
Do While (True) ' find it or add it
otherValue = Range("F" & otherRow).Value
Dim currentValueStatus As String
If otherValue = "" Then
currentValueStatus = Range("B" & row).Value
Range("F" & otherRow).Value = currentValue
If currentValueStatus = "closed" Then
Range("H" & otherRow).Value = 1
End If
If currentValueStatus = "PENDING" Then
Range("G" & otherRow).Value = 1
End If
Exit Do
End If
If currentValue = otherValue Then ' Good news sire, I found it
currentValueStatus = Range("B" & row).Value
If currentValueStatus = "closed" Then
Range("H" & otherRow).Value = Range("H" & otherRow).Value + 1
End If
If currentValueStatus = "PENDING" Then
Range("G" & otherRow).Value = Range("G" & otherRow).Value + 1
End If
Exit Do
End If
otherRow = otherRow + 1
Loop
row = row + 1
Loop
End Sub
Before
And after I run the macro
As you can see, it will automatically put in the company names for you, and work out how many of each exist. This means, if you added a new company and ran the macro again, it will be updated with the new detail without any change to the code.
I have data in my excel sheet in the below format
A B
1 2
2 3
3 5
4 8
5
6
7
8
9
i.e colA has values 1 through 9 and colB has 2,3,5,8. Now what I need in col C is, the data in colA which is not there in colB .
So my result column C should have
C
1
4
6
7
9
Can anyone help me out how to achieve this?
Thanks
I've haven't got excel at this computer but the basic idea is to use
=IF(ISNA(MATCH(A1, B:B, 0));A1;"")
I'll check if this works in a couple of hours.
Explanation: Set the value of the current cell to value of A1 if there exists no cell in column B that has value that is an exact match to the value of cell A1.