Tuesday, 22 October 2013

String Extract options

Hi,

 

Here are various method to work on some data within the string

 

Data

A5 = Custom Audience Blend 110.00 132,439.34

 

 

1)      Need “ data after first Space”

A6                         =             =TRIM(MID(A5,FIND(" ",A5),100))

Output                 =             Audience Blend 110.00 132,439.34

 

2)      Put a Special character for the last set of data easy to split

 

A6                         =             SUBSTITUTE(A5," ","^",(LEN(A5)-(LEN(SUBSTITUTE(A5," ","")))))

Output                 =             Custom Audience Blend 110.00^132,439.34

 

Varaible               Len(a5)                                               =             39

                              (LEN(SUBSTITUTE(A5," ","")))                      =             35          

                                             (SUBSTITUTE(A5," ",""))                 =               CustomAudienceBlend110.00132,439.34

 

 

 

               Repeat the same process on the resultant data to extract number of last columns

Read from Right

Q: Is there a function to read values starting from the right of a cell and pulling characters until I reach a space?

Two examples:
SGA3220R_IAOAKTREE (BTL-3) 1177 64089 - would return "64089"
SGA3321R_IAREDROAD (BTL-3) 1177 975 - would return "975"

I know we could do a Text to Columns with a space delimiter but if there are multiple spaces it would distort the data.

 

 

Try using this formula

=RIGHT(A2,(LEN(A2)-FIND(" ",A2)))

Saturday, 19 October 2013

Use Search command in Vlookup

Vlookup can also pick the like fields – here is the command

 

 

Search

Name

ID

DOB

Team

eo

Vikram Deo

0012327

12/12/2012

1

 

Search

Name

eo

=VLOOKUP("*"&H2&"*",'Employee List'!$A$2:$D$11,1,0)

 

Wednesday, 9 October 2013

Is Italic Function

Public Function Isitalic(c As Range) As Boolean

 

On Error GoTo Handler

Isitalic = c.Font.Italic

Exit Function

 

Handler:

If Err.Number = 94 Then Isitalic = True

 

 

End Function

Saturday, 5 October 2013

Related Party Transactions :: Clause 188 of New Company Bill

In old company law there were few section which were regulating the transaction with related parties whereas in new bill clause 188 covers all the provision of old act and also widens the scope. Under new bill, Section 2 (76) describes the meaning of related parties with reference to company.

 

Who are all comes under this section let us see:

 

1.      Director or his relative.

2.      A key managerial personal or his relative.

3.      A firm in which directors, manager or his relative is a partner.

4.      A private company in which director or manger is member or director.

5.      A public company in which director or manger is director or holds along with his relatives, more than 2% of paid up share capital.

6.      Anybody corporate whose board of director, managing directors or managers is accustomed to act in accordance with the advice, direction or instruction of a director or manager;

7.      Any person on whose advice, direction or instruction a directors or manager is accustomed to act.

Provided that nothing in the sub clause (VI) and (VII) shall apply to the advice, direction or instruction given in a professional capacity.

8.      Any company which is –

a. A holding, subsidiary or an associate company of such company ; or

b. A subsidiary of a holding company to which it is also a subsidiary;

 

9.      Such other person as may be prescribed;

 

After coming across the related party definition let us analyze the “related party transaction under new bill –

 

Clause 188 of the new bill 2013, deals with the related part transaction. In the old act only two clauses were explaining the types of transaction to which board sanction is required to enter in to the  contract. i.e.,

 

1. For the sale, purchase or supply of any goods, material or services; or

2. For underwriting of shares in or debentures of the company.

 

The old act was silent about the contract involving immovable properties. However in the new bill, considering the above problem, it has introduced a 5 more clause to the section 188 to specify types of contract which requires board consent.    

 

They are as follows:-

 

1.      Selling or otherwise disposing of, or buying, property of any kind;

2.      Leasing of property of any kind;

3.      Availing or rendering of any services;

4.      Appointment of any agent for purchase or sale of goods, materials, services or property;

5.      Such related party's appointment to any office or place of profit in the company, its subsidiary company or associate company; and

 

However sub clause (f) was already covered under section 314 of the Companies act, 1956.

 

Previously on Companies Act, 1956, Section 297 Provides the Provision as below-

 

Except with the consent of the board of directors of a company, a director of a company or his relative, a firm in which such a director or relative is a partner, any other partner in such a firm or private company of which director or member or director, shall not enter into any contracts.

 

However in the new Bill section 188 wipes off the sub clause (1) of section 297 of old companies act and has inserted as follows – “(1) Except with the consent of the Board of Directors given by a resolution at a meeting of the Board and subject to such conditions as may be prescribed, no company shall enter into any contract or arrangement with a ‘related party’ “. And hence it has brought many transactions in to the boundary of this clause.

 

Let us analyze with a small situation wherein how the old Law and new bill will implicate its provision –

 

“XYZ ltd enters into a contract of leasing of a property with ABC ltd in which XYS ltd.’s director Mr. H holds more than 2% of the Paid up capital of ABC ltd.”

 

According to Companies act, 1956-

 

Section 297 (1) (a) /(b) doesn’t provide any provision that brings the Leasing contract also requires the consent of the board and also there is no directors interest  in the later company  and hence the Board’s consent is not required for the above said contract.

 

However according to New Bill, 2012

 

Sub Clause (c) to subsection (1) of section 188 includes the contract of leasing also require boards consent and section 2 (76)(v) brings in its definition of related party that  if a public company in which a director or manager is a director or holds along with his relatives, more than two per cent of its paid-up share capital.

 

Thus Boards consent is required for the above mentioned transaction.

 

Therefore we can conclude that the new Company Bill,2012, has done much effort to overcome  form the many loopholes of the previous act and to broaden the scope of the regulation over the companies by inserting many new provisions so as to cover all the drawback. However MCA has notified only 98 section of the Company bill. Clause 188 has not yet notified by the MCA but on phases it will be notified by the MCA.