The loss at Madrid against Fernando Verdasco left all his fans disappointed, he on the other hand, trained in endurance and humility, learnt from that experience. He, just as all of us and his competitors, knows that he is the best player to play on the red surface. After being jolted time and again by the unstoppable Serbian tennis machine last year and in this year’s Australian Open, he bounced back when they battled on his court. Winning Monte Carlo Masters record eight consecutive times, defeating his rival for the first time in eight encounters (all in Finals) in the process. He topped up with a majestic win at Barcelona. But if any complacency crept in his mind with these victories, it was rightfully washed out by the defeat against his fellow countryman on the new blue clay of Madrid, losing his No.2 World Ranking to the Former World No.1 Tennis great Roger Federer. Heading to Roma, the King of Clay had his hands full as he faced the same man who was pretty much undefeated last year. But he was oozing with confidence and despite of some lousy tennis from his side he won in straight sets, winning the title for sixth time – The tenacious Rafael Nadal captured his 21^st ATP Masters 1000 title and his No.2 Rank, giving him confidence to head to Roland Garros, the championship which he owns, for his seventh title.

I saw the match between Nadal and Djokovic at the Monte Carlo Masters 1000 finals and witnessed some difference in strategy employed by Nadal for the first time against the World No.1. Almost every time Nadal will go for the backhand corner of his opponent when he served, but this time around Nadal peppered his serve to Djokovic forehand. Many of the experts believed that the win at the Monte Carlo Country Club was because of this new way of serving that Nadal had appointed. Nadal for the entire match didn’t serve even once on the backhand corner of the Serb. My anxiety was at peak on Sunday as I awaited the match to begin, only to find out that it was rescheduled to Monday noon. While watching the match between Nadal and Djokovic for the Internazionali BNL d’Italia Championship I put the serve theory to test. This time however Nadal served the way he usually does, but also mixing it well with some body serves and serves to Djokovic’s forehand. Nadal played a positive game despite of the fact that he was not serving all that well. His serve was broken only once in the match despite of the fact that both Djokovic and Nadal got the same number of opportunities to break the other’s serve. Nowhere in a match is Nadal’s mental and emotional strength more on display than when his back is against the wall facing break point on his serve and he showed that getting a breakpoint against him on clay is one thing and converting it is altogether a different proposition. Nadal, who usually is found scrambling on the baseline was seen to go up to the net and volley, showing the attitude the man is made of.

Nadal now goes into Roland Garros, where he will attempt to lift the trophy for the seventh time, with a 16-1 record on clay this season. The journey to the seventh title will not be easy, Nadal will surely take the momentum to the second grand slam of the season but his rivals in Djokovic, Federer and even Murray, who can become a dark horse and make it tough for his opponents, will surely have everything to play for as Nadal will be under pressure since he comes in as the Defending Champion and the second seed. The draws will be released on 25^th May. Keep watching this space for the news from the French Open

How many times have you found yourself in a situation where you did all the hard work creating a database, its tables, queries, forms reports etc. and all of a sudden for some reason your database file is corrupted? You will have to re do the entire tedious cycle which had done before, not to mention the disappointment and possible impact on your job. Also, there is no ruling out of the possibility of it happening again. Such situations are as frequent as computer crashes, virus or malware infection. Is there a way out? Do we have a permanent solution for this?

Well, Cimaware Software develops data recovery utilities for businesses and individuals. Since the release of the OfficeFIX product line in 1999, Cimaware Software has become a leading provider of Microsoft®Office file recovery utilities and has sold its software to some of the largest businesses in the world. The AccessFix product that they provide is a brilliant tool enabling you to recover data from even the most damaged database. What impresses me the most about this product is its versatility, it works on all the standard versions of Windows® and Microsoft®Office and even those that are outdated. They have not left any stone unturned when it comes to recovery of your valuable data, you can recover the entire set as good as new. Lets look at some of the features provided by AccessFix.

The solution provided is quick, smart and safe. Unlike other vendors, Cimaware provides you with money back guarantee, technical support for the tool and free evaluation so that you buy the product only if you feel its appropriate.

For more information you can view the video and visit Cimware Software

