New answers tagged

0

I've added Income to table A from your earlier post. I then made a copy of the query, and edited it like this: let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], times = List.Times(Time.FromText("00:00:00"),1440,Duration.FromText("0:01")), #"Changed Type2" = Table.TransformColumnTypes(Source,{{...


0

As the OP is using Google Sheets, there are Google Sheets' functions that will help extract the output. Insert another column. It will be used to display the results. In this example the inputs are in column A, the outputs are in column B. In cell B2, enter the formula: =JOIN(CHAR(10), REGEXEXTRACT(A1,REGEXREPLACE(A1,"(Player \d+)","($1)&...


1

Use SUBTOTAL to count the number of visible rows on a variable range: =AND(ISEVEN(SUBTOTAL(3,$A$1:$A1)),$A1<>"")


0

Here I have used PowerQuery to extract the data from Wikipedia, transform into the format in your post, then extract the players into a new table, with a link to the original data. I include this simply because I did it to get the same data as you and it was an interesting exercise for me and I hope it might be interesting for others. To get directly to the ...


0

Converting my comment to an answer. The short answer is "no", the &[File] macro includes the extension, if you tell the Mac's Finder to hide the extension. In the past you could remove the extension entirely, something supported in old Mac OS versions and Excel, but not anymore (just tried it). If this is for printing, just use the Sheet name ...


0

