Thursday, November 8, 2018

How to swap 2 excel ranges | swap with multiple cells selected

I had created a macro to swap 2 cells values in MS Excel. By just clicking a button a macro could interchange the content of 2 excel cells.  There was a limitation on that macro. It could only work only if the selection is just 2 cells. It could interchange the value of just 2 cells but not the ranges. I used to get a lot of request from my viewers to extend this macro for more than 1 cell. Due to time constraint, I could not work on it.

Excel VBA macro to swap 2 values in cells



Now, I have written another macro which will work on the Excel ranges. This macro is completely different. My original macro was based on area selection. But the new macro is based on the ranges. It is very small and easy to understand, unlike another macro, which is comparatively bigger.

I will explain each of the lines in the macro.

Check out my youtube channel for more amazing excel and office automation videos

My youtube channel



1. Sub swap_2_ranges()

2. string_all_selected_area = (Selection.Address(ReferenceStyle:=xlA1, RowAbsolute:=False, ColumnAbsolute:=False))


The first line is the subroutine name. The second line gets the string of value of all the ranges selected. The output will be "I23,H25,G22" format. It says 3 ranges are selected. Each range is separated by a comma. I will use this string later to get the ranges.

3. array_selected_area = Split(string_all_selected_area, ",")

The above line splits the multiple ranges into one range and stores them in an array

4. If (UBound(array_selected_area) = 1) Then
5. area1 = array_selected_area(0)

6. area2 = array_selected_area(1)

Then, there is an if condition to check if the selection is suitable for our swap operation. It will not work as expected if we select more than 2 ranges. or less than 2 ranges. Our purpose is to swap or interchange the content of 2 different ranges. So the selection should be exactly 2 ranges.

After this check, the selected ranges are stored in 2 temporary variables.

7. selection_1 = Range(area1)
8. selection_2 = Range(area2)

The variables are converted to Excel ranges. This is very important, as we are not getting selection directly in the Excel range format. Instead, the selection is in text format. The string values should be converted into an Excel range object so that we can apply some Excel VBA functionalities.

9. If Range(area1).Count = Range(area2).Count Then

Then comes one more if condition to check if the selected ranges have the same number of cells. It is obvious that we should have the same number of cells on each selection so that we can interchange their values.

10. Sheets(1).Range(area2) = selection_1
11. Sheets(1).Range(area1) = selection_2

This is actual swapping code. This is achieved through the temporary variables.

12. End If
13. End If

14. End Sub

Finally ending the if conditions and the Subroutine.



Friday, October 26, 2018

Practical Text Manipulation using MID LEFT RIGHT FIND Functions in MS Excel

Text manipulation is one of the great features of MS Excel. It has a lot of built-in functions to extract desired values from a text. By using different combinations of these functions we can create a complex formula.

I have listed a few practical examples which are very useful.

Extracting a Door number from an address text. 


8314 W. Galvin Lane West Des Moines, IA 50265
68 4th Ave. Neptune, NJ 07753
8 Oklahoma St. Ladson, SC 29456

I will use the LEFT and FIND functions to extract the Door number of an address. 

=LEFT(B4,FIND(" ",B4))

Here I am using the delimiter space (" "). The formula first finds a space character which appears after the door number. Then extracts the value whatever if at the left side of the first space. 


Extracting a ZIP code from an address 

Generally, the ZIP code will at the end of the address. Using the RIGHT function, we can extract the ZIP code 

=RIGHT(B9,5)


Extracting a specific number from anywhere in the text 

Suppose, from the below text I need to extract the dollar amount Which may be present anywhere in the text. For this, I will use multiple functions. For better understanding and for clear visibility,  I have separated the functions. If you understand them clearly then you can create your own complex formula. 

I had $100 in my pocket
bought 2 pens for $1 each
Spent $50 for snacks. 
Now I have $48 left

First, identify the starting position of the Dollar symbol.

=FIND("$",B10)

Then identify the next            
Then identify the next space after the Dollar symbol and the digits using the below functions

=FIND(" ",B10,FIND("$",B10))

Finally, extract the context between the first index, tha is a dollar sign and the space character after the amount. For this, I am using the mid function

=MID(B10,C10,D10-C10)



Extracting the names from a text 

