Text in a Column to return a statement. 5

[HairyHippy]

Unfortunately the search function is down, so apologies if this has been posted before.

I have a column that might have the text "Not Enough Data" in anyone of that column's cells. I would like a forumla that if the column has that text then return a statement such as "This deal has insufficient data to calculate" but if the column doesn't have this text then return something else (I have this final bit!).

Thanks in advance for your assistance.

 

[gizmo1973]

In cell next to your data type
=if(a1="Not Enough Data","This deal has insufficient data to calculate","")
Where A1 is your typed text

et voila

and if you want another outcome if it doesn't have the text insert into the last ""

Regards, Phil

"Then I’d say what’s the point of having rank if you can’t pull it?"

(DCI Jack Meadows)

 

[HairyHippy]

Thanks Phil

That works if it is only cell A1 that contains the text what I need is to search the entire column (eg A:A) as that text might be in cell A125 for example.

 

[gizmo1973]

Sory you did say that,
Just drag the formula down to your last cell (or further if you want)in that column
This will show any cell that the formula points to that has the data in it as I presume there may be more than one?

Regards, Phil

"Then I’d say what’s the point of having rank if you can’t pull it?"

(DCI Jack Meadows)

 

[frainbreeze]

hello

in your workbook go to Tools -> Macro -> Visual Basic Editor
Then go to Insert -> New Module
then paste this

Public Function ShowData(src)
       If src.Text = "Not Enough Data" Then
            ShowData = "This deal has insufficient blablabla"
       Else
            ShowData = "Sufficient Data"
       End If
End Function

then go to File -> Close & Return to Excel

then in your spreadsheet to use the function do this
=ShowData(A3) (or whatever cell you want it to check)

and it should check for whether it says "Not Enough Data" or not.

hope that gets you a start

fraiNbreeze

 

[xlbo]

=IF(NOT(ISNA(MATCH("Not Enough Data",A:A,0))),"This deal has insufficient data to calculate","")

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[HairyHippy]

Unfortunately I need this forumla/statement to appear in one cell only (eg this statement must appear in cell B1). Apologies for not being clear at the start.

 

[gizmo1973]

You could go down the V Lookup route but after seeing what Geoff has just submitted I'd say "What's the use?"


Regards, Phil

"Then I’d say what’s the point of having rank if you can’t pull it?"

(DCI Jack Meadows)

 

[HairyHippy]

Thanks all, the forumla ended up as follows:
=IF(ISNA(VLOOKUP("Not Enough Data",C:C,1,FALSE))=TRUE,"This deal will start to be implemented in "&ROUND(MIN(C:C),0)&" days for notification date.","There is not enough data to estimate this deal, please see the individual product and country timeframes below.")

 

[xlbo]

There's no point using vlookup as you are not returning data - just checking it is there.

Admittedly, one formula won't affect your spreadsheet but for future reference, vlookups are memory intensive and if you had 5000 vlookups vs 5000 Match formulae, you would certainly notice the difference in calc time

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[HairyHippy]

Strange - I used Geoff's formula but it didn't work for some reason, so went with what I wrote. Just tried Geoff's again and it did work so will revert to Geoff's. Many Thanks all!

 

[gizmo1973]

As I said I saw Geoffs and my great idea for a lookup was out of the window.
It's what I'll be using.


Regards, Phil

"Then I’d say what’s the point of having rank if you can’t pull it?"

(DCI Jack Meadows)

 

[HairyHippy]

fraiNbreeze - thanks to you also but it couldn't cope with:
"This deal will start to be implemented in "&ROUND(MIN(C:C),0)&" days for notification date.","

I'm sure there is a solution using your method and will be interested to see it.

In the meantime, the formula I'm now using (along Geoff's) line is:

=IF(NOT(ISNA(MATCH("Not Enough Data",C:C,0))),"There is not enough data to estimate this deal, please see the individual product and country timeframes below.","This deal will start to be implemented in "&ROUND(MIN(C:C),0)&" days for notification date.")

 

[frainbreeze]

hello

if you're not super comfortable with editing code then use your round formula in the spreadsheet

if you amend the code to this:

Public Function ShowData(src As Range, theformula)
 If src.Text = "Not Enough Data" Then
  ShowData2 = "There is not enough data to estimate this deal," & _
  "please see the individual product and country timeframes below"
 Else
  ShowData = "Sufficient Data " & theformula & " days for notification date."
       End If
End Function

and then in your spreadsheet use the function like this:
=ShowData(A1, ROUND(MIN(C:C),0))

where A1 is whatever cell you want it'll search through the column C and spit out the lowest number. then just copy and paste this formula down to wherever..

good luck with it

fraiNbreeze

 

[xlbo]

heh heh - frainbreeze - still wouldn't work as per the spec. Theya re asking for ONE function to test the whole column

Public Function EnoughData(whatCol as string)
dim fCell as range
set fCell = columns(whatCol).find("Not Enough Data", lookin:=xlvalues, lookat:=xlpart, matchcase:=false)
If not fCell is nothing then
 'Text found
  EnoughData = true
else
  EnoughData = false
end if
end fucntion

Then enter this in B1:

=if(EnoughData("A")=true,"This deal will start to be implemented in "&ROUND(MIN(C:C),0)&" days for notification date.","There is not enough data to estimate this deal, please see the individual product and country timeframes below.")

But it's all a bit pointless because it can easily be done with native functionality - therefore no need for a UDF.

Top Tip: You can also store formulae as names

Go Insert>Name>Define

Instead of a range, enter the formula
=IF(NOT(ISNA(MATCH("Not Enough Data",$C:$C,0))),"There is not enough data to estimate this deal, please see the individual product and country timeframes below.","This deal will start to be implemented in "&ROUND(MIN($C:$C),0)&" days for notification date.")

and call it "DataTest"

Then goto b1 and enter
=DataTest

et voila



Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[dcompto]

xlbo,
Many thanks for the Top Tip!

 

[byundt]

dcompto,
COUNTIF is even shorter than the NOT, ISNA & MATCH functions:
=IF(COUNTIF(C:C,"Not enough data")>0,"There is not enough data to estimate this deal, please see the individual product and country timeframes below.","This deal will start to be implemented in "&ROUND(MIN(C:C),0)&" days for notification date.")

Both this formula and the NOT, ISNA & MATCH formulas return an error value when column C contains one or more error values (including #NA).
Brad

 

[dcompto]

byundt,
Thanks! I appreciate the information.