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

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 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 … Read more

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

by

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 typing … Read more

[Solved] find and remove partial duplicates in excel

by

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 with … Read more

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

by

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 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

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 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

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 be … Read more

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

by

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] vba code to search google using cell content as search criteria [closed]

by

There’s no need for VBA if all you want to do is open a Google search page one at a time. With your search phrase in A1, use this formula, for example: =HYPERLINK(“http://www.google.com/search?q=” & A1,A1) and fill down as far as required. That will put a clickable link in column B, corresponding to the search … Read more

[Solved] How to set column conditionally in Excel?

by

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 false) … Read more