Preparing MS-Excel based test beds is what my job is, as boring as it sounds it helps me learn new things and understand how powerful a tool MS-Excel really is and use it to get the job done. In this tutorial I am going to share a learning I came across recently, the use of array formulae. For beginners the term Array Formula(AF) can be little intimidating, but actually its as simple as the problem you want to solve and a little knowledge of the basic formulae which you will actually use to formulate the main formula will make you realize how easy this entire process is.

I am using LibreOffice Calc for this tutorial but the same process can be carried out in MS-Excel too. So jumping in, lets take a very simple example to start with. In your spreadsheet application copy the following two columns Number Sold and Unit Price to column A and B respectively (see the screenshot attached).
NUMBER SOLD UNIT PRICE
5                           2200
4                           1800
6                           2300
8                           1700
3                           2000
1                           1600
9                           2150
5                           1950
6                           2250
8                           2000

Now I want to find the total sales for each row. For this lets perform the following steps:

1. Select the cells C2:C11 and enter =A2:A11*B2:B11 in the formula bar.
2. Press the Key combination, CTRL+SHIFT+ENTER.

After this process is done, you’ll see the following entries get populated against each row.

If you navigate across the cell rage C2:C11, you’ll notice that all the cell have the same formula, notice the extra {} that enclose the formula which we had entered; this actually tells your spreadsheet application to process the formula as an AF. Now try deleting one of the cell in the range, you wont be able to . Which is fine as all of them use the same formula. Now lets go one step ahead, try finding the total sum in one go(please do not take the easier way out by summing the values we found just now ). You guessed it right, this is how it’ll be done

1. Select cell C12 and enter =SUM(A2:A11*B2:B11) in the formula bar.
2. Press our favourite key combination CTRL+SHIFT+ENTER to obtain the required result.

Simple isn’t it? Now to a little bit of theory so that we are able to expand our horizons and vision so that we can use this concept well.

If you’ve done even a little programming, you’ve probably run across the term array. For the purpose of this tutorial, an array is just a collection of items. In spreadsheet applications, those items can reside in a single row (called a one-dimensional horizontal array), a column (a one-dimensional vertical array), or multiple rows and columns (a two-dimensional array). You can’t create three-dimensional arrays or array formulas.

An array formula is a formula that can perform multiple calculations on one or more of the items in an array. Array formulas can return either multiple results or a single result. For example, you can place an array formula in a range of cells and calculate a column or row of subtotals. You can also place a formula in a single cell and calculate a single amount. An array formula that resides in multiple cells is called (logically enough) a multi-cell formula, and an array formula that resides in a single cell is called a single-cell formula.

Now lets make things a little complicated, consider this case, I want to find the average of the those sales which fall in top 50% of all the sales. . So, lets see how to do this :

1. Finding out which all formulae that we have to use
   1. Average
   2. Conditional statement (IF)
   3. Percentile to get top 50%

2. Formulate the main formula now. It’ll look like this

        =AVERAGE(IF($C$2:$C$11>PERCENTILE($C$2:$C$11,0.5),$C$2:$C$11))

3. And CTRL+SHIFT+ENTER and the braces will appear.

Final advice, if you do not press CTRL+SHIFT+ENTER the formula will give an error. Now let your imagination free and try this out.

This is one of those technical ideas that always fascinated me. But I was never able to carry it out until yesterday after I upgraded my laptop to Ubuntu Precise Pangolin (12.04 LTS). The idea was clear to me since the beginning and is very simple. You must have heard about bluetooth headsets, well, we are going to use the same principle just that instead of headsets its going to our laptop/desktop speakers. After this most of you can leave the rest of the tutorial and try it on your own, for the remaining, please follow the instructions as stated below. But for making this work, you need a internet enabled ubuntu 12.04 desktop along with bluetooth and a bluetooth enabled cellphone.

1. Click on the Software Center. So that you can install a software.

2. Search for an application name “Blueman”, its short for Bluetooth manager. Blueman enables you to use the unprecedented power of bluetooth. Its like a bluetooh Swiss knife

3. Install the application.

4. Now you’ll see the top menu bar as below. There will be two Bluetooth logos, the one with some complicated looking menu items is your Blueman.

