Using VBA Collections To Compare Two Sets Of Data In Excel

A common task for Excel users is to compare two separate lists of information with similar data. Usually the goal is to find duplicates, update data or transfer data from one list to another. For example:

  • Merge two separate customer lists which might contain common entries
  • Update product details by transferring data from one list to another
  • Identify duplicates across the two lists

We’ll write some simple VBA code to identify some data in one worksheet based on the information in another sheet. The scenario we’ll look at is updating a master price list based on a separate update sheet.

Our code will assume the update file has a listing of unique product codes in the first column and the new prices in column(2). We’ll create a new collection of product codes to enable us to directly access the data in the master file which has an identical structure.

Finding Data Efficiently In A Spreadsheet With VBA

The problem with finding data in Excel is sometimes you either have to loop through every cell or use a filter or match function each time the data needs to be found. In our price update scenario, the looping structure would be something like this:


For each cell in updateFile

For each cell in masterFile

'find the matching entry

next
next

For a small data set this loop creates few problems but what if there were over 1000 entries? The code might take a while to run; instead we’ll look at some code to take us directly to the matching entry in the master file:


For each cell in updateFile

' go to the matching cell in the masterFile
next

Using A Collection To Index A Worksheet

The first task is to create an index of the master sheet product codes and their row numbers in a new collection


Dim productCodes As New Collection
sheets("master").activate
Range("a1").CurrentRegion.Columns(1).Select

For Each c In Selection

productCodes.Add c.Row, c.Text
Next

We’ve created an index of product codes by adding the row number of each code and, importantly using the product code as the unique key. This means we can directly access the product code and get the row number in the master file.

Updating The Master File By Using The Product Code Collection

Now, all the code needs to do is loop through the update file and directly access the matching product code in the master file and update the price.


Sheets("update").Activate
Range("a1").CurrentRegion.Columns(1).Select

For Each c In Selection
' get the latest price from the update file and access the
' row number of the matching product code in the master file

newPrice = c.Offset(0, 1).Value
rowNo = productCodes(c.Text)

' update the master file
Sheets("master").Activate

Range("b" & rowNo).Value = newPrice

' go to the next product to update
Sheets("update").Activate
Next

Summary

Instead of repeatedly looping through data in Excel to find a particular cell, it makes sense to directly access a cell if we know the value. This is a good example of using VBA to address a common task and making the process more efficient and productive.



Source by Andy L Gibson

Finding the Right Get Your Ex Back Ebook for You

Finding aa good eBook on getting an ex back is actually surprisingly easy. Sadly not all of them work for more than one of two situations though. Most of the time when you buy a get your ex back eBook you find yourself with something that only works for certain people or certain age groups or even certain types of relationships. There are ways to avoid this but you have to do some research to find the one for you.

First go through a bunch of choices and find a few that at least look like they are not trash.

This part is pretty easy because if it looks too good to be true it usually is. So any choice that says they can get you back together tomorrow I would stay clear of. Or of the advertisement is really flashy and elaborate they are probably trying to overcompensate for something that is missing. So when looking for a good get your ex back eBook you should go with the ones that offer results not instant or flashy things.

Check out the author, see if he or she has any personal experience.

A lot of the time you will find an eBook written by someone who knows nothing at all about the relationship market so has no idea what they are talking about. So either find an eBook written by a psychiatrist or psychologist, or written by someone who has some experience in the breakup to makeup arena. To do that just check out the reviews of the books, they can usually give at least those details. Just do not put too much stock in reviews because they are usually written by people who are selling the ebooks themselves or by people they pay to write for them.



Source by Eric M Hill

The Kangaroo Tail Pattern in Technical Analysis

The kangaroo tail is a pattern that points trading opportunities for traders. It has been introduced in Alexander Elder’s book, Come Into My Trading Room.

The tails could be used to mark trend reversals in markets. While trends need long time to form, the kangaroo tails are formed in just a few days.

