Search results

I am still struggling to understand the issue here. Here's one example based on the understanding that you need formula to manage year-wise break up. Formula looks like below which is just to ensure that we have a number closer to rounding...

You can use an IF construct like below which is simple but lengthy. =IF(K3<>"",K3,IF(H3<>"",H3,IF(F3<>"",F3,IF(D3<>"",D3,IF(B3<>"",B3,""))))) If rest of the cells are always blank then you can also use which will be easier to extend if you have more columns...

Can you post a sample where the formulas (yours as well as Guido's) do not work with the results expected? As of now, for the example you used i.e. your own formula works.

Great! Thanks for confirming. The code with /b switch will look like below. Call Shell(Environ("COMSPEC") & " /c copy """ & Src_Pattern & """ /b """ & Target & """", vbMinimizedFocus) There is a simple way of understanding double quotes as and when you are using them in VBA. Suppose the...

See below code with two demonstrations. Src_Pattern = "C:\Temp\feed test\*.txt" Target = "C:\Temp\feed test\Combined.txt" '\\ With Quotes Call Shell(Environ("COMSPEC") & " /c copy """ & Src_Pattern & """ """ & Target & """", vbMinimizedFocus) '\\ Another way using Chr(34)...

Hi All, Recently, I decided to take a plunge into posting online content and I have posted my first video on 05th May, 2021. The link to this video is I am hoping to build on a series of simple videos that explain some of the aspects of Excel and learn as I go along. The videos will be in...

This is fairly straightforward if you have FILTER formula available to you which will work like below. In cell E3: =FILTER(D3:D10,A3:A10="Apples") If you do not have access to FILTER formula or you need to maintain compatibility with older versions of Excel then you can use below Array formula...

If the intent is to color alternate rows alone then use table and coloring style that suits you. This will help you get rid of conditional formatting.

In that case you can use: =TRIM(MID(SUBSTITUTE(","&A1,",",REPT(" ",99)),3*99,99))&" "&TRIM(MID(SUBSTITUTE(","&A1,",",REPT(" ",99)),2*99,99))&" "&TRIM(MID(SUBSTITUTE(","&A1,",",REPT(" ",99)),1*99,99)) It looks longer but it is just the same formula i.e. TRIM(MID(SUBSTITUTE(","&A1,",",REPT("...

If you have access to TEXTJOIN and FILTERXML functions as well as if your data is as consistent as in the sample then you can try below formula. =TEXTJOIN(" ",TRUE,FILTERXML("<t><d>"&SUBSTITUTE(A1,",","</d><d>")&"</d></t>","//d["&{3,2,1}&"]"))

Okay, in such case you could use below construct with the dataset posted at #1 which will check for minimum possible difference between reference values and locate...

Thanks for informing. What exactly will be outcome of approximate matching? Can you explain with few examples?

Please see if below formula works for you, implement this in cell C9 and copy down as much needed: =INDEX($A$1:$D$1,1,MATCH(B9,INDEX($A$1:$D$7,MATCH(A9,$A$1:$A$7,0),0),0)) Note: I have tested it in Office 365 and I am little unsure if it will work the same in other versions of Excel. Post back...

With your data you can probably try below formula and see if it helps your situation. =SUMPRODUCT(LEFT(B2:B8,FIND(" ",B2:B8,1)-1)*1440)+SUMPRODUCT(TRIM(MID(SUBSTITUTE(B2:B8," ",REPT(" ",99)),99*2,99))*60)+SUMPRODUCT(TRIM(MID(SUBSTITUTE(B2:B8," ",REPT(" ",99)),99*4,99))*1)

Its manual equivalent is CTRL+END+UP from the last row of a sheet where Range("A" & xx) represents column A. Please refer this article for ways to find the last row. It is third item in the article...

Please clarify below queries. 1. Does the range N1:O10 have any relation to calculations? If yes, how exactly is it supposed to be used in formulas. 2. You have two different formulas in ranges B2:K11 and B15:K24 respectively. Do you need assistance with either one or both of them? 2. In...

What the posting person means here is demonstrate all columns correct results manually so that a formula or other approach could be verified! This would be because the logic that you think is clear may not be clear at all for the other person!!

Posted macro will fail / stop if there's cell that doesn't contain space. This condition means that it will not work with blank cells either. So a test needs to be added to see if a cell contains a space. See below edited code. Sub SplitDataInTwoColumns() Dim wks As Worksheet Set wks =...

Sorry, I do not understand your requirement. After finishing the data, do you want to keep 10 blank lines and then create an entry which says "End of Data"?

It can be done with formulas as well. Below is basic example of code that should work, edit it to suit your needs. Sub SplitDataInTwoColumns() Dim wks As Worksheet Set wks = ActiveSheet For i = 1 To wks.Range("A" & wks.Rows.Count).End(xlUp).Row wks.Range("B" & i).Value =...

Thanks for clarification! I mistook it as "duration" based prompt. However, as you have rightly pointed out it is more connected to user behavior and trigger lies with user's subsequent actions. If user chooses to ignore the warning that "Macros are not enabled" and perform some operations then...

This question arose out of curiosity. Another discussion on StackOverflow led me to this question. Reference to this discussion can be found here: https://stackoverflow.com/questions/65746707/why-would-my-excel-2010-udf-quit-working-with-a-name-error This applies to users who have not changed...

If you are okay with helper columns then simpler approach can be used. Helper#1 Calculate cumulative running total for pick orders =SUMIF($B$3:B3,B3,$C$3:C3) Helper#2 Calculate cumulative running total for scanning template =SUMIF($G$4:G4,G4,$H$4:H4) Helper#3 Based on Helper#2 Qty...

The attached file is corrupt. Please reupload a new sample.

If you add 53 weeks to a date in 2021, you are bound to end up in 2022! :) =IF(E4="","",E4-WEEKDAY(E4,2)+1) Use this formula which can give you desired results. Edit: @GraH - Guido beat me to it.