Search results

Ah, you are correct. Honestly I had not considered this situation / possibility. Since we are dealing with revisions, I thought they'd be unique. As an aside, the way you present solution is quite impressive. @Hui @r1c1 I think some of these can be Chandoo blog post.

If your data is sorted on following fields 1. Number 2. Revision Then you can use a simple formula like below in an empty column =IF(AND(A2=A3,B2<B3),"","Last Record") and then copy it down. You can then filter on "Last Record". Edit: Your requirement of finding last record reminded me of my...

You can use logic like =INDEX(A3:L3,1,COUNTA(A3:L3))-INDEX(A3:L3,1,COUNTA(A3:L3)-1) assuming you have at least two cells filled all the time consecutively. I edited my post little late, what will happen when only cell A3 is filled?

What happens if someone directly fills cell K3 and leaves J3 blank? What happens when there's only one cell e.g. A3 is filled.

I have not tested below logic heavily but it should point you in right direction (assuming I have understood it correctly ;) ). In row 3, E3 --> =A3 F3 --> =MOD(C3,12) G3 --> =MOD(C3,30) In row 4 E4 --> =E3+A4+FLOOR((F3+B4)/12,1) F4 --> =MOD(F3+B4,12)+FLOOR((G3+C4)/30,1) G4 --> =MOD(G3+C4,30)...

If you don't want to use R1C1 route then you can set AutoFilter & filter on blanks. It will show the blank cells. Select them all and then press "DELETE" button. Afterwards, select all cells and then press F5>>Special Cells>> Blanks and then press OK It will select all blank cells. Now...

A little safer construct would be to use =MID(A1,MIN(FIND("T"&{0,1,2,3,4,5,6,7,8,9},A1&"T"&{0,1,2,3,4,5,6,7,8,9},1)),6) as it will test for a capital T which precedes a numeral.

Yes! such formulas rely solely on pattern being consistent. The risk is any change in pattern may not necessarily result in a visible error but may instead return unintended calculation outcome! That is why I suggested a two column output (Vol and Rate on the same row) where all relations are...

As such with your posted layout it will be possible to get the same results by using below formula for cell H7 and then it can be copied across. =SUM((H11:H21*H12:H22)*(($B$11:$B$21="Comp Alpha")*($G$11:$G$21=TRUE)*($F$11:$F$21="Vol."))) This can be surely reduced and simplified if you create...

Here's a code to start with comments. Public Sub CreateSumFormulas() Dim lngLastCol As Long, lngLastRow As Long '\\ Find out last row and column lngLastRow = Cells(Rows.Count, 1).End(xlUp).Row + 1 lngLastCol = Cells(1, Columns.Count).End(xlToLeft).Column + 1 '\\ Insert fixed titles...

You can use INDEX/MATCH like below. Both formulas need to be array entered i.e. CTRL+SHIFT+ENTER. For HeadCount in cell M6 =INDEX($G$5:$G$14,MATCH($K6&$L6&M$5,$D$5:$D$14&$E$5:$E$14&$F$5:$F$14,0)) For %Completion in cell Q6...

You need to modify the code. Attached code shall give you pointers. One thing I noticed is that the code that was previously written (was it copied from somewhere?) would lead to different type of results as your sample text file is fixed width type and not space delimited.

You try below VBA route as well. On Error Resume Next cnt1 = Range("A:A").SpecialCells(xlCellTypeConstants).Count cnt2 = Range("A:A").SpecialCells(xlCellTypeFormulas).Count On Error GoTo 0 MsgBox cnt1 + cnt2

Rahul's code contains all ingredients which need some modifications so as to meet your requirements. Following code is untested but I have added comments to it so it should be easier for you to check and adopt should it run into issues. Option Explicit Sub CopyFileRev2() Dim fso As Object Dim...

This thread on Stack Overflow enlists some of the solutions tried by various users (similar to Debaser's suggestion). https://stackoverflow.com/questions/27411399/microsoft-excel-activex-controls-disabled I remember once having such issue post update and deleting .exd files resolved it.

If I use below code lines. Me.TextBox1.Value = Format(Date, "dd/mm/yyyy") Me.TextBox2.Value = Format(DatePart("q", TextBox1.Value)) Then I get below results respectively as per today's date i.e. 02-Jan-2020 : TextBox1 = 02/01/2020 TextBox2 = 1 They are correct! You need to check the...

If cell C5 shall change based on the changes of E4 then you should appropriately define the relationship. Currently, as it stands, you have defined it based on C4 which will always result in 27.4.

You can also use INDEX/MATCH as well. =INDEX(B7:J15,MATCH(A3,A7:A15,0),MATCH(A2,B6:J6,0)) Also a VLOOKUP as well =VLOOKUP(A3,A6:J15,MATCH(A2,A6:J6,0),0)

I liked your approach. It also makes the decision (inner calculation) visible as well. There's nothing special in my formula as such, it is the same unitary formula repeated thrice.

Assuming that the cell A1 holds input value, you can test below formula: =SUMPRODUCT(--(MOD(ROW($A$1:INDEX(A:A,A1)),3)=0),--(MOD(ROW($A$1:INDEX(A:A,A1)),6)<>0),--(MOD(ROW($A$1:INDEX(A:A,A1)),9)<>0))

The only thing that comes to my mind reading this thread is that Windows 7 extended support is also coming to an end https://home.bt.com/tech-gadgets/computing/windows-7/windows-7-support-end-11364081315419 Was there any hard requirement not to migrate to Windows 10? If option is available...

Thanks for the feedback! Please also take note of cross-posting and general rules of posting online.

Modify the for loop as below and see if it helps. Dim strOut As String For Each oCell In Worksheets("SPOT").Range("A1:A90") strOut = Join(Application.Transpose(Application.Transpose(oCell.Resize(1, 25).Value)), vbTab) Print #intFH, strOut Next oCell

TRIM will reduce more than one spaces to one when they exist between words and it will remove all extra spaces if they are preceding or trailing the string. If there's one space remaining between words then applying TRIM won't result in anything.

After bit of searching around, what I understood is: - Excel uses UTF-8 (UTF = Unicode Transformation Format) encoding. Simple way to find this is to save Excel file as .zip and then navigating to "yourexcelfilename.zip\xl\worksheets" and opening the xml sheet which will show the encoding used...