The kangaroo tail pattern takes a minimum of three bars to form. A tail is a one-bar in bar chart or one-stick in candle stick chart, which spikes in the direction of a trend, with surrounded narrow bars at the beginning and at the end. That middle bar is the tail, but traders will not know for sure until it followed by a reversal in the following day, when a bar or a stick has sharply narrowed back at the base, letting the tail hang out.

Market tails often occur at turning points in the markets, offer a trading opportunity to traders. Traders should recognize tails and trading against them. Whenever traders see a tail, they should be ready to put on a position trading against that tail, before the close.

Once traders enter trades by using tails, they should place protective stops at approximately half-way of the tails. If the tails are being chewed up, exit without delay. As for the profit-taking targets, they might be established using moving averages and channels.

The kangaroo tail pattern is one of the most reliable reversal patterns, especially when it is supported by signals from other indicators. Furthermore, the pattern could be used in any time-frame. A tail in a longer time-frame usually generates a bigger move than a tail in shorter time-frame.



Source by Hideyoshi Taro

Discover the Secrets to the Best Natural Skin Care Methods

Treating skin properly is something that everyone should be concerned with. You should be aware that proper skin care can also reap benefits that go beyond beautiful-looking skin. There are a number of ways to perform regular skin maintenance. Yet natural skin care has emerged as a way to avoid perfumes and other chemicals that typically treat the skin, but potentially can harm it. In the UK, the best natural skin care methods can involve some unorthodox measures – but primarily is easy to accomplish if you're dedicated to better skin and better overall health.

One recommended method to enhance the skin involves the sustenance that goes into the body every day. Plenty of people find that they need to observe a better diet anyway, yet many may be unaware that a proper diet might just lead to improved skin health, as well. The first and foremost staple in a healthy diet is plentiful amounts of water. We all need a large (although not excessive) daily water intake to boost our body's waste disposal systems. Not only can lack of hydration lead to constipation of the body … it can "constipate" the skin, as well, through clogged pores and the like. In addition, one should be certain they are eating right. In many cases, when people are experiencing skin problems, the lack of certain nutrients may play a huge role in contributing to those problems. Fiber – whole grains, in particular – is vital to boosting your skin health. Be sure that you do your best to limit sugar intake, as this can hinder skin progress.

One other need those who have skin issues must address is getting more exercise. In fact, getting up and doing a modest amount of daily exercise can increase circulation to the point that skin health shows improvement. The more exercise you do, the more dramatic skin improvement can be!

The type of skin products that can inadvertently harm your skin can be avoided by looking into organic products. Using organic products can be a way to better your skin – and you do not have to spend a lot to do so. Although there are companies that sell these products, you can also use items like honey and olive oil to revitalize skin.

By obtaining the methods necessary to achieve the best natural skin care possible in the UK, you can see results that refresh and invigorate your skin.



Source by Chris J Harris

The 7 Golden Rules To Grow At Least 2 Extra Inches To Your Penis Size Within 8 Weeks (NATURALLY!)

Yes, I’m dead serious. You can actually grow at least 2 extra inches to your penis size. Even more, you can do this within 8 weeks. Even more than that, you can do so 100% naturally, without side-effects, without spending a fortune, and you can keep all of your gains… for life! You can have a porn star sized penis before you know it!

Sounds too good to be true?

Well, it is true, and the way to make it all possible is to follow through with the 7 golden rules of male enhancement. If you follow these rules, then it is almost guaranteed that you can increase your length, girth, and firmness of your erection. You’ll also make your flaccid size bigger, fix premature ejaculation, shoot your load out like a cannon, and so much more!

Okay, if you are ready to get on the right track to penis enlargement success, then down below I’m going to reveal those 7 golden rules for you to follow.

But first…

Please understand that you will NOT get the results you are hoping for unless you first follow ALL the rules below. Second, you won’t get the results you want unless you stay 100% consistent.

The rules below are all based around NATURAL enlargement. This means that although the results come fast, there are NO SHORTCUTS to success. Consistency and dedication is what will get you the AMAZING penis size lightning fast.

Alright, now let’s get into those rules, shall we…

Rule #1 – Avoid the unnatural approach by all means necessary!