5. Setup your mobile device by clicking on “Setup New Device” option from the menu and select the following option to stream. Complete the procedure by clicking on Forward.

6. After this, click on the menu of the Ubuntu desktop again and select “Devices” to view this screen.

7. Now right click on your device and select Audio Source. This will prompt your mobile to accept the connection, which you’ll say, “Yes” to.

8. Now if all goes well it connection will established and you can start playing music in your cell phone music player as the sound comes from the computer.

Well that’s all. Hope you like it.

Cheers.

The newspapers, radio stations and social networking websites are buzzing with the news that Master Blaster Sachin Tendulkar has been nominated to the Rajya Sabha. Sachin has been nominated along with yesteryears’ Bollywood actress Rekha and business leader Anu Aga. This is not the first time that sportsmen and celebrities turn into become MPs, what makes this news worth of the all the marketing and buzz its creating is matter of fact that Sachin is the first active sportsperson to be nominated to the Rajya Sabha (and of course the fact its about Sachin ). Recently former Indian Hockey Captain Dilip Tirkey too was sworn into Rajya Sabha, but Dilip has retired from international hockey quite some time back; Sachin on the contrary has not shown any signs of such a decision. Lets take a look into what I feel Sachin should do to this offer.

Let me start the reasoning with a question, Boring Rajya Sabha or another Cricket World Cup?As great an opportunity as it may seem to be part of a pack that leads the Nation; viewing the current state of affairs in politics I personally feel that Sachin should refrain from getting into this. Sachin has always been a role model for the youth of the country and even around the world, his attitude on and off the field has been nothing less than exemplary. Even the television commercials he signs for, are done after a great deal of pondering, because he knows that with billions in the fan kitty a celebrity of his stature has a lot more responsibility off the field than on. So technically, if he is so good, he should become a parliamentarian. But lets take a step back and reassess the situation by picturing what will happen if Sachin does become a MP. He’ll be surrounded by people who have been in politics for more time than Sachin has been on the field playing cricket. Sachin of course will be getting affiliated with a party, which he will be deciding on his own depending on the parameters he thinks are good for the Nation and him. But we all know what a mess has the Indian political system been and Sachin will have to first actually reform the stringent and stiff Indian Polity before he can use it as a weapon as good as his bat to push the nation forward. I will not be surprised if he is used as a pawn by someone to will in the vote banks, which is not what any of his fans want.

So should Sachin never join politics? Ideal case, he shouldn’t because we all know he loves cricket more than the parliament. But he can, may be after 20 years when is mature enough to understand the so called experts in this field who fool the nation everyday. But anyway even if he decides to step into politics I suggest him to become a part of it only after a retirement, although ideal scenario will be he becoming coach of the Indian Cricket team and bringing another World Cup to India.

Few days back while travelling to office via Delhi Metro; after the usual rushing into the train to grab a seat, I took out the book (It’s Not About the Bike – Lance Armstrong) I was reading then. With my hand searching my bag for the book my eyes scanned the fellow passengers who were getting in the train one after another in what seemed an endless trail. Amongst all this, I saw a girl with what looked like moist cheeks and puffy eyes. I was pretty sure that she was crying and that too for a long time; before I could catch a proper glimpse of her, she disappeared in the crowd. By the time the train started I was ready for my 50 minutes long journey on a two seater that we find at the end of the Delhi Metro Coaches. While reading the book I glanced to my left to notice that the girl was standing next to me, she was not facing me. I could see her properly now, not giving into the sight I continued reading. All of a sudden I hear her gasping, this time she was on the floor of her train, sobbing. I got up and asked her to sit in my seat; she refused, I said, “I am not asking you, I am telling you to sit here”. I knew she won’t argue, she had other things to be worried or feel sad about than someone offering his seat. As expected she sat down kept her head down on her lap as I stared at her for few moments and went back to reading.

