5

If you don't have access to the UNIQUE function, you can use a PivotTable to generate the desired result. There are a few idiosyncrasies with PivotTables but it'll get the job done. 1. Create the PivotTable Click anywhere in your data and, on the Insert ribbon click PivotTable Make sure the range is your entire table and click OK. The range may be wrong if ...


3

If you have Excel 365, you can use these formulae: name: =UNIQUE(A2:A7) date: =MAXIFS($B$2:$B$7,$A$2:$A$7,E2) score: =FILTER($C$2:$C$7,($A$2:$A$7=E2)*($B$2:$B$7=F2))


1

Try: If ActiveCell.Offset(0, -2).Value = "W1" Or ActiveCell.Offset(0, -2).Value = "OUTSIDE" Haven't done VBA in forever and do not currently have it but: ActiveCell.Offset(0, -2).Value = "W1" Or "OUTSIDE" does not look correct to me, as '=' has higher precendence than the logical operator 'Or', so this is doing : (...


1

Since the formula works, adjusting the search range should help to reduce the formula load. Try : =FILTER(tab1!L1:L6000,tab1!K1:K6000="US") Please share if it works/not.


1

When you are using a formula IF(H1=FALSE,[VALUE IF TRUE],[VALUE IF FALSE]) the formula checks for a FALSE or a blank cell in H1. If you wish to retain your current requirements, put the if blank at the beginning. =IF(IF(H1="","",IF(H1=TRUE,"Tested",IF(H1=FALSE,"Not Tested","ERROR"))) The "ERROR" bit ...


1

This sets vertical borders for all cells in the usedrange after row 1. 'Sets vertical borders Dim iRange As Range Dim iCells As Range Set iRange = ActiveWorkbook.Worksheets("Sheet1").UsedRange.Offset(1, 1) For Each iCells In iRange If Not IsEmpty(iCells) Then With iCells.Borders(xlEdgeRight) .LineStyle = xlContinuous ...


1

Doing this with an array formula with Excel 2016 will be quite involved. Why not use an easier approach that doesn't involve complicated formulas or VBA? Create a list of dates from the start date to the end date. In the next column, use a Match formula to find the date in your data. You could use a formula that returns a TRUE/FALSE result, like =isnumber(...


1

If you are OK with a thin black border, then conditional formatting will work. If you really want a thick black border, you will need VBA event code, since you cannot modify the thickness of a border in the conditional formatting border property. To enter this event-triggered Macro, right click on the sheet tab. Select "View Code" from the right-...


1

This is easily accomplished with conditional formatting. Make the active cell J2 Open up the conditional format dialog box Select Use Formula for Conditional Formatting Type in the formula =$J2<>$J1 and set the border as you like Adjust the Applies To cells to make the underline go to the cells you want


1

What you describe, literally, in your numbered example set of actions is how Excel has always worked. When you copy a CELL, whole, Excel will only paste it whole, not inside a cell as part of that cell's contents. Excel has ALWAYS required the "cumbersome" workaround you allude to in order to paste into a cell's contents vs. pasting a whole cell ...


1

I found an alternative solution here: Sorting problem. Under "pivot table options" -> "totals & filters" un-check the box for using custom filter.


Only top voted, non community-wiki answers of a minimum length are eligible