Shift + right arrow selects the corresponding rows of both A and B Then Ctrl + Tab moves the active cell to the last cell in the range (is it Option + tab on a Mac? Whatever reverses direction on various shortcut keys) Then shift + right arrow deselects column A Optionally, tab moves the active cell to the top of the selected range (you might not need this, ...


0

To create a Histogram in Excel: Select the entire dataset. Click the Insert tab. In the Charts group, click on the 'Insert Static Chart' option. In the HIstogram group, choose the one you need and click on the Histogram chart icon.


2

Edit given your clarification that the relevant strings will always be in the format of xyz3##, and that you do have Windows Office 365, we merely have to change fn to reflect that: Edit2 Formula changed to account for situation of no matches. In that case, the FILTER function returns an error which COUNTA would count =LET(ss,FILTERXML(SUBSTITUTE(SUBSTITUTE(&...


1

The problem here is that the way you've set up the formula, you're effectively saying If the first row contains any of the words Off, Vac or Flex, then return 0 [and don't do anything else] The important thing here is that IF will exit as soon as the first condition it finds to be TRUE is met. So, it's found that F38 contains Flex, and it's stopped there. ...


0

After testing a couple of times, it seems that the sidebar will go away only if you open the faulty file FROM THE SIDEBAR. If you open Excel by clicking the fauly file, or open it from Excel's start screen, then it won't help whatever you do. The sidebar will reappear next time you open Excel. In other words, to get rid of the sidebar: Start Excel. Open ...


0

I believe something like this should work: =XLOOKUP(B3&C3,A12:A16&B12:B16,C12:D16) Here, we are joining the values in column B and C in the first parameter (i.e. B3&C3). In row 3, that gives us the value "asset managementcustodian transfer". This is the lookup_value for XLOOKUP. We are looking for that value in the array that is formed ...


0

The only way the reported results and second comment make sense is if, instead of the formulas reported in the first table, which would give something rather different from the reported results, Vinz243 is actually NOT using the INDEX() function at all, just =Letters. That WOULD give the reported result due to implicit intersection, which was very present in ...


0

Change this portion of the second formula (the unsuccessful one): MAX(("'"&A9&" & '!"& A:A <> "" "") (noting the areas I've stretched out with spaces) to this: MAX((INDIRECT("'"&A9&"'!"&"A:A")<>"") Presently there are several sins ...


0

cjb110 in 99.999% certainly right in his comment. The reason it doesn't matter if you format as a date or as outright text is because, to Excel, it is outright text and looks identical to it either way. Also as pointed out, Excel, in the situation, will actually do an alphabetical comparison, and "S" is "greater than" "O" as it ...


1

Could you please tell us which verion of Office are you using? If you have one of Professioanl Plus versions, you could try the Group Policy to disable this shortcut. User Configuration > Administrative Templates > Microsoft Excel 2016 > Disable Items in User Interface > Custom, enable "Disable shotcut keys". Then enter following "[...


1

If you have Office 365, and if your desired output is incorrect given your input, and the column you labelled House ID in your output is really the House, then you can use the following formula: H2: =IFERROR(FILTER(Pref[[Preferences]:[Preferences]],(Pref[[House]:[House]]=$G2)*(Pref[[Preferences Rank]:[Preferences Rank]]=COLUMNS($A:A))),0) then fill down and ...


1

Maybe this is what you want. Please confirm. Sample data is in Cells A1:C8. Row 1 is the header. The formula in C2 is =IFERROR(INDEX($A$2:$A$6,MATCH((ROW()-ROW($D$1)),$B$2:$B$6,0)),0) Drag it down along the length of the intended rows. Since the data starts in Row 2, ROW () - ROW ($D$1) starts the Index with 1 and further for the MATCH Function. It can be ...


1

Hover the mouse pointer over the text that has been formatted automatically and click the Autosuggest Options button. This button is very small, so look closely at it when you click the mouse pointer. To remove the formatting for the selected text only, click Undo. For example, if an Excel hyperlink is automatically created and you want to delete it, click ...


0

Maybe an answer: The "Format Cells" dialog, i.e. CTRL+1 has an Alignment tab, where you find [x] Wrap Text... To set or Reset that option for the entire sheet; CTRL+A, CTRL+1 and remove/set [x] Wrap Text This might help... I can imagine that you have Wrap Text enabled for any adjacent cell when the "problem" occurs.


1

I suspect the issue is the translation of custom numbers formats into local languages eg YY would be JJ auf Deutsch and AA en Francais. You might have to parse your date into chunks to get this to work. In your formula: =IF(ISBLANK(B5),NA(),B5&" "&TEXT(I5, "[$-0809]dd/mm/yy")) Try replacing the TEXT bit with this: TEXT(DAY(I5),&...


1

You can use the ASK or Fill-In field that activates on a new document or you could use a vba UserForm. A UserForm is more "elegant" but requires that the user allow macros to run. Here are two MVP pages on UserForms. Create and Employ a UserForm by Greg Maxey Create a Simple UserForm by Graham Mayor This is intermediate vba work. The Ask and Fill-...


0

If the value for in the "Total" line (19) for column C is the sum of all items divided by the number of items, the "2.66" is flat wrong and one hopes is some kind of typo after obtaining the original data and before taking the screenshot of it. If it is the sum of the column entries divided by the number of non-zero entries, then it fits: ...


0

When you enter the Sorting functionality in Excel, it gives you the dialog for telling it your sort keys and orders. You then tell it to perform. If it detects numeric-looking data in cells in the sort range's sort key columns (or rows if doing a horizontal sort), it will ask if it should treat the data AS numbers or not: Treat Numbers as Numbers? Dialog Box ...


1

There is NO way excel will recognize that text string as a date. It needs to be manipulated in a second column (or in place using VBA) to something Excel can recognize as a date. Then you will be able to format it. One method: Use a formula to convert that text string to an actual Excel Date. =--(TEXT(--SUBSTITUTE(A1,"_",""),"0000\/...


0

To do this, you need to create a sorting group. You can use this formula: =ROUNDUP(INT(RIGHT(A5,LEN(A5)-1))/2,0) Like this: You can then use the sort dialog configured like this: If you want to change it so that you are sorting three of the values in the Row column at a time, you would change the denominator in the formula to 3, and so on.


0

Select your columns with mouse (or CTRL-A to select everything, if this is the only entry in the whole sheet) Select "Filter" from Data menu: first row of data (labels) present a new arrow sign: click it and it gives the sort menu select anything that suits you and it will be sorted in a paired way for all selected cells


0

This is a little embarrassing, I figured it out only a moment after I spend some time and effort to formulate my question. So I hope it will help to others. The solution is Record.FieldNames. If all object in JSON data Array have same set of properties, we can just extract FieldNames from any Record. Source = Json.Document(...), data = Source[data], ToTable ...


0

In the table on the right, add a column to join the names together, eg in column L: =$J2&$K2 In your main table, add a column to find which name is being referred to. In column G (or wherever suits you), enter: =IFERROR(MATCH($C2&$D2,$G$2:$G$999,0),9999) So for every name that is found, you now know what row it is on, and for names with no match ...


0

It sounds like a job for a Pivot Table, using a tiny bit of the Power Pivot functionality. Select your data (which ideally wants to be in a table, but any range will also be fine). Insert > Pivot Table. In the dialog box, select the check box to "Add this Data to the Data Model". This is crucial. Now build your Pivot Table as usual, something ...


1

It appears you want to have a result with two columns date and data. If that is the case, all you need to do is expand the table that results from Step 3. Merely click the double headed arrow at the top of the column M Code let Source = Json.Document(File.Contents("C:\Users\ron\Desktop\new 3.json")), data = Source[data], #"...


0

The set-ups involving CHOOSE as given previously are perfectly good, though have two drawbacks: They do not allow a contiguous range to be entered, e.g. A1:A6 As such, each individual cell must be referenced as a separate argument within CHOOSE; for large ranges this could be become a significant inconvenience An alternative, which does not suffer those ...


0

You can approach the "IF" part differently. =COUNT(SEARCH("Area 25",IF(NOT(ISNUMBER(VALUE(RIGHT(A1:A10,1)))),A1:A10,""))) RIGHT(something,1) finds the last character of every STRING, then VALUE() tries to make it a number. ISNUMBER() tests the success of that, and NOT() reverses the TRUE/FALSE values found by it to be FALSE/...


0

=FILTERXML("<Group><Element>"&TEXTJOIN("</Element><Element>",TRUE,A1:A3,C12:C16,TRANSPOSE(C1:D5))&"</Element></Group>","/*/*") This will not work for Excel 2013 due to TEXTJOIN(), and CONCAT(), which can stand in for it, using LEFT(), or another approach, to cut off the &...


0

Clearly, the work is done outside of Excel. If it were VBA, you'd surely have altered it to handle the desire by now. If you cannot change the import process in the outside tool or routine, you only have one real option. VBA cannot help during an ongoing import done by a non-Excel family routine because it has no way to get its foot into that door. If you ...


0

This should get you started in the right direction: =OFFSET($H3,0,2+XLOOKUP(0,INT($K$2:$V$2<>""),SEQUENCE(1,12),"")-1)="" INT($K$2:$V$2<>"") is returning a 12 column array of integers, containing either a 1 if row 2 has a value in that column (starting with K, which is where the first column header of my ...


1

You need a VBA function for summing up all numbers from a range of cells, even for the case that there are several numbers in the same cell. The function can look like: Function SumNumbers(rngS As Range, Optional strDelim As String = " ") As Double Dim xNums As Variant, lngNum As Long For Each elem In rngS xNums = Split(elem, strDelim)...


0

=WEEKNUM(Date;Mode) Excel or LibreOffice Hit F1 then type "WEEKNUM" into the search field and have it all revealed. Mode sets what is to be considered to be the first day of week 1 in any year. Ref: LibreOffice 7.2 Help on WEEKNUM


4

If there is always only 1 digit and it is always on the end: =SUM(IFERROR(--RIGHT(A1:A4,1),0)) Some Older versions will require the use of Ctrl-Shift-Enter instead of Enter when confirming the formula. Right pulls the right most character and the -- tries to turn it from a string to a number, if it cannot become a number it will error and the IFERROR will ...


0

The cause appears to be HKCU\SOFTWARE\Microsoft\Office\16.0\Common\ExperimentConfigs\ExternalFeatureOverrides\excel\Microsoft.Office.UXPlatform.FluentSVRefresh = 'true' This setting, along with two more ribbon-related settings, causes Excel to change appearance to what is meant to be the 'new' Office look, and introduces all the abovementioned issues. It is ...


1

=WEEKNUM(A1)-IF(WEEKDAY(YEAR(A1))=1,0,1) =WEEKNUM(TODAY())-IF(WEEKDAY(YEAR(TODAY()))=1,0,1) Assumes A1 is a valid date. TODAY() can be substituted for A1 WEEKNUM returns 1 to 54 corresponding to start of year and Sunday (or other days with modifiers). No problem if year starts on Sunday, otherwise when Sunday rolls around week count is off by 1. If Jan 1 ...


2

Lets start from beginning let Source = Json.Document(...), data = Source[data], #"ToTable1" = Record.ToTable(Column1) Now we have List of Records stored in #"ToTable1"[Column1]. (Because table Columns are Lists.) So we can use List.Transform function to manipulate with each Item of this List. Syntax is List.Transform(list as ...


0

No answer, but it changed all my office programs and deleted all my shortcuts. MSFT improving efficiency I guess


0

Good Day Pete, This example in the image below should provide you a starting point for your solution. The formula in F3 provides Unique students in week 1, the second is unique students in grade 7 in week 1. I hope this helps, Brad


0

You can try this: =ROUNDUP((DAYS(A2,DATE(2022,1,2))+1)/7,0) where the date you want to return the week number for is in cell A2.


-1

Do you have Microsoft 365 Apps? If yes, you could use Xlookup function to find the data contained in right table. Such as =XLOOKUP($I2&$J2,$C$2:$C$7&$D$2:$D$7,$A$2:$F$7), drag down to get other data. Then you could use array formula to find the data that not included in the table on the right. Such as =INDEX(A$1:A$7,SMALL(IF(COUNTIF($I$2:$S$6,$C$2:$...


-1

Give everybody Firstname ID and LastName ID (your oreder list), then add ID columns also in your data table. Then VLOOKUP the ID values, you get what you want!


1

It sounds like you want to type a hex color value into a cell and have the cell to the right change to be whatever color you typed in. That can be done with VBA. This sub is written to leave in the Sheet object although you could generalize it to work in the Workbook object if you want it to apply to all sheets. Private Sub Worksheet_Change(ByVal Target As ...


0

You can put a FILTER function in a range somewhere then refer to that: Formula in cell I4: =FILTER($A$2:$A$16,$B$2:$B$16>=$J$1) Formula in column C: =IFERROR(RANK.EQ(A2,$I$4#),"") It would be nice to put the FILTER inside the RANK.EQ function but that doesn't appear to work.


1

The "asdf" was never Copied to the Clipboard, Windows or otherwise. It was simply highlighted. Presumably Copied via a keyboard command as the mouse was not used for it. But it never made it to the Clipboard. If I saw the GIF and in it the mouse right-clicked and chose "Copy" I'd see things differently. But that is not the case. As to the ...


0

I am making some assumptions here. You have hidden rows and and you want to exclude numbers in those hidden rows. According to your picture your start row will be H13 and I am assuming it will go until H100... but you can change that H100 to any rows of your need. Please use the following formula to H13 and pull down. =AGGREGATE(9,5,$G$13:G100) Make sure ...


Top 50 recent answers are included