I am sure we all have cried and have been terribly sad over innumerable things and happenings in our lives. This girl was one of us; I didn’t ask her why she was crying or tried to reason with her but at the same time I felt that whatever she may be grieving on will any way not last for long (just like everything in the world). We have all made a shell around ourselves, a, what we feel as a protective covering and when this shell gets punctured we become sad, cry and become remorseful. You can think of this shell to be made of things that makes us happy or makes us feel wanted and no matter what happens we want to be surrounded by stuff that makes us happy. The shell when shattered or punctured makes us insecure and we mostly give into the situation. We lose our control, intelligence and do things we all mostly repent later. Insecurity is perhaps the worst thing that can happen to us and we all suffer from it almost all the time. This feeling of being vulnerability is because of lack of faith we have on ourselves. I lost perhaps the most important person in my life because of this very feeling and I repent every now and then. At the same time the feel of self belief comes and I come out of it.

We all are insecure about every small thing that perhaps defines us and makes us who we are. The songs we hear, the way we look, the achievements, the failures, the list is endless. In short we can be insecure about everything associated with us. To get rid of this seemingly incurable problem I looked at perhaps the most confident individuals in the world, “Children”. Children have seen less failure hence more confident, they just want to enjoy each moment of their lives playing, cheering laughing; even their cry lasts for few minutes as they start smiling and laughing at the very next amusing thing they see.

While running my mini marathons during weekends, I am joined by some kids running with me or perhaps in the opposite direction, few of them run almost for the full duration with me. They breathe hard, they get drained but they keep their small legs running. I encourage them by slowing down once in a while letting them take the lead which makes them happy as a broad smile appears on the face and when they tire I take the lead pulling them. Most of the time seeing this, the older guys start moving their legs a little and jog on their course. Those kids do not feel vulnerable, when they start running it feels like they’ll run forever with grace and happiness because they love every bit of it. Kids do not think before taking a pencil in their hand and drawing what they see or write what they feel. They may run fast and erratic without discipline, they may make the most bizarre drawings or write something that neither affirm the grammar of the language or rhyme but they are better than everyone who have stopped running because they feel they are too tired of bench work they have done, better than everyone who stopped drawing because someone told them they cannot draw a straight line and better than everyone who feel they are educated but are at loss of words when they take a pen in their hand.

We all were kids and trust me we all were ideal human beings, but we have lost our way, have become conditioned by the tastes of what we have termed success and failures. We fear defeat more than death at times. We just need to have the wisdom that we or anything we have will not last forever and that its just going to make you a better and a stronger human being and not an insecure and vulnerable loser.

Cheers To Life and all it provides! Ad Astra per Aspera!

It’s been more than two months (Part-5) now that they are struggling to reveal their story to their parents. Mishti and Arav were keen on telling their parents about the relationship they share but from today to next weekend, to next month situations have been forcing them to delay this meeting with their own parents. The first time Mishti invited Arav home to have a word with his parents; Arav could not make it because of urgency in his office. They again planned it for the next weekend but that weekend Mishti’s parents had engagements. Things were not going smoothly as planned with them. But they had to tell it to their parents anyhow. They knew that it was too early to reveal everything to their parents but they wanted them to be assured that their children had already chosen someone for their own selves. They were very confident of the fact that their parents will definitely approve their relationship. They did not actually see any reason behind the rejection of this relationship by both their parents and this gave them further confidence to approach them at such an early stage.

Arav’s friends suggested him many times that it would be better to give this relationship more time and see if his expectations were fulfilled by Mishti. But he was certain that now it is only Mishti that would be the better half in his life. Similar was the case with Mishti. Not many friends around Mishti knew about Arav, but the two friends who knew always warned her about the hasty decision of sharing the alliance with her family. But just like Arav, Mishti also felt that it was the right time to disclose things to her parents. One Friday night, Mishti told her parents that a friend of hers would be visiting them and joining them over dinner.

Mishti’s parents were unaware of any man in Mishti’s life and so they were surprised when Mishti told them about someone joining them for dinner. It never struck Mishti’s parents that their daughter could be having a relationship because her routine had not changed even a bit. She was never home on weekends before Arav came into her life as well, the only difference was that initially she use to spend time with friends and now it was Arav who took all her time. But the moment Mishti told about some friend visiting them for dinner, her parents got an idea that there was something cooking which they were unaware of. They agreed to a formal dinner evening because they didn’t want to say a ‘no’ to their only daughter. Seeking approval from her parents, Mishti called up Arav and told him to reach her place latest by 7 pm. She also told him that he should not get late because her parents don’t like waiting.