Doing anything unnatural on your endowment is only going to cause you pain, side-effects, little to no growth, and you won’t keep whatever you gained permanently.

Unnatural options includes pills, surgical methods, tools, creams, patches, lotions, and potions!

That may have just sounded as if I mentioned every last method there is. But that’s not the case. There’s only one tried and true method that is gentle, safe, POWERFUL, and guaranteed effective… and that method involves using nothing but your hands to maximize your size.

Rule #2 – Change your diet.

What you eat directly can affect how big you can grow. It can also affect you in a negative way with getting bigger.

Foods to eat to grow bigger include foods high in protein (lean chicken breast for example), antioxidants (such as berries), healthy fats (such as unsalted almonds), and micronutrients (such as RAW fruits and veggies). I also recommend drinking at least 1/2 your body weight in ounces of water daily for great results.

Foods to avoid include salty foods (use Mrs. Dash for seasoning instead of salt), foods high in bad fat (such as fast food), and excessive alcohol.

Of course, following this rule will improve your overall body as well!

Now, the reason watching what you eat is so critical is because of blood flow. Proper blood circulation is VERY important with getting a bigger and stronger penis size.

Rule #3 – Change your lifestyle.

I LOVE video games! I can play Skyrim or GTA 5 until my fingers go numb! However, having a sedentary lifestyle will do you no good with getting a bigger penis size.

If you want to get bigger, and protect your overall health might I add, I strongly suggest you get more active. This includes fitness at least 3 times a week, and getting more active throughout the day. I don’t mean to be on your feet all day long, just spend a good chunk of your day doing something active.

Staying active keeps your blood flowing effectively and increases testosterone levels… which helps with growing a bigger penis.

Rule #4 – Increase blood flow naturally.

What this means is to avoid taking male enhancement pills and instead follow rules 1-3 above.

Rule #5 – Enhance your penile chambers.

Your penile chambers are the chambers that expand to form your erection. When they expand, they fill up with your increased blood flow from rules 1-4 above! The key to getting a bigger erection is to increase the size of your penile chambers so that they can hold more blood… which equals a BIGGER erection!

The only possible way to do this is to stimulate your chambers using just your hands.

Rule #6 – Extend your suspensory ligament.

Your ligament is what makes up the length of your penis. If this ligament is gently and naturally extended, this is how you’ll be able to grow at least 2 inches in length within 8 weeks.

Again, this is only possible… and safe, with using just your hands.

Rule #7 – Strengthen your PC muscle.

The PC muscle is a very tiny muscle that controls your ejaculate flow and it also helps with proper blood flow to your erection. If you naturally strengthen this little muscle, then you’ll no longer have to worry about premature ejaculation, you’ll have stronger erections, and you’ll have explosive orgasms.



Source by Anthony Sciuto

Safe Alternative To Vaniqa For Facial Hair Removal

Is there a safe alternative to Vaniqa cream for women wanting to do something about unwanted facial hair?

Yes there is! In fact, this alternative to Vaniqa was on the market some years before Vaniqa was finally approved by the FDA (Food And Drug Administration) for public distribution on prescription in the year 2000.

In the early 1990’s a lesser known company operating near Toronto in Canada by the name of Nisim International was carrying out extensive research in its laboratories for a natural herbal solution to hair loss.

