Native worksheet formulas simply do not handle string concatenation well. Even the new string functions that are being introduced such as TEXTJOIN function¹ and the updated CONCAT function² have difficulty with conditional concatenation beyond TEXTJOIN’s blank/no-blank parameter. Here are a pair of User Defined Functions (aka UDF) that will perform the tasks. Function udfUniqueList(rng As … Read more
Your description is not that detailed, but I took a shot, and from what I understand you might be looking to have something like this: Where you would put a number into B1, and then column C would print out as many statements as requested. Here’s the formula to that: C1=IF(ROW()<=B$1,”Enter load for Span ” … Read more
Since you don’t seem to have a problem using helper columns, this can be done in a fairly straightforward way, given that your data is already sorted by date. RAW DATA TAB Add a new column (we’ll call it column X) which checks to see if your cell is the first cell which starts a … Read more
Try this formula =MATCH(G5,{“Prica Plus”,”Miks S”,”Miks M”,”Miks L”,”Miks XL”,”Miks XL Plus”,”Miks XXL”,”Super”},0)-MATCH(F5,{“Prica Plus”,”Miks S”,”Miks M”,”Miks L”,”Miks XL”,”Miks XL Plus”,”Miks XXL”,”Super”},0) Above formula can be broken down in two parts. Firstly, matching Cell G5 to your values =MATCH(G5,{“Prica Plus”,”Miks S”,”Miks M”,”Miks L”,”Miks XL”,”Miks XL Plus”,”Miks XXL”,”Super”},0) Secondly, matching Cell H5 to your values =MATCH(F5,{“Prica Plus”,”Miks S”,”Miks … Read more
With you setup you would use INDEX/MATCH with your FIND as an array formuls: =INDEX($D$1:$D$3,MATCH(TRUE,ISNUMBER(FIND($C$1:$C$3,A1)),0)) being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode. 1 solved Excel substitute for FIND Function when searching for multiple criteria
How can I output the matching portion of two strings, while having the % character separate any non-consecutively matching portions? solved How can I output the matching portion of two strings, while having the % character separate any non-consecutively matching portions?
Use the following VBA code to transpose data with spaces. This will not remove the original code. Sub Transpose() Dim rng As Range Dim i As Long Dim j As Long Set rng = Cells(Rows.Count, 1).End(xlUp) j = 1 For i = 1 To rng.Row Step 5 Cells(j, “B”).Resize(1, 5).Value = _ Application.Transpose(Cells(i, “A”).Resize(6, 1)) … Read more