Quick Bytes IT Stuff

Finding duplicate values in Excel can easily be done using Excel Formula. Manually find duplicate values in a single column with 100 or more rows is tedious and prone to errors. Thankfully, Excel formula can do this task with precision. Assuming, the data or values has no trailing white spaces. Trailing white spaces, will cause problem because even though its whitespace still treated as a character by Excel. Data must be sanitized before comparing. Excel fomula to find Unique values or duplicates can be done using the formula below. =IF(COUNTIF($A$1:$A$13,A1)>1, "Duplicate","Unique") $A$1:$A$13 ==> Range to check duplicate or unique values, adjust the range as required A1 ==> Check duplicate values at this row or start comparing at A1 >1 ==> If there is a duplicate or a similar value more than 1, if the value is 2. Then the 3rd value that is similar to the other two will be identified as duplicate "Duplicate","Unique...

Converting column values to a single row separated by commas, is needed by some applications to iterate thru the values. Some automation tools, require such format. Example image below, shows A1 to A35 a list of Server Name. 35 rows is just an example or a demonstration it can easily become hundred of rows or more depending on the actual servers or data in the environment. To convert the rows to a single row separated by a comma, can easily be done using Excel. Excel 2019 and above is needed for the TEXTJOIN formula. Example image below, shows how to convert the Column values to a single row with comma as a delimiter. Formula is: =TEXTJOIN(",", TRUE, A1:A35) "," means separate the data with a comma "TRUE" means don't include empty spaces A1:A35 the range of values that will be converted to a single row with comma as a delimiter Image below shows the output after the formula process the data. Once you have the data as desired...

Excel is an spreadsheet tool. However, it can also be useful to as a word or a text editor. Such as creating a simple file with links, URLs or website name and saving the file as PDF. One way to create PDF is by printing the file and selecting a PDF printer and of course the file will be in PDF format. In some cases, after printing to PDF the URLs or website links may not work. Or the text hyperlink is not clickable. Other way to save the file as PDF in Word or Excel is to used the "save as" option and save the file as PDF. To save the Excel file as PDF and to preserve the Hyperlinks or make the link clickable in PDF, follow the steps below. - On the Excel file, click "File" - This will open a few options and "save as" is one of them - After clicking "save as", it will show the option where to save the document - You can click "This PC" and select "Documents" - The file will be saved on Documents folder - Afte...

In Excel, ctrl + end will go to the last cell with a value. But this will be tricky if you have deleted the last row with a value or has deleted its contents on the cell. Since it will be recoded as the last cell that has a value or the user has inputted or type on that specific cell.   And Excel will still remember it as the last type cell with value. Pressing ctrl + end will still go there, thus provide the wrong result. However, to prevent this from happening you need to close the workbook and open it again. Then pressing ctrl + end will show the last row with value. But while the worksheet is active any last typed cell even though its content or deleted will be considered as the last row. Getting the last row with data even without closing the workbook can be done via this simple VBA code below. One caveat though, this will only search for the defined row within the VBA code and the number of rows to be checked has to be defined on the code also. So, type a numbe...

How to type equal sign "=" in Excel without evaluating? Typing equal sign in Excel will trigger Excel to evaluate the contents or the formula that is being typed. But how to type equal sign plus the contents without evaluating the formula? Or how to display a formula in a cell using Excel? In VBA, Excel Macro, VBscript and VB.Net; to put comments or remarks on the code is to use single quote. A single quote tells the engine that whatever that follows from the quote is a string and not a command and should not be evaluated. In this way, you can type anything after the single quote “ ‘ “. So, this goes the same way in the Excel interface. If you want just to show the formula for remarks or whatever purposes but don’t want Excel to evaluate the contents, then just type a single quote before the equal sign or the formula. Example: ‘=1+1 Excel will just display =1+1 It will be treated as a literal string. Of course, without the single quote ...

