Excel: Merge cells in rows having same values

All most people like me and you often use Excel to process Data. Some times you get a Data with cells in rows having same values, and you want to merge them into 1 cell by each column. For example, you have a Data like the screenshot below:

And you want to achieve new Data as the following:

In which, SUM column will have new value = SUM of rows on AllOrHalf column which are belong to the merged cell.

In this article, I'll show you a solution using VBA to make this work. On the sheet, press Alt+F11 to open Visual Basic Editor (VBE). Right click on your workbook name in the Project-VBAProject pane (at the top left corner of the editor window) and select Insert >> Module from the context menu.

Copy below code to the window:

Option Explicit

Private Sub MergeCells()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    Dim rngMerge As Range, cell As Range
    Dim nrow As Integer: nrow = 0
    Dim continue As Boolean: continue = True
    Dim totalLeave As Double: totalLeave = 0
    Set rngMerge = Range("A2:A9") 'range to check
    

    For Each cell In rngMerge
        Do
            totalLeave = totalLeave + cell.Offset(nrow, 5).Value
            If cell.Offset(nrow, 0).Value = cell.Offset(nrow + 1, 0).Value _
              And cell.Offset(nrow, 1).Value = cell.Offset(nrow + 1, 1).Value _
              And cell.Offset(nrow, 2).Value = cell.Offset(nrow + 1, 2).Value _
              And cell.Offset(nrow, 3).Value = cell.Offset(nrow + 1, 3).Value _
              And IsEmpty(cell) = False Then
                nrow = nrow + 1 'check next row
                continue = True
            Else
                continue = False
            End If
        Loop Until continue = False
        If nrow > 0 Then
            Range(cell.Offset(0, 3), cell.Offset(nrow, 3)).Merge
            Range(cell.Offset(0, 2), cell.Offset(nrow, 2)).Merge
            Range(cell.Offset(0, 1), cell.Offset(nrow, 1)).Merge
            Range(cell, cell.Offset(nrow, 0)).Merge
            nrow = 0
        End If
        cell.Offset(0, 3).Value = totalLeave 'assign value to SUM column
        totalLeave = 0
    Next
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

Then press F5 to run the code. After that, you can format merged cells and you will get the result.

That's it. Any comment is welcome.

How to use MS SQL to generate random unique number for scratchcard

Do you need a series random unique numbers for scratchcards, or bundle codes for vouchers?

There are many ways to do this work, in this articles I would like to share a way to generate these numbers in SQL server.

SQL server has a function calling NEWID(), it creates a unique GUID (uniqueidentifier) value. Depending on your purpose, you can use this function in several ways to generate unique numbers.

Generate a GUID:

SELECT NEWID()

It will generate a new random uniqueidentifier e.g. 4FEC77B5-83AC-485B-9B0A-9A3E58BE4A06

Generate a random unique number:

SELECT ABS(CAST(CAST(NEWID() AS VARBINARY(5)) AS Bigint))

It will generate a random unique number e.g. 685690842599

Generate a random unique number with fixed digit:

SELECT REPLACE(STR(CAST(CAST(NEWID() AS binary(6)) AS bigint),15),0,0)

You will have a random unique number with fixed digit e.g. 188125385042097. Then you can use it for your scratchcard or voucher etc.

That's all. Any comment is welcome!

Windows Server: prevent anonymous login and ban IP of attacker

On Windows Server 2008 R2 / Windows Server 2012 you can disable anonymous login by using Local Group Policy Editor. To open the Local Group Policy Editor: click Start button, key gpedit.msc in the Start Search box, and then press ENTER.

Under Computer Configuration\Windows Settings\SecuritySettings\Local Policies\SecurityOptions, there are 6 policies to control what information can be accessed anonymously:
1. Network access: Allow anonymous SID/Name translation
2. Network access: Do not allow anonymous enumeration of SAM accounts
3. Network access: Do not allow anonymous enumeration of SAM accounts and shares
4. Network access: Let Everyone permissions apply to anonymous users
5. Network access: Named Pipes that can be accessed anonymously
6. Network access: Shares that can be accessed anonymously

Just disable policy 1 and 4, enable policy 2 and 3, and clear empty for policy 5 and 6.

Disabling anonymous login is not enough for preventing attempts to attack your Windows Server, you should buying & install an application like Symantec Endpoint Protection to protect your server with advance functions.

However if your server just run SQL server and you use Remote Desktop to remote the server, you can do a security layer by your self. The first thing is you should change the default service port of Remote Desktop and SQL server. The second thing is you should use IPBan written by Jeffrey N. Johnson, it is a free tool tracking any IP that invokes services on your server and when number of fail events reaches to a predefined threshold, it will block the IP in the Windows Advanced Firewall by using a Blocking rule there.

If you like coding, you can download the code of IPBan from here. If not, you can download its binary from here (required .NET Framework 4). Below are main configurations for making it up & run:
1. Config Remote Desktop Session Host Configuration to log IP address in event log. To run it: click Start button, key Remote Desktop Session Host Configuration in the Start Search box, and then press ENTER. Double click the connection RDP-Tcp to change encryption settings to native RDP encryption. See the picture below for howto. After finishing, please reboot your server.

2. Copy IPBan binary to a folder, e.g. D:\IPBan. Then open and modify IPBan.exe.config file. Below are some rules that you should learn:
       2.1 Group rules, for example:
<Group>
<Keywords>0x90000000000000</Keywords>
...
<XPath>//Provider[@Name='MSSQLSERVER']</XPath>
...
</Group>
This group is used for tracking Application events for logging on to MS SQL server which having keyword 0x90000000000000, see the following captured image in Application events for more detail:

In this case, Provider is MSSQL$SGSQL2012, so we'll change MSSQLSERVER to MSSQL$SGSQL2012.

     2.2 Rule for attempts before banning
