Search results

Welcome to Chandoo Org forums! Your description is unclear (at least to me). Describe the method of import, post the code. Post some sample data where it doesn't work for you.

What is the format of the files that you want to edit? Editing csv or text files is fairly easy if you know Excel VBA. If there's any other format that you want to manipulate then it could be fairly involved process. To do it through Excel, the parent application either needs to provide access...

Hi Lou, The construct you have implemented is correct, it just needs small tweak. See the red-marked formula to get the next month. =IF(G3="This Month End",DATEVALUE("25-"&TEXT(A3,"mmm-yy")),IF(G3="Within 7 days",A3+7,IF(G3="Next month...

You can take care of gaps by using construct like below. =MAX(A2:INDEX(A2:F2,MATCH(2,1/ISNUMBER(A2:F2))-1))

Welcome to Chandoo Org forums. In cell H3, you can write a formula like below. =IF(F3="Month End",DATEVALUE("25-"&TEXT(A3,"mmm-yy")),IF(F3="Within 7 days",A3+7,"Formula for other")) You need to provide logic for values such as "Other (See Notes)" so that you get correct results in all three cases.

That caret ^ symbol would fail with the new sample. JSON chunk seems to be the block wrapped in braces ({}) and therefore the block pattern could be: ^{[\s\S]+?^} and then you can safely execute rest of the code without worrying if other patterns exist or not.

I tested with the sample of data you posted. If it is not picking up then you should check the text that is getting processed.

If you mean the internal loop then one way could be (with posted sample at least): With Rgxp .Global = True .MultiLine = True '\\ Load Blocks Here .Pattern = "^\d+:[\s\S]+?trend:" Set elem = .Execute(S) '\\ Loop through them r...

One way might be to capture the blocks first using a pattern like ^\d+:[\s\S]+?trend: and then extracting the relevant values if they exist. Not elegant (just like my RegExp knowledge ;) )

That kind of makes me feel quite young, I began with Excel 2003 and it wasn't love at first sight. But as it often happens, it was popular choice in office computers and therefore I kept using it. I realized Excel's power when I could write VBA program to send emails from IBM's another product...

If you search following term on Google: "Who created Excel" or "Who invented Excel" then Google promptly returns Dan Bricklin's name and couple of his younger day photos. In reality, Dan Bricklin never worked for Microsoft. But he did create the electronic spreadsheet before Microsoft Excel...

This is one more way to read information through RegExp Public Sub ReadTextFileRegEx() Const ForReading = 1 Dim strInput Dim i As Long '\\ Use FileSystemObject Object to read contents of text file in one go Dim objFSO As Object: Set objFSO = CreateObject("Scripting.FileSystemObject") Dim...

Your data in column A and B shall be numerical then following array formula (to be committed by CTRL+SHIFT-ENTER) works...

If you create a source table like 0 6% 56 8% 66 10% 81 10% 91 11% Then old warhorse VLOOKUP works just fine where $K$3:$L$7 houses above source table! =VLOOKUP(B2,$K$3:$L$7,2,TRUE)

If sequence is not of importance then you can use. =TEXTJOIN(", ",TRUE,IF(ISNUMBER(SEARCH('Sector Mapping'!$A$2:$A$18,Sheet1!A4,1)),'Sector Mapping'!$B$2:$B$18,""))

If you have TEXTJOIN function then a simpler formula can be used. =TEXTJOIN(CHAR(10),TRUE,IF(ISNUMBER(SEARCH($E$2:$E$7,A2,1)),$F$2:$F$7,""))

This is how Marc's suggestion would look. Adjust the range to suit Public Sub AdjustRange() Range("A1:A4").Value = Evaluate("INT(" & Range("A1:A4").Address(0, 0) & "*100)") End Sub

This is my suggestion, which differs only a little bit in its concept. In cell B1: A formula is implemented to keep track of all cells having commas and provide entries...

Hi Manish, Your requirement is quite unique for me (personally). The formula approaches sometimes become tedious to deal with as you are finding out. I am curious about cell B10 where West, West is appearing. What does it mean? 10.9 shall be added twice?

You sample is inconsistent. Cell D10 shall be "PE, PE, PE". It could well be consistent for human to identify this as PE for all but it is too much to deal for a code. If you have office 365 and TEXTJOIN function then you can try which can be copied down and across...

In the first piece of code you have posted, variable ar is variant and it can accept all data types.

Following code works Dim arSplit() As String arSplit = Split("a b", " ") Range("A1:B1").Value = arSplit It fails at your end on the line specified because you are not assigning the data type. Split generates output in string format. Does that answer a part of your query?

Finding the word boundaries makes it bit tricky. =SUMPRODUCT(--ISNUMBER(SEARCH(" "&F15&" "," "&SUBSTITUTE($B$3:$B$12,","," ")&" ",1))) and for totals =SUMPRODUCT(ISNUMBER(SEARCH(" "&F15&" "," "&SUBSTITUTE($B$3:$B$12,","," ")&" ",1))*$C$3:$C$12)

With your posted sample, assuming the split occurs at numerical-alphabetical point following formula works which assumes that your data starts from cell A1 =LEFT(A1,LOOKUP(2,1/ISNUMBER(MID(A1,ROW($A$1:INDEX(A:A,LEN(A1))),1)+0),ROW($A$1:INDEX(A:A,LEN(A1))))) Once passport number is found then...

You can use in column G: =COUNTIF($B$3:$B$12,"*"&F15&"*") your description seems to be incorrect in the file (central appears 4 times and not 5 times) And in column H following formula can be used. =SUMIF($B$3:$B$12,"*"&F15&"*",$C$3:$C$12)