In 1993 they released the NewHair Biofactors range which consisted of deep cleansing shampoos and a hair growth stimulator made from natural herbs. It received a positive write up in the Medical Post of 1993 and the article listed some impressive success results from the clinical trials. (See http://www.4-hair-loss-solutions.com/baldness-medical-post.html for the full text of the article)

In the course of this research the folks at Nisim stumbled on a way to not only stop hair loss and get hair to regrow, they also found a way to stop unwanted hair from growing by interrupting the mechanism for hair growth in the hair follicle.

This led to the release of Kalo Hair Growth Inhibitor in 1997, some years before Vaniqa arrived on the scene.

Vaniqa And Kalo Compared

There are some main differences between this alternative to Vaniqa and Vaniqa cream. For one thing, Kalo can be used by men, women and children whereas Vaniqa is only for women and girls under the age of 12 are recommended not to use it.

Vaniqa is available on prescription only, Kalo is a non-prescription hair growth inhibitor.

Some may raise a question over Kalo being a viable alternative to Vaniqa because it is not FDA approved. While that is true it is important to understand the process of FDA approval.

Vaniqa was brought to the market by two powerful giants in the personal care industry, Bristol-Myer Squibb and The Gillette Company. Will millions of dollars available they put up the necessary funding to get Vaniqa through the many years of tests and trials necessary before the FDA gives approval.

The final clinical trial results showed that 58% of women who used Vaniqa found a reduction in hair growth. That means a fairly large percentage did not see any difference.

The alternative to Vaniqa, Kalo, is produced by a small company without the massive resources available to the major pharmaceuticals. They also rely on word of mouth instead of pouring large sums into advertising. As a result, Kalo has grown in popularity and since the appearance of Vaniqa on the personal care scene, it has proven to be a viable non-prescription alternative to Vaniqa cream.

Reasonable Expectations

As with Vaniqa, Kalo doesn’t work for everyone. That’s why the company offers a money back guarantee. Given the rate of returns and refunds however, no more than about 1%, it is evident the majority of users are satisfied to a degree.

Finding an ideal method of facial hair removal is a matter of experimentation for many women before settling on the method that suits their pocket, skin sensitivity and lifestyle.

Combining the use of Vaniqa cream, or an alternative to Vaniqa such as Kalo, has helped thousands of women make facial hair removal sessions much easier and more convenient!



Source by Mike Jones

True Stories of Cheating Wives

We always hear about men straying and having affairs. But we also know women can be capable of such things.

There are varied reasons why wives cheat on their husbands. Most often, unfaithfulness of wives stem from loneliness and boredom. Sometimes a liaison with another man is committed out of revenge for their cheating husbands. Here are some true stories:

Cheating Wife 1

A man notices his wife of twenty three years changes from being computer illiterate to an Internet addict in a matter of six months. His trust for her made him ignore the long hours she spent online with her “new friends” and even sending them gifts. Later on, her time for their children suffered too. It turned out she became obsessed with other men, even male prostitutes who solicit online.

His wife had become a totally different woman, influenced by the dark and perverted world she had embraced. She left her husband and family but not before doing as much damage as she can, making hideous accusations and horrid remarks to other family members.

Cheating Wife 2

Another man found out about his wife’s three month long affair. She had slept with her lover several times over this period. Only after being confronted and being face-to-face with evidence did she confess. The husband was naturally devastated but forgave her so they can have a second chance and begin to rebuild their broken relationship. The wife was truly repentant and did her best to make up to her husband.

However, the pain remained inside. The husband did not know how to cope. He wondered if he should know more about the affair in order to get over it. The past just seemed to catch up and hurt him time and again. He forgave but could not forget.

Cheating Wife 3

Who could understand the madness that drove a man to kill his three year old daughter? A subsequent trial revealed the man had been depressed for months after finding out his wife was having an affair. He drugged his daughter with anti-depressant medication he had been taking and killed her with chloroform two days before her fourth birthday. He said he dealt with the deceit and wanted his wife to deal with the consequences for the rest of her life.

Cheating Wife 4

A couple got married after a year of meeting and falling in love while attending university. They later moved to Italy and back to Britain. A year after that, the wife filed for divorce. Minutes after that, he used a can of gasoline to set their home on fire.

But before the divorce papers, there were already marital troubles. Bad enough for the man to threaten his wife of burning their Italian flat so they can be together forever. Among his accusations were his suspicion of his wife having an affair.

Cheating Wife 5

One woman admitted to cheating on her husband every time she got angry with him. She had one-night stands with different men which left her unfulfilled. She wanted to get back on her husband through her infidelity but ended up destroying herself.

Some cheating wives blame their husbands for driving them to enter into affairs. But for marriage to really work, it takes commitment and effort from both husband and wife. Cheating on your spouse is a selfish act.



Source by Mark De Smit