Combine Formula

[Reji Rajan]

Hi,

I just wanted to know if its possible to combine below 2 formula's:

1. =IF(LEFT(A2,2)="BB", 1, IF(LEFT(A2,2)="CU",2,IF(LEFT(A2,3)="PR:",3,IF(LEFT(A2,5)="SNEAK",4,IF(LEFT(A2,6)="PRECAP",5,IF(LEFT(A2,5)="RECAP",5,IF(LEFT(C2,1)="L",6,IF(LEFT(A2,7)="Segment",8,IF(ELFT(A2,2)="NA",9,ISTEXT(M2)))))))))

2. =IF(COUNT(SEARCH({"xco","cross"},A2)),"9")

Any suggestions.

Thanks!
RJ

[mdmackillop]

Please post a workbook with sample data and desired result. Go Advanced/Manage Attachments

[Reji Rajan]

I have attached sample workbook. The work book has 2 sheets. On sheet 2, column E has the first condition & Column F has the second condition. Wanted to know if there was a way to combine both the conditions into 1. Also sheet 1 has some data where column has cells marked in yellow & red color. Wanted to know if there was a way where the count of cells in yellow could be displayed in the column F. For e.g, A3 is yellow, so the count should be displayed in F2 as 1, likewise, A5 to A12 are yellow, so F4 should display count as 8. I know this is too much to ask but am just wondering if this is doable.

Cheers!!
RJ

[mdmackillop]

Just join them
=IF(LEFT(A3,2)="BB",1,IF(LEFT(A3,2)="CU",2,IF(LEFT(A3,3)="PR:",3,IF(LEFT(A3,5)="SNEAK",4,IF(LEFT(A3,6)="PRECAP",5,IF(LEFT(A3,5)="RECAP",5,IF(LEFT(C3,1)="L",6,IF(LEFT(A3,7)="Segment",7,IF(LEFT(A3,2)="NA",8,ISTEXT(D3)))))))))) & " - " & IF(COUNT(SEARCH({"xco","cross"},A3)),"8")

[mdmackillop]

if there was a way where the count of cells in yellow could be displayed in the column F.
All yellow cells start BB, Is this always the case? If not, what is the rule for Yellow/Red?

[Reji Rajan]

Yes, that's the criteria, all the cells that start with BB will be colored yellow & if any cell in column D has text, then corresponding cell in column A gets colored red

[Reji Rajan]

Joining both the formulas doesn't give me the result i was expecting, it returns TRUE-FALSE & 1-FALSE only

[mdmackillop]

Copying it from above gives me an error; without Bold: Paste it in Row 3.. I'm seeing it as E - F.

=IF(LEFT(A3,2)="BB",1,IF(LEFT(A3,2)="CU",2,IF(LEFT(A3,3)="PR:",3,IF(LEFT(A3,5)="SNEAK",4,IF(LEFT(A3,6)="PRECAP",5,IF(LEFT(A3,5)="RECAP",5,IF(LEFT(C3,1)="L",6,IF(LEFT(A3,7)="Segment",7,IF(LEFT(A3,2)="NA",8,ISTEXT(D3)))))))))) & " - " & IF(COUNT(SEARCH({"xco","cross"},A3)),"8")

[Reji Rajan]

Hi,

My report is based on certain search criteria's. I filter data if the cell begins with "BB","CU","SNEAK", hence have used LEFT formula as the position of these letters is fixed in the cell. The only problem i encounter is when i have to filter the data for cell containing the letter "xco", so here i cannot use the left formula.

eg.1 PR : OPPO:FTOT:MOTM:INDEPENDENCE DAY RESURGENCE : TEASER - 2 :(OSP):XCO:SD:(15TH AUG 1 PM & 9 PM)
eg.2 HOTSTAR X-(GOT PAGER):XCO:SD:(GENERIC)

If you see the letters XCO are in middle & the position is also not constant. Hence i wanted to combine the above 2 formulas. The resulting condition column, should either return TRUE, FALSE or numbers 1 to 8 as per the condition in the formula. Any other like TRUE-FALSE will not work for the macro i have recorded. If I use the the formula

=IF(LEFT(A2,2)="BB",1,IF(LEFT(A2,2)="CU",2,IF(LEFT(A2,3)="PR:",3,IF(LEFT(A2 ,5)="SNEAK",4,IF(LEFT(A2,6)="PRECAP",5,IF(LEFT(A2,5)="RECAP",5,IF(LEFT(C3,1 )="L",6,IF(LEFT(A2,7)="Segment",7,IF(LEFT(A2,2)="NA",8,ISTEXT(D3))))))))) ) & " - " & IF(COUNT(SEARCH({"xco","cross"},A2)),"8").......it returns result FALSE-FALSE which will not work with my macro.

P.S.The smileys are brackets

[mdmackillop]

Does this "generally" give the correct result? If so, what should the result be if both are numbers?
=IF(F2=FALSE,E2,F2)

[Reji Rajan]

So far the condition has given correct result, i have used the formula u mentioned in your last revert in another cell & its giving the desired result. Thanks.
Just checking if there was possibility of getting a count on the colored cells (i had mentioned the details in post#3).

Thanks for the formula though.

Cheers!
RJ

[mdmackillop]

And this bit?

If so, what should the result be if both are numbers?

[Reji Rajan]

If both the conditions are numbers then the numbers should be same, i.e if condition 1 has returned result as 8, then condition 2 should also return the same result. I can post the sheet in which i have used the formula to get desired result.

[mdmackillop]

170713_GO_INDEPENDENCE_DAY_SPONSOR_CROSS_DATED13:17:07:21 in Row 684 returns 6 & 8 in your sample

[Reji Rajan]

Yeah....unfortunately, the data that i get is in a specific format so i have to exclude the "cross" criteria & search only "xco".

[mdmackillop]

Your formula is looking at Column C, not A to return 6
IF(LEFT(C682,1)="L",6,

[Reji Rajan]

I have attached my file in which i incorporated your formula, its giving me the result i wanted.

[mdmackillop]

FYI You could use range names for the formulae and combine them in one column

[Reji Rajan]

I have never used name range before, will look it up thanks for the tip.

[mdmackillop]

As colours are the result of Conditional Formatting, you cannot retrieve an Interior.Color value for the cell, you need to look at the rules for the formatting.
In this case try this UDF. Enter "=ClrCount(A4)" in Row 4

Function ClrCount(Data As Range)
i = 2
x = Left(Data(i), 2)
Do
i = i + 1
Loop Until Left(Data(i), 2) <> x
ClrCount = i - 2
End Function

[Reji Rajan]

If not by color, then can the counter be triggered by a number or a text. for e.g,In my attached workbook, the first sheet is named as counter. In this sheet, Column E contains header called condition & the cells below either has text "TRUE" or number "1" in it. I know i can use count if condition to get the number of "1s" in between true but the problem is the data is never constant, i mean TRUE & 1 can be in any cell in that particular column and I specifically the count of "1s" between 2 "true" cells.

[mdmackillop]

The Function in my previous post looks at the cells in Column 1 to do that, Enter that code in a standard module and enter
=IF(LEFT(A2,2)<>"BB",ClrCount(A2),"")
in Row 2 and copy down.

[Reji Rajan]

I tried the function & it works brilliantly on an individual sheet (like the one i have pasted here)but there is a slight hindrance. This sheet is part of a macro enabled workbook, which i have made partly by recording macro & partly by adding codes. It allows me to add the code & the formula (=IF(LEFT(A2,2)<>"BB",ClrCount(A2),"") only in 1 cell. the moment i try drag the formula in the entire column, the workbook freezes & excel file is unresponsive. I have to forcefully exit the workbook. Am i missing some logic here or this code wont work with another macro enabled workbook. Also pardon my knowledge and sorry to sound like an oaf but can the count of cells be obtained without code and only by formula.

[mdmackillop]

I suggest you mark this as solved post this part as a new question to get more input.