User Profile
Brad_Yundt
MVP
Joined 7 years ago
User Widgets
Recent Discussions
Re: INDEX MATCH Avoid Duplicate Returns
I simplified the formula by using AGGREGATE instead of SMALL. I don't believe it needs to be array-entered with AGGREGATE. Note that the comma is the list separator in my U.S. locale. If you open the attached workbook, the formula should be formatted correctly for your locale. =IFERROR(INDEX($I$4:$L$48,AGGREGATE(15,6,(ROW($K$4:$L$48)-ROW($K$4)+1)/((COUNTIF($G$4:$G$15,$K$4:$K$48)+COUNTIF($G$4:$G$15,$L$4:$L$48))>0),ROWS(B$3:B3)),COLUMNS($B3:B3)),"")5.4KViews0likes1CommentRe: INDEX MATCH Avoid Duplicate Returns
When asking a question, please post a sample workbook showing your data, its layout, what you have tried, and the desired results. Anybody trying to help will need a test workbook, and lots of them will go onto the next question if they need to create it from a screenshot. The amount of data you showed in the screenshot is plenty.5.1KViews0likes2CommentsRe: Text to Columns for Multiple Columns at Once
frri3484 It is also possible to use a macro to insert the blank columns. Select your data, then run the macro. It will add blank columns after each column in your selection. Sub InsertBlankColumns() Dim rg As Range Dim i As Long, n As Long Application.ScreenUpdating = False Set rg = Selection.EntireColumn n = rg.Columns.Count For i = n To 1 Step -1 rg.Columns(i + 1).EntireColumn.Insert Next End Sub43KViews0likes1CommentRe: #SPILL! Error when doing VLOOKUP?
saurajyoti What I see in the Message box is shown below. The Browse... button at the bottom left is what I use to attach files. If you don't have that button, then my email address is first initial last name at my ISP alum dot mit dot edu. Brad Yundt101KViews1like0CommentsRe: Text to Columns for Multiple Columns at Once
frri3484 "however my data is all over the place in length" That's why you should always post a sample workbook with data illustrating the problem. Then instead of getting a suggestion that solves the question you asked, you get one that addresses the specifics of your actual data. If the columns are of different length, that is easily addressed with a tweak to the macro. If the blank columns between your data don't exist, that can also be addressed. If you may have more than one delimiter in each cell, even that can also be addressed (though with somewhat more difficulty). Brad44KViews0likes1CommentRe: Need to copy some data from workbook A to Workbook B
Bob, Recorded macros are littered with .Select and .Activate statements. You don't need them, and getting rid of them will make your macro shorter, easier to debug, and faster. You will notice that I rewrote your code to use With blocks. When you use an object in a With block, any time you start an expression with a dot, VBA knows you are referring to the With block object. And because VBA doesn't have to resolve a fully qualified reference, the code runs faster. When you ask for help on Excel help forums, you should anticipate almost everybody either getting rid of the .Select statements for you, or wishing that you would get rid of them yourself. The one concern I have is that after I eliminated your .Select statements, your code calls the sub ShowPreviousEntry. I don't know which worksheet needs to be active when you run that sub--but I may have broken a dependency. Ideally, all of your code will be rewritten to get rid of the .Select statements. You may need to learn a little bit of programming from scratch, but I have found most people pick it up with about an hour of study or one on one tutoring. Brad Sub PostAddr() ' ' PostAddr Macro ' Post entry data to data sheet ' ' Keyboard Shortcut: Ctrl+Shft+A ' Dim mySpace As String Dim longAddress As String Dim addrLen As Integer Application.ScreenUpdating = False If CheckForDuplicateAddress = True Then Exit Sub mySpace = " " ' check to make sure there is data to post With ThisWorkbook.Worksheets("Entry") addrLen = Len(Trim(.Range("B9"))) End With If addrLen < 5 Then Call ShowPreviousEntry 'Does this sub depend on a particular worksheet being active? Exit Sub End If ' insert a blank row pushing existing rows down Set wsData = Workbooks(dataName).Worksheets("Data") With wsData Set rgData = .Rows("4:4") rgData.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove '..... End With End Sub1.8KViews0likes0CommentsRe: Request for Experts Formulas for this Calculation
Superhero3984 The figures in your example table neither round up to the dollar nor to 10 cents. So I don't understand how to interpret "I required to derive to a nearest 0.10 cent if its above 0.5 should be 1.0 then." Either revise your example table, or clarify how you want the calculations to be done.998Views0likes0CommentsRe: Text to Columns for Multiple Columns at Once
frri3484 If you already have blank columns next to your data, you can use a simple macro to automate your text to column conversions. I suggest recording the conversion of one column, then generalizing the recorded macro because the text to columns method is a little complicated. The code below was based on a recorded macro. It assumes you select the cells to be converted, that every other column will be converted, and that you already have the necessary blank columns. Sub TextToColumnator() Dim rg As Range Dim i As Long, n As Long Application.ScreenUpdating = False Application.DisplayAlerts = False Set rg = Selection n = rg.Columns.Count For i = 1 To n Step 2 rg.Columns(i).TextToColumns Destination:=rg.Cells(1, i), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _ Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _ :=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True Next Application.DisplayAlerts = True End Sub45KViews0likes5CommentsRe: Trying to create an "if statement" in excel
Carol423 Let's suppose the total amount of time worked is in cell A1, and the amount of time worked on job XYZ is in B1. Furthermore, time is being entered as decimal hours (e.g. 6.5) rather than clock time (6:30). I suggest using MAX instead of IF when you figure out your overtime and pro-rating. Once you wrap your head around it, MAX makes the calculations simpler. The number of hours of overtime is =MAX(0, A1-8) If you want to assign overtime pro-rata to project XYZ you might use =MAX(0, A1-8)*(B1/A1) If you use time and a half for overtime, the number of equivalent straight time hours is: =B1+0.5*MAX(0, A1-8)*(B1/A1) If you still need help, I suggest that you post a workbook in this thread using the Browse... button at the bottom of the Comment box. That way people can give suggestions specific to your worksheet layout.523Views0likes0CommentsRe: Problemas con el Editor de Visual Basic en Excel 2016 para Mac
Jorge, You may display userforms in Mac Excel 2016, but you cannot create them or edit how they look. Neither can you change the properties of the UserForm or any objects on it using the Property window. You can, however, edit the code for those userforms. Until Microsoft developers get around to eliminating this gap, you should build your userforms using Windows Excel and then test whether they display properly on your Mac. I run Windows Excel on a Parallels Virtual Machine on my Mac, and have done so for years. Code runs at perhaps 75% of the speed of a Windows computer. If I am satisfied with how fast code runs on my virtual machine, I know that my customer will be happy, too. Brad4.7KViews0likes0CommentsRe: Need to copy some data from workbook A to Workbook B
Bob, Excel VBA has a pre-defined set of so-called "event subs" that run whenever the event they are associated with occurs. Each of the worksheets and ThisWorkbook have their own set. In my copy of Excel, there are 48 in ThisWorkbook, which you can see by choosing Workbook in the left field and then clicking the field on the right to display the dropdown choices. Some event subs run before the associated activity is completed, while others run after. All of them are triggered automatically--you need do nothing more than include the stub (first and last lines of the sub, which Excel generates for you automatically) in the class module sheet. It is possible to have a sub named Workbook_Open in a regular module sheet. That sub won't run automatically when you open the workbook, and if you have another Workbook_Open sub in ThisWorkbook, you will need to qualify the sub names so VBA knows which one you mean, e.g. Module1.Workbook_Open. All that said, it isn't good practice to have a sub in a regular module sheet with the same name as an event sub. "All my code is in Module1." That's good practice. The snippet below shows how you might save and close a workbook. Workbooks(dataEntryName).Save. 'Save workbook Workbooks(dataEntryName).Close SaveChanges:= False. 'Close without saving Workbooks(dataEntryName).Close 'If changes made, Excel will ask to Save before closing1.9KViews0likes2CommentsRe: Problemas con el Editor de Visual Basic en Excel 2016 para Mac
Jorge, I was quite impressed with how well Bing Translate handled the translation of your Spanish inquiry into English. I hope that it does equally well for you in the other direction. Mac Excel 2016 (including VBA) was completely rewritten to use the Cocoa API instead of the older Carbon API. At initial release, the VBA IDE in Mac Excel 2016 was missing so many features you could do little more than debug code written in Windows Excel (or Mac Excel 2011). If you have Office 365, many of the missing features were restored--but not all of them. I have the Office Insider release of Mac Excel 2016/Office 365. It is more recent than your version, but the middle icon is still grayed out. On Windows Excel VBA, that icon returns you to the worksheet. In Mac Excel 2016 VBA, I click the yellow circle at the top left corner of the VBA window to minimize it and accomplish the same thing. Alternatively, if you leave a small portion of the worksheet window exposed, you can click on that to jump back to the worksheet. The far left icon in Mac Excel 2016 VBA does take you to the code for the selected object. This was a feature I had never noticed before in either Windows or Excel VBA. My habit is to doubleclick the gray module icon in the Project Explorer (shaded gray in screenshot below) instead.4.8KViews0likes2CommentsRe: Request for Experts Formulas for this Calculation
Superhero3984 I tried to build a formula that went from your Total column to each of the other two, but the results were inconsistent. The closest I got was to assume amounts should be rounded to the nearest 5 cents. The following formula assumes your sample data are in A2:D2. Values in D2 are given. The formulas for B2 and C2 (less 15% discount and plus 3% GST) are shown below: =MROUND(D2*0.85,0.05) =MROUND(D2*1.03,0.05)1KViews0likes2CommentsRe: Need to copy some data from workbook A to Workbook B
Bob Hilton FWIW, sub GetEntryFile can never fail to find the entry workbook--because it is the one containing the code that is running. Not a problem--just that you have a useless test of whether that file is open. The data file workbook, however, may or may not be open. I revised sub GetDataFile so it would open the workbook if need be: Public Sub GetDataFile() Dim dataFilePath As Variant Dim wbx As Workbook 'MsgBox ("Please select the file containing the data for Neighbors") dataFilePath = Application.GetOpenFilename _ ("Excel files (*.xls, *xlsx, *xlsm, *xlsb", Title:="Please select the file containing data for Neighbors") If dataFilePath = False Then Exit Sub dataName = Right(dataFilePath, Len(dataFilePath) - InStrRev(dataFilePath, _ Application.PathSeparator)) bDataOpen = IsWorkbookOpen(dataName) If Not bDataOpen Then Workbooks.Open dataFilePath 'MsgBox "Data workbook (file) failed to open", vbCritical, "Data Workbook Open Failed" End If ThisWorkbook.Worksheets("Entry").Range("E22").Value = dataFilePath End Sub1.9KViews0likes1CommentRe: Concatenating column B values if left adjacent cell is empty
JayNixon I revised the macro to allow up to 200 more rows of results in case of overflow, as well as up to 165 characters in next pass through the concatenation loop. When an overflow situation is detected, the server name will be repeated as often as needed to get all software names listed on additional rows. Sub Sequelize() Dim rg As Range Dim delimiter As String, s As String Dim i As Long, k As Long, n As Long, nData As Long Dim vData As Variant, vResults As Variant delimiter = ", " Set rg = Range("A2").CurrentRegion Set rg = rg.Offset(1, 0).Resize(rg.Rows.Count - 1, rg.Columns.Count) n = rg.Rows.Count nData = Application.CountA(rg.Columns(1)) nData = nData + 200 'Allow for servers whose software list exceeds 32,767 characters vData = rg.Value ReDim vResults(1 To nData, 1 To 2) For i = 1 To n If vData(i, 1) <> "" Then k = k + 1 vResults(k, 1) = vData(i, 1) If s <> "" Then If i > 1 Then vResults(k - 1, 2) = Left$(s, 32767) End If s = IIf(vData(i, 2) = "", "", vData(i, 2)) Else If vData(i, 2) <> "" Then If s = "" Then s = vData(i, 2) Else s = s & delimiter & vData(i, 2) End If End If End If 'Overflow occurs if you put more than 32,767 characters in a cell. This block allows up to 165 characters (plus delimiter) in next pass through loop. If Len(s) > 32600 Then If (i < n) And (vData(i + 1, 1) = "") Then vResults(k, 2) = s vResults(k + 1, 1) = vResults(k, 1) s = "" k = k + 1 End If End If Next If s <> "" Then vResults(k, 2) = Left$(s, 32767) rg.ClearContents rg.Resize(nData, 2).Value = vResults End Sub7.3KViews1like7CommentsRe: Concatenating column B values if left adjacent cell is empty
Thanks for sending me the file. I can reproduce the problem with my Excel 2016/Office 365 64-bit. Excel cells can hold a maximum of 32,767 characters. Your problem server has 86,510 characters worth of software. I am guessing an overflow occurs that results in the loss of your data. To test this hypothesis, I modified the code to truncate the software list to the first 32,767 characters. Now the macro seems to work. Sub Sequelize() Dim rg As Range Dim delimiter As String, s As String Dim i As Long, k As Long, n As Long, nData As Long Dim vData As Variant, vResults As Variant delimiter = ", " Set rg = Range("A2").CurrentRegion Set rg = rg.Offset(1, 0).Resize(rg.Rows.Count - 1, rg.Columns.Count) n = rg.Rows.Count nData = Application.CountA(rg.Columns(1)) vData = rg.Value ReDim vResults(1 To nData, 1 To 2) For i = 1 To n If vData(i, 1) <> "" Then k = k + 1 vResults(k, 1) = vData(i, 1) If s <> "" Then If i > 1 Then vResults(k - 1, 2) = Left$(s, 32767) End If s = IIf(vData(i, 2) = "", "", vData(i, 2)) Else If vData(i, 2) <> "" Then If s = "" Then s = vData(i, 2) Else s = s & delimiter & vData(i, 2) End If End If End If Next If s <> "" Then vResults(k, 2) = Left$(s, 32767) rg.ClearContents rg.Resize(nData, 2).Value = vResults End Sub Please try running the revised macro on your full dataset. If it works, we can then shift discussion to how best to handle the text in the overflow. Perhaps additional columns or rows?4.8KViews0likes9CommentsRe: Concatenating column B values if left adjacent cell is empty
JayNixon, I would like test my code on your actual workbook. It's OK if you replace all the data with the letter "x" as long as you can reproduce the problem. You can post a workbook in this thread, or you could email it to me at first initial last name at my ISP, which is alum dot mit dot edu. I would then like to try reproducing the problem. Fixing the problem is easy if I can reproduce it. It's possible that there is a limitation of Excel VBA present in your version of Excel that is not in mine. All my testing so far has been on 64-bit Excel 2016/Office 365 running on Windows 10--but I have other versions available for testing (both Mac and Windows, 32-bit and 64-bit, 2007 to date). Please tell me: Which version of Excel do you use, Mac or Windows? If Windows, is 2007, 2010, 2013, 2016 or 2019? Is it 32-bit or 64-bit?4.8KViews0likes11CommentsRe: Can I get MATCH to grab the last identical value in an array?
You are probably best off not reading the on-line help for LOOKUP. It will only mislead you. LOOKUP has a number of useful properties: Contrary to the on-line help, the lookup column does not need to be sorted in ascending order If you search for a value so big (either in numeric magnitude or alphabetic sort order) it will always be last, LOOKUP matches the last value of the same data type as the first parameter LOOKUP ignores error values If you give LOOKUP two parameters, it returns the last match from the second parameter. If you give it three parameters, it returns the value in the third parameter that corresponds to the match found in the second parameter. If you use LOOKUP in an array formula, it does not need to be array-entered. It's like SUMPRODUCT and AGGREGATE in that respect. In the suggested formula, the magic happens in the second parameter of LOOKUP. The numerator is the index numbers 1 through 13, while the denominator is the results of your criteria testing. The numerator of the second LOOKUP parameter returns index numbers 1 through 13. Although I could simplify the expression for the given layout, many people don't know how to change ROW($A$2:$A$14)-1 for a different worksheet layout, but have no trouble adjusting (ROW($A$2:$A$14)-ROW($A$2)+1). The denominator is a Boolean expression holding the criteria for which values to include: a match for E2 in column A and F2>= values in column C. Each test returns an array of TRUE and FALSE, which get converted to 1 and 0 when used in an arithmetic expressions. Since the criteria are in the denominator, you get an array of either index number for the rows or a DIV/0! error values. The first LOOKUP parameter 1E+307 is a very large number. Although the formula would work with the given layout using 14, I like to choose a number so large it will always work. Doing so gets people's attention and makes them reluctant to make changes. Finally, rather than applying INDEX to $A$2:$C$14 and then specifying the result come from the second column, I just use $B$2:$B$14 and drop the third parameter in the INDEX function.2.4KViews1like0Comments
Recent Blog Articles
No content to show