The below data has names mixed with some other text. The left part does not have any unwanted text. So we don't have to worry about the left part. But the right portion has other text and we want to remove that. The text marked in red are to be removed. 

John Smit (IT Dept)
Krishh J (Sales)
Priore Priore (Software)

Lucy S (Sales)

It is very clear that we need to extract the text up to the first "(" symbol. This is can be done using a FIND and MID functions. Note the -1 at the end of the formula. This is to exclude the "(" character. Because the FIND function will return the position of the "(". But we don't need it. 

=LEFT(B17,FIND("(",B17)-1)


Sunday, October 21, 2018

Practical examples on MS Excel date functions

MS Excel has an amazing set of Date and Time functions. By tweaking these functions, we can create a complex and useful formula. I am trying to list down a few practical examples particularly on the Date part, which will be very useful for a moderate to advanced Excel user. 


How to find the end of the month for a given date

Note that EOMONTH will return a serial number. You have to change the format to date format. 

=EOMONTH(TODAY(),0)

=EOMONTH(DATE(2018,10,10),0)


How to find the number of days remaining in a month

This is useful for interest calculation on first and last month on any type of loan repayment schedule. 

Use DAYS, EOMONTH and DATE functions.

=DAYS(EOMONTH(TODAY(),0),DATE(2018,10,10))


Number of days remaining in this month or current month

=DAYS(EOMONTH(TODAY(),0),TODAY())


Find the number of months between two dates

In other words, find the date difference in terms of months. DATEDIF is one of the very useful formulas in MS Excel. It will take 2 parameters, the first parameter is the date and the second parameter is the unit. A unit may be Y, M or D each constitutes Years Months and Days.

=DATEDIF(TODAY(), DATE(2019,1,10), "M")
=DATEDIF(TODAY(), DATE(2019,1,10), "Y")
=DATEDIF(TODAY(), DATE(2019,1,10), "D")

If you want to calculate the non-cumulative difference use these options.

"MD" - Ignores the Year and Month.
"YD"  - Ignores the Year
"YM" - Ignores the Year

How to construct a date using Day, Month and Year

DATE formula returns a date value. Takes 3 parameters - day, month and year. This formula is useful whenever you want to construct a date from different formats or from a text value. Before using this formula to extract date value from a text, you need to parse and get the individual DAY, MONTH and YEAR values. I will discuss more on this later

Syntax: 

=DATE(YEAR, MONTH, DAY)

=DATE(2018,10,10)

A practical scenario where the DATE function is useful. You have the date but you only want to change the Month. 

=DATE(YEAR(B2), 12, DAY(B2))


How to get the first and last day of a month

First day:

=DAY(EOMONTH(TODAY(),-1)+1) 

The above formula finds the end of the month of the previous month and adds one day to the date and then returns the day of the date value.

Last Day:

It is very simple to find the last day of a month using EOMONTH function

=DAY(EOMONTH(TODAY(),-1))

EOMONTH takes 2 parameters, Date and Month. The month value will be positive for future dates and negative for past dates, 0 for the current month.

0   - for the current month
-1  - previous month
3   - the 3rd month from now


How to convert month number to month name

Using TEXT function, the month name can be extracted from a date value. Note the different formatting options and the output. 

=TEXT(TODAY(),"mmmm")
=TEXT(TODAY(),"mm")
=TEXT(DATE(2018,1,1),"m")
=TEXT(DATE(2018,1,1),"mm")
=TEXT(DATE(2018,1,1),"mmm")
=TEXT(DATE(2018,1,1),"mmm")



How to get the numerical value of a month text

Suppose I have a month value in the string format, say 'October', and I want this in numerical equivalent value 10. There is no readily available excel formula to get the numerical value of this string. We can use the below trick to get the numerical month value

In excel there is a shorthand to enter a date. By entering 1oct and then by pressing enter key, we can get the date 1st October of the current year. Using this technique I will construct a formula to get the numerical value of text month value


=MONTH(1&LEFT(B24,3))
=MONTH(1&B25)
=MONTH(1&MID(B26,6,3))






How to get a Day, Month or a Year of a given date

It is very simple to extract a day, month or a year from the date value. Just use the built-in Excel functions as shown below. These functions alone may not be that useful, but whenever you need to write a complex formula, these functions will be very helpful

