Hi,
I'm going to assume that the column Logic contains some sort of IF formula.
Step1 Open VBA
Press Alt F11
Click on Insert, click Module.
Paste this into the white area....
Function GetFormula(Cell As Range) As String
GetFormula = Cell.Formula
End Function
Press Alt F11 to return to your worksheet.
You will need to add a "Helper" column (F) to display the formulas used in column Logic.
This column (F) must have the following formula added to it.....
=GetFormula(C2)
Where C2 is the first cell in column Logic with a formula.
Copy this formula down until the last cell required.
You will now need to type IF, case, move into seperate cells so that you can do a count on them, please see the following table as an example...
Excel Workbook |
---|
|
---|
| A | B | C | D | E | F | G | H | I | J | K | L |
---|
1 | table name | source colun | Logic | target table | target column | Cell Formula | | Criteria | Count1 | Count2 | Count3 | |
---|
2 | a | a1 | FALSE | A | A1 | =IF(A1=4,IF(B1="null",1,0)) | | IF | 10 | 10 | 10 | |
---|
3 | b | b1 | 0 | | | =IF(B1=4,1,0) | | case | 3 | 3 | 3 | |
---|
4 | B | B1 | FALSE | | | =IF(C1="null",IF(A1=3,1,0)) | | move | 5 | 5 | 5 | |
---|
5 | c | c1 | case | C | C1 | case | | | | | | |
---|
6 | d | d1 | move | D | D1 | move | | | | | | |
---|
7 | e | e1 | move | E | E1 | move | | | | | | |
---|
8 | f | f1 | case | F | F1 | case | | | | | | |
---|
9 | a | a2 | 0 | | | =IF(B2=4,1,0) | | | | | | |
---|
10 | G | A2 | FALSE | | | =IF(C1="null",IF(A2=3,1,0)) | | | | | | |
---|
11 | b | b2 | FALSE | H | B2 | =IF(A2=4,IF(B1="null",1,0)) | | | | | | |
---|
12 | c | c2 | case | I | C2 | case | | | | | | |
---|
13 | d | d2 | move | J | D2 | move | | | | | | |
---|
14 | e | e2 | move | K | E2 | move | | | | | | |
---|
15 | f | f2 | move | L | F2 | move | | | | | | |
---|
16 | | | | | | | | | | | | |
---|
|
---|
The formula in F2 needs to be copied down.
The formula in I2 needs to be copied down.
The formula in J2 needs to be entered with ctrl shift enter and NOT just enter.
The formula in K2 needs to be copied down.
I have used different formulas to show how this can be resolved.
The main difference here is that K2 will display the correct result regardless of the use of UPPER or LOWER case letters, see the following....
Excel Workbook |
---|
|
---|
| A | B | C | D | E | F | G | H | I | J | K | L |
---|
1 | table name | source colun | Logic | target table | target column | Cell Formula | | Criteria | Count1 | Count2 | Count3 | |
---|
2 | a | a1 | FALSE | A | A1 | =IF(A1=4,IF(B1="null",1,0)) | | IF | 10 | 10 | 10 | |
---|
3 | b | b1 | 0 | | | =IF(B1=4,1,0) | | case | 2 | 2 | 3 | |
---|
4 | B | B1 | FALSE | | | =IF(C1="null",IF(A1=3,1,0)) | | move | 4 | 4 | 5 | |
---|
5 | c | c1 | CASE | C | C1 | CASE | | | | | | |
---|
6 | d | d1 | move | D | D1 | move | | | | | | |
---|
7 | e | e1 | Move | E | E1 | Move | | | | | | |
---|
8 | f | f1 | case | F | F1 | case | | | | | | |
---|
9 | a | a2 | 0 | | | =IF(B2=4,1,0) | | | | | | |
---|
10 | G | A2 | FALSE | | | =IF(C1="null",IF(A2=3,1,0)) | | | | | | |
---|
11 | b | b2 | FALSE | H | B2 | =IF(A2=4,IF(B1="null",1,0)) | | | | | | |
---|
12 | c | c2 | case | I | C2 | case | | | | | | |
---|
13 | d | d2 | move | J | D2 | move | | | | | | |
---|
14 | e | e2 | move | K | E2 | move | | | | | | |
---|
15 | f | f2 | move | L | F2 | move | | | | | | |
---|
16 | | | | | | | | | | | | |
---|
|
---|
IF, column Logic does contain formulas, then this shouldn't be an issue for you and you should be able to use the formula in I2.
I got the above solutions by doing a quick G**gle search........
Count1
Excel Count the number of times a word appears in a range?
Count2
Formulas to count the occurrences of text, characters, or words in Excel for Mac
Count3
Count number of times a string exist in multiple cells using excel formula | Get Digital Help - Microsoft Excel resource
I hope this resolves the problem for you, if not then I am sorry but I cannot help you further with this and I suggest that you start a new question.
In your new question you may want to include a more accurate example of your data and the actual formulas used.
Good luck.
Ak