Mishti was preparing herself for the big dinner and so was Arav. He wanted to look his best before Mishti’s parents. This was the first time Arav would meet her parents and this would be the time when he would tell Mishti’s mom that he was crazy for her daughter. Arav did not had much time to groom himself as it was already 5 pm and he had to leave at 6 pm. He dressed very formally and thought of buying a bouquet for Mishti’s parents. He left home a bit early because he didn’t want to get late when he meets Mishti’s parents for the very first time. He picked up a decent bouquet for her parents and headed towards her home. Mishti on the other hand was also getting the stuff ready at home. She was helping her mom prepare the dinner so that when Arav reaches her place she and her parents can sit with him and talk.

Things were all going well and then exactly on time the door bell rang. Mishti expected it to be Arav and so ran to open the door for him. It indeed was Arav and she welcomed him home and introduced him to her parents. They sat down for some time and then started the actual conversation. Mishti’s parents were keen to know about Arav and so they started questioning him about a lot of things including his family, his education, his profession, his future and lot more. Arav was not prepared for all this because he expected that it would be a normal meeting where he would just accept his love for Mishti and that would say all. But it was obviously not what he expected because Mishti was their only child and they wanted the best for her. They had quite a long discussion and a healthy one and then Mishti’s mom invited him for dinner. They had dinner and over dinner also they discussed a few things about how and where they both met and since when have they been together. Arav told Mishti’s parents that it had now been nine months that they have known each other although they have been in a relationship for only the past five months. Mishti’s parents had a confused expression on their face but they subdued it and finished dinner. It was already ten past five and so Arav decided to take leave and head towards his home. He greeted Mishti’s parents and left them for the actual discussion about him to start.

As soon as Arav left, Mishti showed an expression like that of a restless child to her mother. She was curious to know what her parents felt about her choice. Mishti knew that there was nothing wrong about Arav that her parents could not disapprove him, but still she wanted to know their views about him. She insisted her father to tell her about what he feels about Arav, but her dad first insisted on cleaning the house so that they could sit in peace and discuss. Things were all done as told by Mishti’s dad and finally late at night, both Mishti’s parents as well as Mishti sat down to discuss about Arav. On the other hand, Arav was also very anxious to know what Mishti’s parents felt about him, and so as Mishti and her parents discussed he waited for the discussion to end so that he could have a word about the same to Mishti.

Mishti’s parents wanted to know a lot about how things happened between the two of them and so they had a discussion with her. Her father only had one concern about the whole thing. He felt that it was too soon for both of them to decide about their future. Mishti told his father, that they don’t wish to get married at this stage; they just want their parents to be aware about the whole thing. She told him that once she gets approval about her relationship with Arav, she would meet Arav’s parents. She told her father that she wanted to make her parents meet Arav so that they don’t look for a suitable match for her; she made it very clear to her parents that she would not marry so soon, but would wish to marry only Arav. She wanted their approval for the same. Her father understood her completely and he had nothing against Arav since he was good. He was from a decent family background, was professionally qualified and working well to live a happy life ahead. Above all, he wanted to see his daughter happy and he could see that she was happy with Arav. But still he was doubtful of everything because it had been just five months that they had been together. There was nothing else that was disturbing her dad.

Till this time her mom had been silent and listening to the communication between Mishti and her dad. She had not said anything; but seeing her so silent Mishti asked her, “Mom, apko kaisa laga Arav?”. Her mom was still silent and kept looking at Mishti. This silence was actually killing Mishti. She was getting negative vibes from her mom this time and that was scaring her to the core. This time she took her mothers’ hand in hers and asked her how she felt after meeting Arav. This time her mom looked into her eyes and just said, “I don’t feel good after meeting him; I don’t think he is the right choice for you. You are my only daughter Mishti, and I want the best for you because you are the best. You deserve someone else… someone better than Arav. I cannot approve this relationship of yours”.

