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,ISTEX T(M2)))))))))
2. =IF(COUNT(SEARCH({"xco","cross"},A2)),"9")
Any suggestions.
Thanks!
RJ
Please post a workbook with sample data and desired result. Go Advanced/Manage Attachments
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
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
Last edited by Reji Rajan; 07-20-2017 at 05:01 AM. Reason: Forgot Attachment
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")
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
All yellow cells start BB, Is this always the case? If not, what is the rule for Yellow/Red?if there was a way where the count of cells in yellow could be displayed in the column F.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
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
Joining both the formulas doesn't give me the result i was expecting, it returns TRUE-FALSE & 1-FALSE only
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")
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
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 - 2OSP):XCO:SD
eg.2 HOTSTAR X-(GOT PAGER):XCO:SD
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
Does this "generally" give the correct result? If so, what should the result be if both are numbers?
=IF(F2=FALSE,E2,F2)
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
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
And this bit?
If so, what should the result be if both are numbers?
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
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.
170713_GO_INDEPENDENCE_DAY_SPONSOR_CROSS_DATED13:17:07:21 in Row 684 returns 6 & 8 in your sample
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Yeah....unfortunately, the data that i get is in a specific format so i have to exclude the "cross" criteria & search only "xco".
Your formula is looking at Column C, not A to return 6
IF(LEFT(C682,1)="L",6,
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
I have attached my file in which i incorporated your formula, its giving me the result i wanted.
FYI You could use range names for the formulae and combine them in one column
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
I have never used name range before, will look it up thanks for the tip.
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
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Posting Permissions
Reply With Quote