Posts by sannuk7

  • fetching value from differnt sheets

    I am trying to fetch value (degree) from different sheets. On Sheet 1, the first column is id of companies, the second column is the sheet number.
    For the example given below, the formula should find the value of degree for company (id) 111 from sheet 10.


    id sheet degree
    111 10


    I am using the formula =VLOOKUP($A2,INDIRECT("'" & $B2 & "'!" & "$A:$C"),2,0) ( it has been suggested by this forum only). This formula used to work, but it is now not working and showing the error #N/A. Kindly suggest me, if there is some problem with the data i am using or formula needs to be modified. Thanks in advance.


    Regards,
    Santosh

  • Matching names based on parts and final name

    Re: Matching names based on parts and final name


    Dear KjBox,
    This code is working fine and the accuracy has improved. I am really thankful. And yes, wait was little long given the pressure I had from my guide J .
    It will help me in a great way. After the completion of my work, I should mention you in my acknowledgement.
    Thanks once again…


    Regards,
    Santosh

  • Matching names based on parts and final name

    Re: Matching names based on parts and final name


    Dear KjBox,
    Regarding the initial letter, you have rightly pointed outthe problem of considering just first letter. It is an important point and thanks for identifying it.
    It would be a good idea to removeduplicate entries. Kindly remove them.
    Regards
    [FONT="Calibri"]Santosh[/FONT]

  • Matching names based on parts and final name

    Re: Matching names based on parts and final name


    Hi,


    Thanks a lot. Now I am able to work on the data set without worrying about leading or tailing spaces.
    I have been asked to cross-check the result manually. Cross-checking the result would be easier if the result is arranged according to the last name. It would be of great help if you could suggest me some way out for arranging the result according to last name.


    Regards
    Santosh

  • Matching names based on parts and final name

    Hi,
    I am stuck at a problem of name matching. Two names will be considered same if they fulfil following criteria:


    • If they have equal number of parts. For example, “Santos Kumar Tiwari” and “S K Tiwari” have three parts
    • The last part should be equal. Here both name has last part as “Tiwari”
    • The initial letter of each part should be equal


    So, in this way: Santos Kumar Tiwari, Santos K Tiwari and S Kumar Tiwari are same ( all have same last names, same number of parts and each part start with same letter). To indicate they are similar, I need to assign them any numerical number, for example, 1 to all these three numbers .
    I have also attached the sample for your convenience. I am really stuck and cannot do my research further because of this.


    Regards
    Santosh

  • finding the number of companies a director is siting

    Hi,
    I have data of companies and their directors along with thebusiness groups these firms belong to. I suppose to find out “total interlocks”and “internal interlocks” for directors. Total interlocks for a director is: number of companies the director sitting minusone.
    In the given example,
    Group Company Director TotalInterlocks Internal
    Interlocks
    200 A XYZ 2 0
    200 B MNO 1 0
    201 C XYZ 2 1
    201 D XYZ 2 1
    201 E MNO 1 0

    Here director XYZ sits at 3 companies A, C, D and E. Hence hisinterlocks will three minus one i.e 2. In the case of director MNO, she issitting at two companies (B and E) and hence the total interlocks will be one (2-1).
    In the case of internal interlocks, connections among companiesbelonging to the same group should be considered.
    For example, in the case of director XYZ, he is sitting attwo companies ( C and D) of group 201. So for these companies (C and D) theinternal interlocks will be 1 which is two minus one. But for group 200 the XYZis sitting at only one company “A” . So for company “A” the internal interlocksis zero.
    In the case of MNO, she is sitting at only one company (B)for group 200 hence for company “B” her internal interlocks will be zero. Similarly,for company “E” the internal interlocks of MNO will be zero.
    For your consideration I have also attached a sample excel file.Kindly help me how I can do it.
    Regards
    Santosh

  • Fetching values from worksheet named as group no

    [FONT=&#10]Hi,[/FONT]
    [FONT=&#10]I have data worksheet which has details of companies like their “Id” and “GroupNo”. In this worksheet I need to fetch the values of “Degree” and “nDegree” for each company. Thevalues for Degree and nDegree are given in subsequent worksheets formed on the basisof Groups No. For example, there is a separate worksheet named 20101010100 and anotheras 20101010101 (20101010100 and 20101010101 are Group No). [/FONT]
    [FONT=&#10]For example to get the values of Degree and nDegree for company with id ABIRLANUVO, I need tolook the values in the worksheet which is named as its Group No (i.e. 20101010100). In the worksheet 20101010100 the values of Degree and nDegree for ABIRLANUVOare 3 and .33. I need to fill these values in worksheet named as Data[/FONT]
    [FONT=&#10]Kindly help me how I can fetch values from different worksheets. [/FONT]
    [FONT=&#10]Thanks and Regards[/FONT]
    [FONT=&#10]Santosh[/FONT]

  • Finding similar name from a list

    Re: Finding similar name from a list


    [FONT="Verdana"]Hi,


    This code is asking for the name and finding them from the list. Whereas,I need to find similar names. Companies have their own way of writing names ofdirectors. But the code should help me in identifying the similar names. Orcode should tell me, like, there are 95% chance (confidence) that these namesare similar. This will help me in refining the data for my study.
    Using this percentage, I can set a cutoff limit. Like, I can say names having90% of confidence of being similar are assumed similar for the study.[/FONT]
    [FONT="Verdana"]Hope I have explained what is needed. Kindly ask me ifthere are any doubts. [/FONT]
    [FONT="Verdana"]Regards[/FONT]
    [FONT="Verdana"]Santosh[/FONT]

  • Finding similar name from a list

    Hi,
    I have one peculiar issue with the data I am working on. Ihave a list of companies and their directors. Directors may join more than onecompanies. The issue is that companies don’t write names uniformly. For example,the name of Santosh Kumar Tiwari will be written by one company as S K Tiwari, by other as Santosh K Tiwari, or Santosh K Tiwari or Santosh Kumar Tiwari (Dr.).Some names may have even four or five parts (like V. Y. K Reddy) and different variations of writing them.
    Before I can further work on the data, I need to find whichnames are identical across different companies. Perhaps, it may not be possible to tell withfull surety which names are identical. But, It would be of great help if youcan tell me, with reasonable degree of surety, how I can findsimilar names.
    A same name may varies as given below:
    Company Directors
    M Santosh Kumar Tiwari
    N S. K. Tiwari
    O Santosh K Tiwari
    P S Kumar Tiwari
    R Santosh K Tiwari (DR.)
    S S K Tiwari (Mr.)
    T Mr. S K Tiwari

    Regards
    Santosh

  • Breaking data into multiple sheets

    Hi,
    I have data containing company names their business groupsand other attributes.
    Company Group Personal Code
    A X 111
    B X 121
    C Y 122
    I am trying to break this data into different sheets basedon Groups. I need all the information of separate Groups at separate sheet.s For example, all the data related to group Xat single sheet (say Sheet1) and data associated with group Y at differentsheet (say Sheet2) and so on.
    I am also attachinga sample data. In sample the Data worksheet is the given information, whereas Sheet2, Sheet3 and Sheet4 are required outputs.
    Regards
    Santosh

  • Fetching common (common company) data from different worksheets

    Re: Fetching common (common company) data from different worksheets


    Quote from Max1616;770032

    I'm not sure why those lines are highlighted red in my code, but its working fine in the example workbook you provided.


    Thanks a lot.
    This is giving the details of common companies across years and, thankfully, also flexible to have different ranges.
    This code is giving one output worksheet named as a summary. Here I am not able to distinguish which information belong to which year. It will be greatly useful if in a separate column corresponding years for the information are mentioned.


    Otherwise, the second solution can be separate outputs for each input worksheet. Suppose there are data for ten years in ten different worksheets. Then there can be ten output worksheets. But the output will be only for companies that are common across all ten worksheets.


    Thanks and regards,
    Santosh

  • Fetching common (common company) data from different worksheets

    Hi,
    I have information of directors for various companies in different worksheets. Each worksheet has data for one year like 2010 . Each sheets have different companies. But to run the data I need same companies across all years. So, i need the information for companies that are common for all years.
    For example, if in year 2010 there are four companies A, B, C and D; whereas in year 2011 there are five companies A, B, E, F and G. I need data for the companies that are present for all the years. In this example, only A and B are present all the years. So, I need to fetch the information for A and B only. The output can be in different sheets either in same workbook or different workbook.


    I am attaching the sample data from which I need to fetch information. The output will be similar to input but for only common companies across years ( in different sheet for each year)
    Kindly help me in fetching the data.


    Thanks