How to custom format in excel

Source: https://www.ablebits.com/office-addins-blog/custom-excel-number-format/#decimal-places

Converting a number to MILLIONS in currency without dividing the number by 1000000.

$* #,##0.00,,"M";;"";

Excel validating whether required columns (marked with *) are filled or not

 Here is the formula

=LET(dataOriginal,A1:C100,

data,FILTER(dataOriginal,BYROW(dataOriginal,LAMBDA(row,SUM(--(row<>""))>0))),

firstRow,MAP(TAKE(data,1),LAMBDA(x,ISNUMBER(SEARCH("~*",x)))),

BYROW(MAP(DROP(data,1),LAMBDA(x,NOT(ISBLANK(x)))),LAMBDA(array, IF(SUMPRODUCT(--(array)*--(firstRow))=SUMPRODUCT(--(firstRow)),"OK","REQUIRED COL VALUE MISSING")))

)

#HowTo Autofit columns in excel

 Problem: Adjusting the width for every column using ALTH+H+O+I is painful 

Solution: Use AutoFit

#How To Steps:

1. Right-click the sheet and select View Code

1.  

2. Add the following Code under the Worksheet

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Cells.EntireColumn.AutoFit

End Sub

Excel: Adding Table as a Data validation using INDIRECT function

1. Create a table and name it. Say TableDiv and the items should be of the column name Division
2. In the data validation source, select the INDIRECT function and add the following validation =indirect("TableDiv[Division]")

How to create scannable barcodes in Excel

 A powerful tool for businesses is a barcode that can be scanned. Assume you wish to simplify the inventory process and have a list of products with their product codes. You are able to accomplish this in just two easy steps by creating scannable barcodes.

Format the special product codes first. This can be achieved by formatting the codes as text values and placing an asterisk (*) before and after each code cell number. (To save time, use the ampersand sign.)

The second and final step is to switch the font to Libre Barcode 128 after adding an asterisk before and after each product code. If you don't already have it installed, get it for nothing and restart Excel to use it.

Pro-tip: Boost the font size to make the barcodes easier to scan.

Split comma separated string and pass to IN clause of SQL SELECT statement

This was a query from reporting team. Question looked too simple , but the solution was not obvious. 

Problem

We get a colon separated string (1:3)  that we would need to pass to the IN clause of a select statement to get the required rows. 
SQL>select * from test;ID Text1   A2   B3   CSQL>select * from test where ID in :P_INPUT  #P_INPUT=1:3No rows selected
Here we expected 2 rows instead we got no rows. 

Solution

• Split the input string based on delimiter

• Get the individual strings as rows

For the above, we use a combination of  regexp_substr function and connect by features in oracle. 

SQL>select * from test where ID in (     select regexp_substr(:P_INPUT,'[^:]+', 1, level) from dual  connect by regexp_substr(:P_INPUT, '[^:]+', 1, level) is not null;)ID Text1   A3   C

Bigdata projects in Apache

Cloudera combines