Search results

With the regex solution you don't always need a space. It's looking for a 'word boundary' (that's the "\b"s in the formula), this includes the beginning or end of a phrase, things such as commas, semicolons. colons, full stops count as word boundaries. If you still want to find NextCare or...

One way, but you have to remember to sort by the TPA column so that the longer strings are at the top: Another way could be to use REGEXTEST if you have such a function available as a function on a sheet? This will need an understanding of regular expressions, but then the data won't need to be...

Since you're now using a version of Excel at least version 2016 then you can use Power Query to do this. See table at cell J14 of the attached. Should you change the table at A5 you'll need to refresh the table at J14 by right-clicking somewhere within it and choosing Refresh.

In the attached, the Unit Price column of the table at cell A32 of the sheet Ch Inv has been reduced by 10% (=x 0.9). No other columns are affected (the Total column remains the same).

and it goes on: =SORT(UNIQUE(TOCOL(CHOOSECOLS(A2:D15,1,3,4),1))) Table version:=SORT(UNIQUE(TOCOL(CHOOSECOLS(Table1,1,3,4),1)))

The article is here: https://chandoo.org/wp/9-box-talent-map-template/ and there's a link to the workbook therein here: https://chandoo.org/wp/wp-content/uploads/2020/11/9-box-grid.xlsx I strongly suspect @Trashman is using O365 since he refers to it.

A start: At cell D2 of the Action sheet in the attached workbook is your table of Sales Order numbers that you want included in the other 3 sheets. Because this is a proper Excel table, when you edit this table make sure that all the order numbers you want included are within the confines of...

Cell E2:=IF(MAXIFS(C2:C30,A2:A30,A2:A30)=C2:C30,"Latest","")

In a cell in row 2: =MAXIFS($C$2:$C$23,$B$2:$B$23,B2)=C2 and copied down will show true/false (true if the latest date). However, this assumes that your 'given customer' is based on column B (AccountName). If this is not the case, the and the given customer is based on column A then it becomes...

Taking @Debaser 's idea a bit further by not having a new column step, with the likes of:= Table.SelectRows(Source, each not (List.AllTrue({[Invoice Balance2]=0,[Debit Memo Balance]=0,[Credit Balance]=0,[#"Unapplied Payment "]=0,[#"On-a/c Credit Memo "]=0,[#"Account Bal3"]=0})))where Source is...

Updated attachment in my last message.

Correction: You would need a refresh if you change the Hols table too, however, in the attached I've moved all the tables to a Tables sheet. When you move away from that sheet the query is refreshed (a one line macro).

Oops, there is now.

Yes, you'd need to refresh the table at cell K2 (right-click and choose Refresh) if you change the Courses table at cell C2. We could automate this if you want. At the moment, a change in size of that table will probably shift cells around elsewhere on that sheet so perhaps put that table in a...

I have updated the file attached to msg#5

See attached.

Have a look at the light green shaded area (d15:h17) in the attached. If this is what you want then we can tidy it up (it's very rough and ready at the moment). It uses a combination of Power Query, named ranges and a one-line macro to do the job. Don't alter the formula in cell N2 (yet). You...

See the two pivot tables in column N in tha attached.

You have monthly subtotals in the plot, they shouldn't be there.

re: =SUM(L153-K153)*24 The SUM part is superflous, all you need is =(L153-K153)*24 which is what I proposed in msg #3. It's to do with something called 'operator precedence' (internet search it); the order in which calculations are done. Multiplications are done first then subtractions...

See in the attached whether the last step in query Table3 (2) called AddedCustom is giving you the expected results. it uses List.Sum() which ignores null values: =List.Sum({[#"[A]Current (Home Currency)"],-[#"[B]Unapplied Payment (Home Currency)"],-[#"[C]On-Account Credit Memo (Home...

No, but it does equal right days (and fractions of days). All dates and times in Excel are in units of days. 12 hours (half a day) is 0.5 (put 0.5 in a cell and format at it as [HH]:MM and you'll see 12:00. The underlying value will still be 0.5). CONVERT(F29-C29,"day","hr") only multiplies...

…now where in Hull?

=(F25-C25)*24 ? Ensure Excel doesn't automatically convert the cell format to time or date after you commit the formula to the sheet (make sure it stays as a plain number or General).

Does it have to be vba? In cell E1: =SUM(--ISNUMBER(SEARCH("HRG",B3:B12))) This is a case-insensitive search, for a case-sensitive version change SEARCH to FIND Depending on your version of Excel, you may have to commit this formula to the cell with Ctrl+Shift+Enter rather than the usual Enter...