<add key="FailedLoginAttemptsBeforeBan" value="5" />

     2.2 Ban time rule (DD:HH:MM:SS)
<add key="BanTime" value="00:00:30:00" />

     2.2 Log file rotation rule
<target name="logfile" xsi:type="File" fileName="${basedir}\logfile.txt" archiveNumbering="Sequence" archiveEvery="Day" maxArchiveFiles="28" />

3. Create IPBan service and start it
#sc create IPBan type= own start= auto binPath= D:\IPBan\ipban.exe DisplayName= IPBan
#net start IPBan

That's all. Now you can monitor your server under your way.
Any comment is welcome!

Plugin to make Wordpress work with SQL server

If your website is using Windows server with IIS + SQL server and you want to have a blog using Wordpress, you may not want to install MySQL server on your existing server because it can make your server heavier and slower. Luckily there is a solution to make Wordpress work with your existing SQL server. Below are articles that can help you in details:

Installing WordPress on Windows using SQL Server 2008 R2 Part 1 - This section will cover installing and configuring PHP and IIS 7.5.
Installing WordPress on Windows using SQL Server 2008 R2 Part 2 - This section will cover configuring your SQL Server and installing and configuring WordPress.

The heart of this solution is wp-db-abstraction plugin, The following fixes can make it work more smoothly:
1. Open file wp-includes\wp-db.php, goto line 1294:
mysql_free_result( $this->result );
and change it to:
sqlsrv_free_stmt( $this->result );
This will help to flush out the resources after querying results. In this case we use sqlsrv function instead of mysql function

2. Open file wp-content\mu-plugins\wp-db-abstraction\translations\sqlsrv\translations.php, change:
$pattern = '/LIMIT\s*(\d+)((\s*,?\s*)(\d+)*)(;{0,1})$/is';
to
$pattern = '/LIMIT\s*(\d+)((\s*,?\s*)(\d+)*);{0,1}$/is';

In this file too, move to the function translate_specific($query), change it to the following code:

function translate_specific($query)
    {
        $tmp = strtoupper ("SELECT COUNT(NULLIF(`meta_value` LIKE '%\"administrator\"%', FALSE)), "
                        . "COUNT(NULLIF(`meta_value` LIKE '%\"editor\"%', FALSE)), "
                        . "COUNT(NULLIF(`meta_value` LIKE '%\"author\"%', FALSE)), "
                        . "COUNT(NULLIF(`meta_value` LIKE '%\"contributor\"%', FALSE)), "
                        . "COUNT(NULLIF(`meta_value` LIKE '%\"subscriber\"%', FALSE)), "
                        . "COUNT(*) FROM " . $this->prefix . "usermeta WHERE meta_key = '" . $this->prefix . "capabilities'");
        if (strtoupper($this->preg_original) == $tmp) { 
            $query = "SELECT 
    (SELECT COUNT(*) FROM " . $this->prefix . "usermeta WHERE meta_key = '" . $this->prefix . "capabilities' AND meta_value LIKE '%administrator%') as ca, 
    (SELECT COUNT(*) FROM " . $this->prefix . "usermeta WHERE meta_key = '" . $this->prefix . "capabilities' AND meta_value LIKE '%editor%') as cb, 
    (SELECT COUNT(*) FROM " . $this->prefix . "usermeta WHERE meta_key = '" . $this->prefix . "capabilities' AND meta_value LIKE '%author%') as cc, 
    (SELECT COUNT(*) FROM " . $this->prefix . "usermeta WHERE meta_key = '" . $this->prefix . "capabilities' AND meta_value LIKE '%contributor%') as cd, 
    (SELECT COUNT(*) FROM " . $this->prefix . "usermeta WHERE meta_key = '" . $this->prefix . "capabilities' AND meta_value LIKE '%subscriber%') as ce, 
    COUNT(*) as c FROM " . $this->prefix . "usermeta WHERE meta_key = '" . $this->prefix . "capabilities'";
            $this->preg_data = array();
        }

        if (stristr($query, "SELECT DISTINCT TOP 50 (" . $this->prefix . "users.ID) FROM " . $this->prefix . "users") !== FALSE) {
            $query = str_ireplace(
                "SELECT DISTINCT TOP 50 (" . $this->prefix . "users.ID) FROM", 
                "SELECT DISTINCT TOP 50 (" . $this->prefix . "users.ID), user_login FROM", $query);
        }
        
        if (stristr($query, 'INNER JOIN ' . $this->prefix . 'terms USING (term_id)') !== FALSE) {
            $query = str_ireplace(
                'USING (term_id)', 
                'ON ' . $this->prefix . 'terms.term_id = ' . $this->prefix . 'term_taxonomy.term_id', $query);
        }
        
        return $query;
    }

I believe that you can have a Wordpress blog work with your existing SQL server soon -:) Any comment is welcome.

Facebook apps: new way is easier to create apps

Facebook has announced recently its process for developers integrating apps into the social network. The new process is easier than old process.

You can read here to know this new way.

Quote:

In the original app-creation flow, you had to find and download our latest SDKs, create a Facebook App ID, enable the platform you were building for, and then copy and paste certain information between our docs and your development environment — all while trying to read our documentation. We are always trying to improve the experience for developers, and having a quick start to integrating with Facebook is one example.

In the new app registration flow, we've drastically simplified the experience and we added interactive guides to lead you through the process of integrating an app with Facebook. Now, we will only display the steps relevant to your app. Additionally, SDK downloads are included inline, along with code you can copy and paste into your app.

Developers working on Windows Phone and Facebook Page Tabs apps can still access the older app creation tools by using the “advanced setup” link.

Thanks for your reading, welcome any comment.

Best regards.