Last 50 Posts on Chandoo.org

adam on 2020-04-05 10:31:58
For 18.1 =RoundUp(Sum(Cell Address,Cell Address*15%),2) For 18.2 =If(Weekday(Date,2)=OR("2","3","4"),Date-Weekday(Date,2),If(Weekday(Date,2)=OR("5","6",7"),Date+Weekday(Date,2),Date))

Abhinay Sharma on 2020-04-03 18:46:22
First of all thanks for the Superb Tracker. I just set it up and added all my funds. It is working fine except the NAV of ICICI Prudential Mutual Fund is not getting updated since last two days. Please help. Thanks a lot in advance.

Harish Gaba on 2020-04-03 12:38:59
drar, i want create to online play tombola which i want all member pay all member which i decided to how much ticket price, paid my account and who will will win automatically transferred member account

Caroline on 2020-04-03 11:32:47
Hi ! Thank you so much ! Works great :)

vivek on 2020-04-03 10:07:49
do u have PDF for this?

adam on 2020-04-03 04:53:07
=Vlookup(mid(original text,find(" ",original text,find(" ",original text)+1)-find(" ",original text)-1),Range To look in,2,False)

Jim on 2020-04-02 13:43:51
Another (and longer) version is this: =ISNUMBER(FIND("007",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B4,2,""),3,""),4,""),5,""),6,""),8,""),9,""),1))

ron on 2020-04-02 12:10:49
While generating random tickets, I found below issues in a few tickets: 1. Column without a single number 2. This can be for 2 columns at times 3. Columns with numbers in sequence (71, 72, 73) 4. Another Ticket generated will also have the same sequence (71, 72, 73) If I have to distribute the tickets, I need to pick manually which is time consuming.

WRPM on 2020-04-02 09:03:37
Runtime errors (both variants of the file) in Excel 2016 for Mac (version 16.16.20) make this sadly unusable, OOTB. Can anyone confirm this?

Serviceyards.Com on 2020-04-02 01:24:39
You could avoid adding comments: Are you aware that users can add line breaks inside a cell by pressing ALT+RETURN?

Mark Williams on 2020-04-01 05:24:47
This was the first challenge I've tried...happy to find a solution but very impressed with the other solutions here... =IF(IFERROR(FIND("7",B4,FIND("0",B4,FIND("0",B4)+1)+1),-1)>0,"Yes","No")

Nguyen Tan Khoi on 2020-04-01 04:59:17
Thank Mr Chandoo very much. Your website is an extremely valuable source of excel knowledge for everyone.

Jeremy on 2020-03-31 22:17:44
Simplest solution I found =IF(ISNUMBER(SEARCH("0*0*7",B4,1)),"Yes","No")

Jeremy on 2020-03-31 22:15:31
Longest answer ever, I love it :)

Colin Kent on 2020-03-31 16:45:39
I have been trying for a long time to create a graph of my UK mutual funds in excel 2016, but have always failed. I have no trouble with shares but codes for mutual funds baffle me. and example is: HL Multi-Manager Equity & Bond Trust. I would be eternally grateful if you could help me. Kind regards Colin Kent

Daniel Ferry on 2020-03-30 17:59:32
Matt, try this: Function CodeFound(d$, k$) Dim i&, j& For i = 1 To Len(d) If Mid(d, i, 1) = Mid(k, j + 1, 1) Then j = j + 1 Next CodeFound = "No" If j = Len(k) Then CodeFound = "Yes" End Function

Rohit on 2020-03-30 17:22:49
my data have alternative blank rows but the power query did not import all the rows.

Matt Brook on 2020-03-30 15:38:20
Option Explicit Public Const HIDDEN_MESSAGE As String = "007" Public Function IsSecretCodePresent(ByVal cellContents As Range) As String Dim secret() As String Dim message() As String Dim functionResult As String functionResult = "" secret() = SplitStringIntoStringArray(HIDDEN_MESSAGE) message() = SplitStringIntoStringArray(cellContents.Value) If IsCodeEmbedded(secret(), message()) Then functionResult = "Yes" Else functionResult = "No" End If IsSecretCodePresent = functionResult End Function Private Function SplitStringIntoStringArray(ByVal stringIn As String) As String() Dim temp() As String Dim i As Integer ReDim temp(Len(stringIn) - 1) For i = 1 To Len(stringIn) temp(i - 1) = Mid$(stringIn, i, 1) Next SplitStringIntoStringArray = temp() End Function Private Function IsCodeEmbedded(ByRef codeIn() As String, ByRef messageIn() As String) As Boolean Dim functionResult As Boolean Dim codeCounter As Integer Dim messageCounter As Integer Dim lastCounterValue As Integer Dim codeCount As Integer Dim checkArray() As Integer Dim checkCounter As Integer Dim currentCodeItem As String Dim currentMessageItem As String Dim individualCodesFound As Integer Dim messageLBoundValue As Integer Dim forceLoopJump As Boolean Dim forceLoopStop As Boolean functionResult = False lastCounterValue = 0 forceLoopJump = False forceLoopStop = False ' Get the First Item in the message array messageLBoundValue = LBound(messageIn) ' Get number of items in the Seret Code codeCount = UBound(codeIn) - LBound(codeIn) + 1 ' Create Array to keep Checks on each item in the Secret Code are present ReDim checkArray(0 To codeCount - 1) For checkCounter = LBound(checkArray) To UBound(checkArray) checkArray(checkCounter) = 0 Next checkCounter ' Loop through items in Secret Code For codeCounter = LBound(codeIn) To UBound(codeIn) currentCodeItem = codeIn(codeCounter) ' Loop through message For messageCounter = messageLBoundValue To UBound(messageIn) currentMessageItem = messageIn(messageCounter) ' If there is a match, record position in message ' so in next item in Code can start in last ' position in message checked If currentMessageItem = currentCodeItem Then checkArray(codeCounter) = 1 lastCounterValue = messageCounter forceLoopJump = True Exit For End If Next messageCounter If forceLoopJump = True Then messageLBoundValue = lastCounterValue + 1 Else ' If we have cycled through message and haven't found ' the code, then exit function returning false If checkArray(codeCounter) = 0 Then forceLoopStop = True End If End If If forceLoopStop = True Then Exit For End If Next codeCounter ' Find out how many items of the Secret Code were found For checkCounter = LBound(checkArray) To UBound(checkArray) individualCodesFound = individualCodesFound + checkArray(checkCounter) Next checkCounter functionResult = (individualCodesFound = codeCount) IsCodeEmbedded = functionResult End Function