Nature has endowed India with the most fertile lands in the world, and this land of plenty has been enclosed within the most secure natural borders. In the vastness of her fertile plains, in the greatness and ubiquity of her rivers, and in the abundance of her rains and sunshine, there is just no other region of the world that can be compared with India. It is indeed to express their gratitude for bringing such abundance to our lands that Indians having been paying perpetual obeisance to the Himalayas; to Ganga, Yamuna, Godavari, Sarasvati, Narmada, Sindhu and Kaveri; and to the gods of sun and rain.

            Having been blessed with such plenty and natural security, Indians have created a civilization that is unparalleled in its spiritual as well as material affluence and sophistication. They have learnt and taught others to live in harmony with, and to solicitously care for, all sentient and insentient beings. They have evolved a social system that, with its anchorage in the family and community, is self-disciplined and self-regulating. And they have acquired extraordinary skills in agriculture and industry to convert their natural endowments into such material wealth that foreign observers, from the time of Alexander up to the beginning of the nineteenth century, have marvelled at their riches.
            India is a vast country. From the northern borders of Kashmir to the southernmost tip at Kanyakumari, our land extends over 3200 kilometres, and from the eastern boundary of Assam to the western border of Baluchistan, it extends over 3500 kilometres. Total area of what has historically constituted India is 423 million hectares! The fact that Indian Ocean is the only one in the world that is named after a country, speaks volumes about the tradition of Indian opulence and abundance and the Indian discipline of sharing over the successive ages in history from the ancient most times. Uttaram yatsamudrasya himadrescaiva daksinam. Varsam tadbharatam nama bharati yatra santatih.
            With rich soils, climate and water, it is not surprising that India supports an extraordinarily great variety of flora and fauna. There are about 45,000 species of plants including shrubs in the country. Of these 35 percent are endemic to India, and are not found anywhere else in the world. India supports 75,000 species of animals, birds and insects. This forms one-twelfth of the known fauna of the earth, though in terms of geographical area, we are only one-fortieth of the earth.

            India’s great mineral wealth makes her probably the third most gifted of the world’s regions with respect to industrial capacity. Having blessed with such extraordinary natural wealth it is not surprising that Indian lands have always supported vast multitudes. But we have not only been blessed with a land of rare fertility and wealth, but also the gods seem to have conspired to make this land into an impregnable fortress. Indians have been the largest civilisational group in the world till almost the modern times.

            India lives in harmony with all creation and sees unity and order in all. Sharing and caring is the essence of Indianness. These basic tenets of Sanatana Dharma brought students from all over the world to the shores of India to study the people of this land and their philosophy and culture.

            Indian civilization has not only comprehended the essence of the universe; it has also evolved a social order appropriate for leading life in conformity with that comprehension. The Indian social organization is thus as peculiarly Indian as the larger view of the universe. The grihastha, the householder along with his family, and not the individual, forms the basic unit of Indian social, economic and moral order.

            The Dharma Rajya: much of what a modern State is expected to do in the sphere of public polity is in India accomplished through these social groupings – of the community – the grama and the sampradaya – in their respective domains and in their mutual interactions – with the king who sits at the apex of this self-generating and self-regulating polity regulating harmonious functioning of these diverse groupings and institutions of the society in their different domains and roles.

            But it was not just the socio-political institutions of this land and their ethical tenets or the view of cosmic oneness and the rich philosophy and culture of its people that brought the foreigners and marauding invaders into India, but it was India’s excellence in the fields of agriculture, industry and enterprise that resulted in her overflowing opulence. Textile was not only the great industrial enterprise of India but her greatest craft too. India excelled in iron and steel making also.

            India entered a phase of eclipse with the coming of the British; the darkness had probably begun to set in earlier, since about 1000AD. However, this period of subjugation to alien rulers constitutes a miniscule part of the long civilisational history of India. With independence, India has begun to come into her own. India has achieved much in the frontier technologies of today. We have established a strong infrastructure and knowledge base for a great industrial revival. The families and communities of India have begun to participate in this revival using their long established civilisational strengths and skills.

            Mahatma Gandhi had reawakened the spirit of India. Under his great and dynamic leadership India aroused great expectations in the world.

            On gaining Independence, we could not entirely fulfill the high expectations the world had from us. But, we began the task of restoring our lands and our society to some state of health in earnest. People of India began to improve their lands and their agriculture. The craftsmen and artisans began reviving the manufacture and industry. The State began to pay attention to rebuilding the infrastructure.

            The fact of being Independent, and the invigorating experience of having participated in the great and righteous struggle for freedom, restored the initiative and the dignity of the Indian people, and they began a revival of their country and their society.

            The revival however is constrained by the failure of modern metropolitan elite of India to recognize the strengths of India and of the people of India. The elite India so far has failed to enter into a proper partnership with the ordinary India that is organized around self-discipline and self-regenerating families, communities and localities and is anchored in the Indian civilisational genius.

            The national effort should be aimed at facilitating this partnership between metropolitan elite and the families, communities and localities of the people of India. Once the partnership is restored, there would be nothing to stop India from achieving the glory that is rightfully hers.