Copying and paste in Excel is quite simple and straight forward. It should be the same thing with VBA, there are few ways to do this via VBA. The simple is to record a Macro then check the code, then alter the data to suits the operation that needs to be done. Basically, when copying and paste; it’s like pointing the data to another cell or cells so both locations will have the same value or data. The code example below, does the above strategy. Get the value of the data that is to will be copied and point the data to a location where the data will be pasted. Here's the Excel Vba code to copy and paste: Sub CopyandPaste() Set wb2 = ThisWorkbook 'Copy or set the value to be copied to a variable string_row_value = wb2.Sheets("Sheet1").Cells(1, 1).Value 'Paste or set the value to Sheet2 wb2.Sheets("Sheet2").Cells(1, 2).Value = string_row_value 'If needs to be copied to the same worksheet or in sheet1 itself ...

Raw data that comes in pattern is easy to handle for further processing. For example, if you have a data that has final value in every 7 th row, so basically 1 to 6 is not needed and 7 th row is needed, likewise 8 th row to 13 th row is not needed and 14 th row is needed and the pattern goes on that every 7 th row is the final data from the previous 6 rows. If the pattern continues, it will be like: 1-6, 7 final value, 7 -13, 14 final value, 15 to 20, 21 final value, 22 to 27, 28 final value and the pattern goes on up to hundreds or thousands. It will be time consuming to get every 7th row manually. For graphical representation it will be like: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 From the image above every 7 th row (green color) is needed and the rest is discarded. Other issue comes in if the data has to be processed further in another sheet,...

There’s quite a lot of ways to do this in Excel, using Macro or Excel formula will be able to get the desired output. Macro I think is good if the number of rows is not so large, but if the number of rows is quite large then a buffer overflow error might occur. Formula can take quite large of data and able to deliver the result as expected. ISNUMBER is a function that can check whether value is a number or not, the function itself tells what the formula will be doing. Adding a search function to ISNUMBER then it can be used to search for a string with in a cell or the specified range in the formula. Example below shows how ISNUMBER can be used to search for a specified string whether it exists or not. The position of the string doesn’t matter as long as the string exist within the cell, the formula will return TRUE, otherwise it will return FALSE if the string does not exist. Here’s the example: =ISNUMBER(SEARCH("search string", A5)) The “...

Working in a large data set in Excel but filter option cannot be seen or found even clicking the sort and filter option multiple times, the drop-down filtering options is nowhere to be found. Well, just get back to the very basic steps. If it can’t be seen then it might be hidden, right? So, when working in a large data set but some other rows is not needed and need to be hidden. Just leave the first row visible and do not hide it, then hide other rows. The drop down filter option is in row 1, so if it is hidden then of course the drop down filter will also go with it. To unhide row 1 or cell a1, press Ctrl + G. The go to options   window will pop-up, type: A1 to go to cell A1. (see pic below) Then, press ctrl+shift+9 to unhide the row. Or click on the format menu options and select unhide. (see pic below for excel 2016). Then the filter option will be displayed. The keyboard shortcut I think will work on other versions of Excel. If you ...

How to insert a header or footer in Excel 2016? How to insert a custom header or footer in Excel 2016? To insert a custom header or footer with a picture or other sort of customization, can only be done of course using the custom header or footer option. The easiest way to find header and footer is clicking on Insert on the main menu and selecting or clicking header and footer options on the ribbon tab as shown in the image below. If the author of the file has inserted a custom footer or header, sometimes the footer it’s either the header or footer will not be shown. If you need to delete, remove, change or alter the custom footer or custom header, it can be done by the steps below. Steps is shown in the image below. Click on “File”, click “Print”, click “Page setup” a small text found on the bottom right side of the print window. After clicking “Page Setup”, a window will open, then click on “Header/Footer” option. Cheers, till next time! Hope i...

Opening files in Outlook shows an error message that there is not enough free memory to run the program. But the computer has enough RAM installed and no other application is running on the computer except Outlook. Office programs by default don't trust or block files originating from the Internet. This is of course a good security settings but if this setting is getting to your nerves because even if the document that is coming from your own domain you are not able to open. Or the file is originating from your own personal email account other than your company email but when it goes to your Outlook you cannot open it. This is the scenario where good and harsh words will start crossing your mind. You will need to ask what the heck is going on? The Microsoft link below provide some solution on how this thing can be resolved. The article title of the link below is: Error message in Outlook: "There is not enough free memory to run this program. Quit one...