Mike Fitzpatrick on 2020-03-30 13:25:45
Hi. When working with Power query, I was compiling a conditional column using the "step" dialog box and I somehow managed to unintentionally managed to re-size the dialog box and it has re-sized all other step dialog boxes which are now un-usable. I can't see any "global" settings to change the size of the boxes. How do I undo this to revert the step dialog boxes back to their original size. Thanks for your help.

Lee T on 2020-03-29 22:57:08
=IF(ISERR(SEARCH("Procurement A",B4)), IF(ISERR(SEARCH("Project M",B4)), IF(ISERR(SEARCH("Project O",B4)), IF(ISERR(SEARCH("Admin",B4)), IF(ISERR(SEARCH("Pers",B4)), IF(ISERR(SEARCH("General",B4)), "No Matching Job","General Manager"),"Personal Assistant"),"Administrator"),"Project Office Analyst"),"Project Manager"),"Procurement Analyst") Room for efficiency but the formula at least covers eventuality that there is no matching job titles :)

Lee T on 2020-03-29 22:17:07
=IF(IFERROR(SEARCH(7,B4),"No")="No","No",IF(ISERROR(SEARCH(7,B4,SEARCH(0,B4,SUM(SEARCH(0,B4)+1)))),"No","Yes")) 1st time i have had some time to do the homework (wonder why that it is!), thought my solution was good until i saw the other posts, some really efficient coding. Room for me to improve!

sachin Uttam Bhor on 2020-03-29 16:24:05
Also i want create batch code for material automatically Like - Batch Code - for first batch - 001, 002, 003, 004, 005,006 (so on for remaining batches)

sachin Uttam Bhor on 2020-03-29 16:09:28
Hi Guys, I am New in VBA I need your help to create VBA code for below case/example I have created userform for Raw material entries with below details For Example - SR.No - 1 Date Of entry - 29-Mar-2020 Supplier Name - ABC India Ltd Material Name - Deisel Engine Material Number - ............ Now Condition is I want to create Material Code automatically in userform for each material on the basis of material receipt as below Material Code For below Case should be - A100120B Material name - Deisel Engine - A Supplier Name - ABC india ltd - 1 Batch Code - for first batch - 001 (so on for remaining batches) Year - 2020 - 20 Material Type - please guide me Regards, Sachin Bhor Mail - sachin22588@rediffmail.com , sachinb22588@gmail.com

JasonW on 2020-03-29 11:42:34
Having look at the other solutions, I see I used a long way around: =IF( IFERROR(SMALL(UNIQUE(IFERROR(SEARCH(0,B4,SEQUENCE(LEN(B4))),"")),2),"")< IFERROR(MAX(UNIQUE(IFERROR(SEARCH(7,B4,SEQUENCE(LEN(B4))),""))),""), "Yes","No") Using wildcards is brilliant!

JasonW on 2020-03-29 11:40:03
This formula is beautiful! Very elegant!

p45cal on 2020-03-28 16:18:31
I put together a Power Query solution to this a few days ago, but didn't post it because I'm new to Power Query and I don't know the full range of functions available within it, but since no-one has posted one I'll link to my rooky efforts here: https://app.box.com/s/f7rxwlg0g4etvk9ziweu7ztj8gz0qrbz where there's a couple of macros too (not related to the Power Query offering).

Alexander on 2020-03-28 15:51:24
=IF(IFERROR(FIND("007";SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B4;1;"");2;"");3;"");4;"");5;"");6;"");8;"");9;""));0)>0;"Yes";"No")

neil R Auty on 2020-03-28 11:41:24
=IFERROR(IF(FIND(7,B4,(FIND(0,B4,(FIND(0,B4))+1))+1),"Yes"),"No")

neil R Auty on 2020-03-28 11:39:30
You could leave out the =1 bit and still gives the correct answer. I went the long way about this, totally forgot wildcard search so did a long walking nested find solution... Nicely done

Manoj Choudhary on 2020-03-27 22:09:45
=IF(ISERROR(SEARCH(7,B4,SEARCH(0,B4,SEARCH(0,B4,1)+1)+1)),"No","Yes")