How many times have you used VLookup and felt that the column you want to look up on comes after the one you want to display? I have been using MS-Excel extensively at work and the problem I described is encountered every day by me. I hardly thought of an answer and always went using the work around like, to temporarily insert a new column, copy the column to be looked up on to the new column, populate with VLOOKUP, Paste Special Values, then delete the temporary column. Tiring, especially if you have to do this every time. Now, I had no idea that there were other formulae that can be used to do this, however I came to know about this when I was trying to decipher an already constructed excel workbook’s formulae, it was using certain commands like MATCH and INDEX and as a usual practice I went on to learn about these functions on the excel manual I have.

Before we get into these functions let us try to understand the problem once again. There are three columns in the table that is shown in the figure below, Roll Number, Student and Score. If you are given a roll number you can easily find the name of the student and the score obtained by using VLookup as shown.

Piece of cake right? But now consider a scenario where in you have to find out the Roll Number for the Name of the student. If only VLookup can take -1 as an argument for the column number there will not be any such issue. But this doesn’t happen, to tackle this as I have mentioned there are two functions. Yes you need to use both of them, will take some time but again, what doesn’t. Lets take each function one at a time.

MATCH: =MATCH(Value, Single-column data range, FALSE). This function Searches the data range and returns the relative row number where the match is found. So to find which row in the table above is “Albert”(mentioned in cell A11) present we do as shown in figure.

And Match returns the row number which is 2 in this case. With this done. Lets move on to the next Function.

INDEX: =INDEX(data range, row number, column number). This function seemed really silly to me but again this will be helpful. So as the formula suggests if you provide the data range along with the relative row number and column number it’ll return the value stored in that cell. From Match we have the row number already, so after specifing the data range we can give the match function as the row number argument and give the column number whose data you want to display. See the figure

And the problem is solved. So the process is simple, use match to get the row number and use it in the Index function to get the corresponding column value in the data range.

In case you face any issue, which I dont think you’ll just comment on the post. Cheers

I have installed Ubuntu 11.10 a couple of times by now. I first install Ubuntu 11.04, then load the packages and finally upgrade to 11.10. It’s a long shot but then it works every time anyway, recently I tried the same procedure on a friends laptop, everything was fine, except when I wanted to upgrade. (Oh, by the way I load the upgrade files on a bootable pen drive). The Synaptic Package Manager showed an error which was something like – “E: Encountered a section with no Package: header
E: Problem with MergeList /var/lib/apt/lists/
download.learnfree.eu_repository_skss_Packages”.

Now I am just a beginner with ubuntu and solutions to problems don’t really “click” instantly for me. So obviously I dug the net. The logic of solving the problem was actually pretty simple but it took a lot of time to comprehend. You see while installing Ubuntu 11.04 a few packages were only partially installed, to solve that you need to remove the packages/directories and create new ones again. Now there are two ways to do this I suppose.

1. Type these in your terminal

sudo rm /var/lib/apt/lists/* -vf
sudo apt-get update

This however didn’t work for me, maybe because of the net connection.

2. The following commands worked like a charm

sudo rm -rf /var/lib/apt/lists
sudo mkdir /var/lib/apt/lists
sudo mkdir /var/lib/apt/lists/partial

So after executing this when I started my synaptic manager again, it gave a
different error “broken packages”. The solution to that is simple: Edit->Fix
broken packages->Apply.