Search results

You won't get much simpler than that.

In the attached, on the Record-Data_New sheet (I've hidden columns F:L), there's a new table at cell N1. This table's columns O:R are the same as your table, but column N is an added index to give each row a number, and column S shows the count of other rows the row overlaps with, while column T...

Taster of a single cell formula at cell N31. Compare with your matrix above it:

Do you have the GROUPBY and/or PIVOTBY functions available as functions in a cell in your version of Excel?

Have a look at the formulae in the SUMIFS DYN LEDGER 3COL 4CRIT sheet, cells T2, U2, S15 and N26 in the attached; they're a bit shorter. I'll look at the rest of your queries later.

Do you have the GROUPBY and/or PIVOTBY functions available as functions in a cell in your version of Excel?

Same again, try in AF2:=SORT(UNIQUE(FILTER(BUSINESS:VILLAGE,BUSINESS<>"")),{1,2})

There's quite a lot to be getting on with and I haven't looked in depth at all your queries; I'll do it bit by bit. First, perversely, is Question 2 sorting the 2-column, cell N2 spill (Business then Village). Try in cell N2 of the SUM LEDGER 2 COLUMN sheet...

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