Search results

If it is within organization then it should not be really an issue if you all have the same version i.e. office 365. However, if variations are expected then a VBA based UDF can be introduced. Is that what you are looking for?

If you have Office 365 then you can try below formula: =UPPER(TEXTJOIN("",TRUE,LEFT(FILTER(FILTERXML("<t><d>"&SUBSTITUTE(A2," ","</d><d>")&"</d></t>","//d"),ISNA(MATCH(FILTERXML("<t><d>"&SUBSTITUTE(A2," ","</d><d>")&"</d></t>","//d"),$F$2:$F$6,0)),"")))) We first split all words and then test...

See below code which you can adopt to suit your needs Option Explicit Public Sub ReArrangeSecondTable() Dim strTbl1Col1 As String, strTbl1Col2 As String, strTbl2Col1 As String, strTbl2Col2 As String, strTbl2FCol As String Dim lngDataStartRow As Long, lngDataEndRow As Long, i As Long...

If you want to convert in the same cell then you can test below sample code and then adopt it to loop through the list. Dim rng As Range Set rng = Range("A2") rng.Value = Evaluate("=DATE((LEFT(" & rng.Value & ",4)),(MID(" & rng.Value & ",5,2)),(RIGHT(" & rng.Value & ",2)))")

Hi Sachin Does this mean that first table will be fixed and second input is to be rearranged?

Post your workbook with code, form and sample data. It would be much easier to check and advise for the users here.

If you have Office 365 and access to functions (SEQUENCE,TEXTJOIN) then for Zeroes the formula would be: =TEXTJOIN(",",TRUE,IF(MID($A2,SEQUENCE(99,1,1,1),1)="0",SEQUENCE(99,1,1,1),"")) For O Letter: =TEXTJOIN(",",TRUE,IF(MID($A2,SEQUENCE(99,1,1,1),1)="O",SEQUENCE(99,1,1,1),""))

Since OP had mentioned VLOOKUP and MATCH, I demonstrated with it limiting the newer function to its core requirements. Unfortunately, we have not received update with LET so I get the typical _xlfn.LET on my computer with your file. New functions added by Microsoft are powerful and appear more...

If you have Office 365 and FILTER function then following approach can be used: To find out companies matching criteria: =FILTER(B3:B10,C3:C10=K1) And then a typical formula to produce rest of the data: =VLOOKUP($J3#,$B$2:$G$10,MATCH(K$2,$B$2:$G$2,0)) Please see attached file for demonstration.

Are you expecting only alphabets? What defines O is correct and 0 is incorrect? It will be better if you could post few examples of data with manual demonstration so that it becomes clearer.

Your formula needs a small correction to match the ranges. See red marked change. =XLOOKUP(H2,'Global Hierarchy-CEXTCE_SEP-20'!C$8:C$14320&'Global Hierarchy-CEXTCE_SEP-20'!D$8:D$14320&'Global Hierarchy-CEXTCE_SEP-20'!E$8:E$14320&'Global Hierarchy-CEXTCE_SEP-20'!F$8:F$14320&'Global...

You can probably try below formula and see if it helps: =XLOOKUP(B10,$B$2:$B$5&$C$2:$C$5&$D$2:$D$5,$A$2:$A$5,"",0,1)

Look at OP's avatar, someday I am sure "he'll be back!" ;)

For just one sheet (data in Sheet1 cell A1 and check data in Sheet 2 column A to F) , I can think of following formula which will make it crawl. =ADDRESS(SUMPRODUCT((Sheet2!A:F=Sheet1!A1)*ROW(Sheet2!A:F)),SUMPRODUCT((Sheet2!A:F=Sheet1!A1)*COLUMN(Sheet2!A:F)),,,"Sheet2") I would suggest using a...

From the description it appears below is your requirement: - Enter a value in A1 - Search for the value in multiple tabs in the workbook a. Are you looking for an exact match or partial match? b. Are you looking for a specific column or all columns in each tab? c. Is the number of...

After some gap, we have managed to create the second in the series. This one deals with the reasons why to learn Excel. Might be good one for those who are sitting on fence and wondering if they should take the plunge! Next in the series would be "How to...". Stay tuned.

Hi Juniad, Please check if this works for the first part (English Name) with your data: =LEFT(A3,MIN(IF(CODE(MID(A3&REPT(" ",200),ROW($A$1:$A$200),1))=63,ROW($A$1:$A$200),201))-1) This is an array formula so needs to be committed by using CTRL+SHIFT+ENTER

Or you could keep an event code in ThisWorkbook module: Private Sub Workbook_AfterSave(ByVal Success As Boolean) Beep End Sub

What is the format of source column data? What @Marc L has suggested shall work if data is numeric. All you need to then is to invoke following cell formatting dialog and use 1000 separator:

Here's longer version of the formula: =TRIM(RIGHT(SUBSTITUTE(A2,",",REPT(" ",LEN(A2)),LEN(A2)-LEN(SUBSTITUTE(A2,",",""))-2),LEN(A2))) where term in red (2) is correlated to number of terms needed from right (3) i.e. n-1 If you need 4 terms then you just need to change it to 3, for 5 it will be...

Another one using MAX would be: =$A$2+MAX(C2-10,0)*$B$2

A good book to start with, if you have some basic idea of programming from schooling days, is P2P series book on VBA. https://www.amazon.com/Excel-2007-VBA-Programmers-Reference/dp/0470046430 I used this book when I began along with MrExcel's Excel and VBA Macros (for Excel 2003). Some of the...

@Peter Bartholomew I thought FAST was purely finance related standard / document having nothing to do with spreadsheets but it is not. Will give it a read.

@Peter Bartholomew Your first and last few lines have totally evaded me ;) Have no idea what FAST is and after reading word Finance somewhere I backed off. Just about the same for "DecWrite on a VAX". But about the named references and self referencing formulas, I will share my experience. In...

Obviously, it needs to account for non-intersecting phases of years which can be done by adding another MAX to the mix like below which is highlighted in red: =ROUND(MAX(DAYS360(MAX(EOMONTH($E5,(DAY($E5)>=15)-1),DATE(H$4,1,1)),MIN(DATE(H$4,12,31),EOMONTH($F5,(DAY($F5)>=15)-1))),0)/30,0)