=MONTH(TODAY())
=YEAR(TODAY())
=DAY(TODAY())


Convert any text into date format

If you have a date in a different format or the date is represented as text then is difficult to use that for any calculation purpose. First, you need to convert them into proper date format. You have to use different methods or technique in different situations. I am giving a few examples here.



Sunday, September 30, 2018

VBA Code to find a record in the MS word mail merge

MS Word mail merge has an option to navigate through the records. The below VBA code will help you to type and search the record within the MS word document area, you do not have to go to the menu bar.

This code first resets the document to the first record. This is required as the mail merge navigation is only forward. That means the VBA code cannot traverse backward.

Reads a text from TextBox which is placed within the document. If the keyword entered and the current record is matched, then the Active document is activated with the current record.


With ActiveDocument.MailMerge
 .DataSource.ActiveRecord = wdFirstRecord
End With

Dim numRecord As Integer
Dim searchText As String
searchText = TextBox1.Text

Set dsMain = ActiveDocument.MailMerge.DataSource
If dsMain.FindRecord(FindText:=searchText, Field:="Name") = True Then
numRecord = dsMain.ActiveRecord
End If
End Sub


Watch the complete step by step tutorial


Download the Excel file and the word document from this link below

Download

Sunday, August 12, 2018

VBA Code to copy and paste the Content from excel to word

There are situations where you may have to copy and paste some content from excel to word. And save the newly created document into disk. The below VBA code or a macro will help you to do that. It is a very simple code, it is being executed from MS Excel. The MS Word object Library should included. This library has to functionality to interact with MS word object. 

To include MS word object library, go to Tools and then select References. Select the required object library as shown in the picture below.
 

To copy the VBA code follow the below steps

Open excel and make your table ready. This steps is as per your requirement. 

Press Ctrl + F11, this will open a Visual Basic editor. 

Click on insert and then module. 

Module1 will appear if do not have any  module. Paste the below code into this module 

Go back to excel and your macro will appear under macros list. Select and click on Run to execute the macro. 

Watch this video for more details explanation





Sub CreateWord()
    
    Dim w
    Dim d
    Dim filename As String
    'filename = "D:\\krishna\\youtube vidoes upload\\Marks Card " & Format(Date, "ddmmyy") & "_" & Second(Now())
    filename = "D:\\krishna\\youtube vidoes upload\\Marks Card " & Sheets("marks_sheet").Range("G7:G7") & "_" & Sheets("marks_sheet").Range("G8:G8")
    
    Sheets("marks_sheet").Range("a4:k25").Copy
    
    Set d = CreateObject("Word.Application")
    'If w Is Nothing Then
    Set w = d.Documents.Add
     
    d.Selection.Paste
    d.Visible = True
    'd.Selection.Paste ("new content")
    Application.CutCopyMode = False
    'ActiveDocument.Sections.Paste ("adsf")
    d.ActiveDocument.SaveAs filename
    
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    
    Application.CutCopyMode = False
    Set w = Nothing
    Set d = Nothing

End Sub

Saturday, July 14, 2018

Cleaning up private docker registry

The solution to cleaning the unused docker registry layers is not straightforward, there is nothing readily available from docker. As more and more docker layers are pushed and tagged, it may be possible that there are image layers which may not be required. These unnecessary layers will consume a lot of storage space. This page describes the way to delete unused layers without disturbing the registry.
I am going to explain how to clean up unused layers from docker registry using HTTP API V2
Docker HTTP API V2
The current version of docker provides an option to interact with the images in the remote private registry using HTTP API version 2.
Few useful Digest APIs
List all the repositories available in the private registry
CATALOG
$ curl reg-server:5000/v2/_catalog

