excel-formula

[Solved] Look for a word of column 1 and then search that word in another column of another excel sheet and use the adjacent information to fill the cell

by

[ad_1] Look for a word of column 1 and then search that word in another column of another excel sheet and use the adjacent information to fill the cell [ad_2] solved Look for a word of column 1 and then search that word in another column of another excel sheet and use the adjacent information … Read more

[Solved] VBA Excel: How can I use VLookup and IF in VBA?

by

[ad_1] try to use this code: First way (you can use user defined function): Function getSomeData(E3 As Range, Table5 As Range, F26 As Range) getSomeData = “” If WorksheetFunction.VLookup(E3, Table5, 2, 0) >= F26 Then getSomeData= WorksheetFunction.VLookup(E3, Table5, 4, 0) * F26 End If End Function Than you can call it in any cell just … Read more

[Solved] If formula with 3 different conditions [closed]

by

[ad_1] You can’t combine conditions like that. Instead of testing x>=y<=z, you need to use a logical AND and test both x>=y and y<=z. =IF(AND(TODAY()>=E2, TODAY()<=F2),”Running”,IF(AND(TODAY()>=F2, TODAY()<=G2),”Grace”,”Expired”)) 2 [ad_2] solved If formula with 3 different conditions [closed]

[Solved] find and remove partial duplicates in excel

by

[ad_1] In an unused column to the right, put this array formula¹ in the second row. =ISNUMBER(MATCH(LEFT(A2, MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9}, A2)-1, 1E+99)))&MID(A2, FIND(“.”, A2), 9),A:A, 0)) Finalize with CSE and fill down as necessary. Use Data ► AutoFilter to filter on that column for TRUE and delete the visible rows. ¹ Array formulas need to be finalized … Read more

[Solved] How to assign children to parents using Excel?

by

[ad_1] Try this formula: =IFERROR(INDEX($A$2:$A$27,AGGREGATE(15,6,ROW(2:$26)/((NOT(ISNUMBER(SEARCH(“.”,MID($B3:$B$27,LEN($B2)+2,999)))))*(SEARCH($B2 & “.”,$B3:$B$27)=1)),COLUMN(A:A))),””) The AGGREGATE() Function was introduced in Excel 2010. 5 [ad_2] solved How to assign children to parents using Excel?

[Solved] excel popup button to ask how much money to add to an existing cell with an existing amount [closed]

by

[ad_1] Here’s how to connect the macro to a button Private Sub Button1_Click() Dim val As Long val = Application.InputBox(Prompt:=”Enter Amount”, Type:=1) Range(“C43”).Value = Range(“C43”).Value + val End Sub 12 [ad_2] solved excel popup button to ask how much money to add to an existing cell with an existing amount [closed]

[Solved] how to count increasing profit streak?

by

[ad_1] To calculate Years of Increases enter the following formula in Cell L2 =IFERROR(COLUMN(J2)-COLUMN(INDEX(B2:J2,,MATCH(9.99E+307,IF(B2:J2>A2:I2=FALSE,1,””)))),0) and to calculate Years of Steady Profits enter below formula in Cell M2 =IFERROR(COLUMN(J2)-COLUMN(INDEX(B2:J2,,MATCH(9.99E+307,IF(B2:J2>=A2:I2=FALSE,1,””)))),0) Both the above formulas are array formula so commit by pressing Ctrl+Shift+Enter. Drag/Copy down as required. See image for reference. In case you want this formula to … Read more

[Solved] Comparing Sheetnames of different excel workbooks and Storing the result in the third sheet

by

[ad_1] Here, this will do what you want. Option Explicit Sub FileListingAllFolder() Dim pPath As String Dim FlNm As Variant Dim ListFNm As New Collection ‘ create a collection of filenames Dim OWb As Workbook Dim ShtCnt As Integer Dim Sht As Integer Dim MWb As Workbook Dim MWs As Worksheet Dim i As Integer … Read more

[Solved] How to set column conditionally in Excel?

by

[ad_1] If you want the name in column E and the worked hours in column F then set E1 to =IF(B1>0,A1,””) and set F1 to =IF(B1>0,B1,””) If you want the name and worked hours both in column E then set E1 to =IF(B1>0,CONCATENATE(A1,” “,B1),””) and copy down the column. IF(condition, expression if true, expression if … Read more