Output
------
{"repositories":["alpine","1_ubuntu_16.04","centos","centos6-build-test","centos6-build-qa","centos6-build-build","centos6-jenkins-agent","jenkins","squid-deb-proxy","ubuntu","ubuntu-build","ubuntu-build-agent","ubuntu_16.04"]}
Getting a list of layers used, and other metadata of a repository (image)
GET DETAILS
$ curl -k -v --silent -H "Accept: application/vnd.docker.distribution.manifest.v2+json" -X GET http://reg-server:5000/v2/ubuntu-jenkins-agent/manifests/latestrl reg-server:5000/v2/_catalog
OUTPUT
Output
------
* Trying 10.0.93.36...
* Connected to reg-server (12.0.33.1) port 5000 (#0)
> GET /v2/ubuntu-jenkins-agent/manifests/latest HTTP/1.1
> Host: reg-server:5000
> User-Agent: curl/7.47.0
> Accept: application/vnd.docker.distribution.manifest.v2+json
> 
< HTTP/1.1 200 OK
< Content-Length: 5540
< Content-Type: application/vnd.docker.distribution.manifest.v2+json
< Docker-Content-Digest: sha256:d00e05048b4ef3d7e175d233a306f64175f1c716c755224984099c7e8cf0948
< Docker-Distribution-Api-Version: registry/2.0
< Etag: "sha256:d00e05048b4ef3d7e175d233a306f64175f1c716c7552245e14099c7e8cf0948"
< X-Content-Type-Options: nosniff
< Date: Fri, 06 Apr 2018 08:39:42 GMT
< 
{
"schemaVersion": 2,
"mediaType": "application/vnd.docker.distribution.manifest.v2+json",
"config": {
"mediaType": "application/vnd.docker.container.image.v1+json",
"size": 17980,
"digest": "sha256:6c4e6280d347be4762dd77a20845ec69c4c1da3424195523765dfaeeecbffa22"
},
"layers": [
{
"mediaType": "application/vnd.docker.image.rootfs.diff.tar.gzip",
"size": 67103213,
"digest": "sha256:5d890c53be21ea2d7c417960dfdb8edf87f623bfd016751261fac26943a0b188"
},
{
"mediaType": "application/vnd.docker.image.rootfs.diff.tar.gzip",
"size": 72628,
"digest": "sha256:f775b856e1997836995617cf691ea4ffb0b1ef967ac73db661666ba3a216d432"
},
{
"mediaType": "application/vnd.docker.image.rootfs.diff.tar.gzip",
"size": 363,
"digest": "sha256:552c4f407d99f5ff4e96e79430bae55c4ff1154824dab3945ef4bb0482c826d5"
},
{
"mediaType": "application/vnd.docker.image.rootfs.diff.tar.gzip",
"size": 676,
"digest": "sha256:fda304b96f8a99052eacb6ce515f26d8ff10fc78cbb6a9f09e996faaadabdaaa"
},
{
"mediaType": "application/vnd.docker.image.rootfs.diff.tar.gzip",
"size": 162,
"digest": "sha256:2b033adb904af1b663e78cf33f513fc2f98730b9c0dcab4a3ad4cd85eb825880"
},
{
"mediaType": "application/vnd.docker.image.rootfs.diff.tar.gzip",
"size": 69424905,
"digest": "sha256:3b93b65608a04bbd902b73d76e61f52a28b2a0b0faca95b8303dbe9a3397a688"
},
{
"mediaType": "application/vnd.docker.image.rootfs.diff.tar.gzip",
"size": 76466711,
"digest": "sha256:8ccd40bb9dd1bb2dfbc16a0ab661b817ce9b6af0617ae679cfabaa80a74414c5"
},
{
"mediaType": "application/vnd.docker.image.rootfs.diff.tar.gzip",
"size": 1066,
"digest": "sha256:0cf16c8ac4188b500ccbdf405c1288c298c4a50e40f0aafe485cd2d58f81ee8f"
},
{
"mediaType": "application/vnd.docker.image.rootfs.diff.tar.gzip",
"size": 4606,
"digest": "sha256:5670062ffd23e300b2fa32af6d9763211372ae919801c1ee98d1827aec24b57c"
},
{
"mediaType": "application/vnd.docker.image.rootfs.diff.tar.gzip",
"size": 1020,
"digest": "sha256:ce7a2ec46384b5e5cf94f6bdded1c8a46c1614b4e5216101e706a5a47dd1a8b5"
},
{
"mediaType": "application/vnd.docker.image.rootfs.diff.tar.gzip",
"size": 38402,
"digest": "sha256:73dd7e155ea00edcb02adc94f271aa07924faac30b4413fba823cbb7be51541c"
},
{
"mediaType": "application/vnd.docker.image.rootfs.diff.tar.gzip",
"size": 175,
"digest": "sha256:46094570601896ddccb7446c48fd64e7089fb3fb826b747e6db4c971da4f2d9a"
},
{
"mediaType": "application/vnd.docker.image.rootfs.diff.tar.gzip",
"size": 28176,
"digest": "sha256:2a4b48722514ef189776665fa00e98b24296117db359b491c83d995c53d4a3c1"
},
{
"mediaType": "application/vnd.docker.image.rootfs.diff.tar.gzip",
"size": 47021,
"digest": "sha256:55c8e005550e7be9ea7f990696b6b863a270f7a43a059b8e52af9e84a745403d"
},
{
"mediaType": "application/vnd.docker.image.rootfs.diff.tar.gzip",
"size": 415368,
"digest": "sha256:9cbcf4bfa6017c078eb8e2f20da1593e91cb0b5d271538c480c2a847fd0453ea"
},
{
"mediaType": "application/vnd.docker.image.rootfs.diff.tar.gzip",
"size": 98274,
"digest": "sha256:4e6638ed8398ca234d28dad09047ffba24fa96961497d8975ea20fe89738fe7e"
},
{
"mediaType": "application/vnd.docker.image.rootfs.diff.tar.gzip",
"size": 1133119,
"digest": "sha256:cffca1cea7728d2f65b29956c5f4ca46e3130e7c5eca2a8e74ceee7760f75227"
},
{
"mediaType": "application/vnd.docker.image.rootfs.diff.tar.gzip",
"size": 465660,
"digest": "sha256:586d6232925d2948926e1c0697657ddeb8daf63840f50d14e75a5d80a140f53f"
},
{
"mediaType": "application/vnd.docker.image.rootfs.diff.tar.gzip",
"size": 114104,
"digest": "sha256:329692f820b7a8f115763b238025b0021eeebfa83f9ba5ba94d09e4d30ab0443"
},
{
"mediaType": "application/vnd.docker.image.rootfs.diff.tar.gzip",
"size": 11356,
"digest": "sha256:8b948376919e9027aed5fb110f827e29b5d5b89faa249dd5e96d9f322f16671d"
},
{
"mediaType": "application/vnd.docker.image.rootfs.diff.tar.gzip",
"size": 233,
"digest": "sha256:280ed15840b101f31d36195774dbc64a0756b10445ac86a7d56c9323b254d3ab"
},
{
"mediaType": "application/vnd.docker.image.rootfs.diff.tar.gzip",
"size": 177,
"digest": "sha256:8b3430dea5aa62d32f1afa524062c8842b69f4e4c6c206c506096d61b86cf9aa"
},
{
"mediaType": "application/vnd.docker.image.rootfs.diff.tar.gzip",
"size": 5397987,
"digest": "sha256:940f81402bbb4f7cd2fc2e27ad60243ee352594d2d347d4cc7d061543a645579"
},
{
"mediaType": "application/vnd.docker.image.rootfs.diff.tar.gzip",
"size": 265,
"digest": "sha256:f22d1b75d0776f7b50c189b5f902411a16f1e558bb182eaa50d87970a783c3ed"
},
{
"mediaType": "application/vnd.docker.image.rootfs.diff.tar.gzip",
"size": 573,
"digest": "sha256:9d201349e9cab134e816a063bcbf18d1a23ff854a953492b9d9aa4165cf059ec"
}
]
* Connection #0 to host reg-server left intact
Deleting an image tag using the Docker-Content-Digest
Using this API, any given tag of an image can be deleted remotely. This will delete the layers associated with this tag. But one has to identify the tags that should be deleted. After deleting a tag remotely, one has to run the garbage-collector to take the depletion effect. Please note that the DELETE api requires a digest of an image tag. The digest can be obtained using a tag as bellow (for an image which is tagged).
The tag name in the below snippet is latest. It will work with any other named tag
DELETE
$ curl -k -v --silent -H "Accept: application/vnd.docker.distribution.manifest.v2+json" -X GET http://reg-server:5000/v2/ubuntu-jenkins-agent/manifests/latest 2>&1 | grep Docker-Content-Digest | awk '{print ($3)}'
To get the digest of an untagged image, go to the storage of the docker registry inside the registry container. If the registry container has configured with volume, a digest can be obtained from this location as well.
Suppose if 966fcf31b8a2 is a container id of a private registry and /var/lib/registry is the registry storage inside the container
DELETE
$ sudo docker exec -it 966fcf31b8a2 sh
$ cd /var/lib/registry/docker/registry/v2/repositories/alpine/_manifests/revisions/sha256

## below listed sha256 are the manifest whihc reference the image tag This is called a digest. These digests are used while deleting an image tag.
## there are 3 unamed tags here and the latest is always tagged as :latest
$ ls
xr-xr-x 2 root root 18 Apr 4 06:02 ef04ea6e2324b2e1f2b1a25a56defc92d24f6b364e14ddd081241426af82aa2d
xr-xr-x 2 root root 18 Apr 4 10:06 b978ab300d84b859181fbf8c579315e709d22bb47e25b448952e6dfdc79be1f5
xr-xr-x 2 root root 6 Apr 4 10:10 c62d369018e25c79c651cdebc10d380e585acbda33340476d9f85d34c4a37b0d

To delete a tag using a Digest (which you got in the previous step)
Note:
  1. stop the running container and start again with additional ENV variable -e REGISTRY_STORAGE_DELETE_ENABLED=true. The run command looks like
    docker run -d -p 5000:5000 -v /home/sanjeeva/registry_vol:/var/lib/registry -e REGISTRY_STORAGE_DELETE_ENABLED=true --name registry registry:2
  2. By editing the config file inside the registry container.
    vi /etc/docker/registry/config.yml
    Under 
    storage: add these 2 lines
    delete:
    enabled: true
    Run the below command
    $ sudo docker restart >
DELETE
$ curl -k -v --silent -X DELETE http://localhost:5000/v2/alpine/manifests/sha256:934c25b1f1c266e31ee3693890b08f67cf0b05c162561edc779150c0ece7d872
DELETE OUTPUT
* Trying 127.0.0.1...
* Connected to localhost (127.0.0.1) port 5000 (#0)
> DELETE /v2/alpine/manifests/sha256:934c25b1f1c266e31ee3693890b08f67cf0b05c162561edc779150c0ece7d872 HTTP/1.1
> Host: localhost:5000
> User-Agent: curl/7.47.0
> Accept: */*
> 
< HTTP/1.1 202 Accepted
< Docker-Distribution-Api-Version: registry/2.0
< X-Content-Type-Options: nosniff
< Date: Fri, 30 Mar 2018 10:58:18 GMT
< Content-Length: 0
< Content-Type: text/plain; charset=utf-8
< 
* Connection #0 to host localhost left intact
Garbage-collection
After deleting a tag of an image using API, run the garbage collection inside the container.
DELETE
## dry run with -d
$ /bin/registry garbage-collect -d /etc/docker/registry/config.yml
## actual run without -d
$ /bin/registry garbage-collect /etc/docker/registry/config.yml
Summary of steps to clean up a registry
  1. Enabling a layer deletion in docker registry (there are 2 ways to do this 1.b is the preferred method)
    1. by stopping and deleting the registry container
      1. Stop and delete the registry container
    2.  
      1. This may delete all the images in the registry unless it was started with a volume with -v option
      2. Start the registry container with
      3. REGISTRY_STORAGE_DELETE_ENABLED=true as
      4. docker run -d -p 5000:5000 -v /home/sanjeeva/registry_vol:/var/lib/registry -e REGISTRY_STORAGE_DELETE_ENABLED=true --name registry registry:2
    3. by restarting the container after editing the config file
      1. $ vi /etc/docker/registry/config.yml
      2. Under storage: add these 2 lines
        delete:
        enabled: true
      3. Run the below command
      4. $ sudo docker restart
  2. inside the registry container, for each repository
    1. List all the manifests except the latest tag.
  3. Run the API V2 command to delete the non-latest manifests
    1. $ curl -k -v --silent -X DELETE http://reg-server:5000/v2/alpine/manifests/sha256:249c714c688541c83ca2c9b2a8c30dd77b5c45c836e6c69632815ee3614ccbd2
  4. Run the garbage-collector inside the registry container
    1. It will delete the image layers which are no more referenced by any tag(manifest) except the latest tag
    2. We may see a few folders left undeleted.


How to swap 2 excel ranges | swap with multiple cells selected

I had created a macro to swap 2 cells values in MS Excel. By just clicking a button a macro could interchange the content of 2 excel cells. ...