• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Compare 2 columns using VBA

Ejire

New Member
I have an Excel macro which works well in comparing two columns A and B.

it populates column C with unique values in A and not in B, populates column D with unique values in column B and not in A and puts the values found in both A and B in the fifth column (E).

Data in the columns A and B of Sheet 1 has exceeded 1,048,576 (the maximum allowable in Excel 2007) and i will copy the overflow data in Sheet 2's column A and B and i will like the comparison to continue in the next worksheet.

I need the macro to treat the second worksheet as a continuation of the first sheet and not as a separate data.

(The values i am comparing have exceeded 1million)

Kindly assist with the modification on the macro below that will perform this.


#

Sub twocols()

Dim d As Object, na&, nb&, a, b

Dim e, p&, q&, r&, m

Set d = CreateObject("scripting.dictionary")

na = Range("A" & Rows.Count).End(3).Row

a = Range("A2:A" & na)

nb = Range("B" & Rows.Count).End(3).Row

b = Range("B2:B" & nb)

ReDim c(1 To Application.max(na, nb), 1 To 3)

For Each e In a: d(e) = 1: Next

For Each e In b

If d(e) = 1 Then

r = r + 1

c(r, 3) = e

Else

q = q + 1

c(q, 2) = e

End If

Next

d.RemoveAll

For Each e In b: d(e) = 1: Next

For Each e In a

If d(e) <> 1 Then

p = p + 1

c(p, 1) = e

End If

Next

m = Application.max(p, q, r)

Range("C2").Resize(m, 3) = c

End Sub

#
 
Hello Ejire,


Do you have access to any kind of database such as Microsoft Access / SQL Server etc.


This task can be easily done using a SELECT QUERY and would be much faster as compared to the loop and also given the fact the number of records is very high.


~VijaySharma
 
Hi, Ejire!

Absolutely agree with vijaySharma: consider switching to a real database and if needed come back to Excel to perform other analysis or operations over the unique data.

Regards!
 
<p> It does consider a brand new approach to existence, in general, to cure a stubborn acid reflux disorder problem. Believe of it like an enjoyable challenge. You'll really feel so significantly better, it will have been work all the work.</p>


<p> I employed to have problems with acute GERD. I used to be hooked on a PPI drug ..a "certain colored pill". Right after many a long time of taking this drug, my condition grew to become a whole lot worse and I used to be told to double the dose. I began to really feel sluggish and tired. My vision suffered and my blood stress soared. I recognized that these had been feasible unwanted effects of the PPI drug. I did a pc research and identified that there were Karen Millen Dresses hundreds of possible side effects. I was shocked and frightened at what I discovered! I had been harming my entire body and I wished to quit getting this drug! But after i tried to stop the PPI, he acid pumps, which had been shut off, went wild and produced more acid than ever before ahead of.</p>


<p> </p>


<p> The healthcare community might have you believe that only prescription drugs can reduce the signs of abus. This can be basically not the situation. Antacids are, at ideal, a short-term fix and they're loaded with dangerous components including sodium and aluminum. PPI medication really should only be used for 8 weeks, at most. Then what are you to perform These medication only mask and hide the signs and also have hundreds of harmful negative effects. They don't mend the situation! They don't make you well! They only make the drug makers wealthier at your cost!</p>


<p> Consume smaller sized, far more regular foods. I consume four or 5 small dishes every day. I start off the day with Kukicha Twig tea. I then consume fruit, such as bananas and melons. I've a number of snacks of walnuts, almonds and more fruit. For lunch I consume a salad, created with grilled chicken breasts, or tofu, mixed with hearts of latine, tossed using a light lemon and chive vinaigrette.</p>


<p> Don't forget, that the reason this condition exists, inside the very first location, is as a result of a damaged esophagus. Let the esophagus heal by not consuming exactly the same meals within the identical way, that brought on the damage. Loosing excess weight, working out, chewing correctly, drinking adequate quantities of drinking water and elevating the head at night, are all basic issues that anyone can perform to boost this situation.</p>


<div class="img"><img title=" Karen Millen Sculptural Frill Short Dress Black " alt="Karen Millen Sculptural Frill Short Dress Black" src="http://www.karen-millen-km.co.uk/bmz_cache/c/c9712116470a1751f65c3162fe32eae7.image.220x342.jpg" width="220" height="342" /></div>

<div class="prod-info">Karen Millen Sculptural Frill Short Dress Black

<div class="text">Description:* Stretch satin one shoulder pencil dress with i...</div>

</div>


<p>I was determined to defeat this illness! My doctor couldn't support me -no a single could inform me what to complete. I had to cure myself. I became totally dedicated to finding the answers. I analyzed and researched. I wanted to find naturopathic treatments as opposed to drugs, so I searched higher and low for organic treatments that may actually remedy acid reflux. I tried each and every achievable treatment that exists, short of voodoo.</p>

<p>welcome to en-millen-km.co.uk/">www.keren-millen-km.co.uk/</p>

<p> You'll find also a lot of all-natural components located in almost any well being shop, which an help throughout the acid reflux recovery time period. Herbs, for example slippery elm and marshmallow have wonderful therapeutic qualities. Aloe vera juice, licorice and all-natural honey can sooth the esophagus and help within the therapeutic procedure.</p>


<p>Have you ever at any time wondered what genuinely triggers digestive problems Did you realize <span style="color: #ff6600">Karen Millen Dresses</span> that most digestive disorders are not diseases whatsoever, but circumstances which can be completely remedied by making use of all-natural tactics and remedies.</p>


<p> I found that the solutions for the acid reflux difficulty are genuinely really basic. With a couple of modifications in way of life and with the aid of a number of Karen Millen Dresses organic treatments, a single can definitely conquer the acid reflux syndrome, with out using medication.</p>


<p> I cannot tell you how much I suffered. My esophagus was continuously burning and l feared that I would permanently damage myself. I couldn't sleep at night. I had been fearful to eat or consume something but water! I used to be afraid to go out to supper. I became a recluse. I started to loose my friends. They didn't comprehend my problem. Pals would contact and say, "Let's go out for Mexican, barbeque or pizza". Obviously, I could not. I liked to consume hot chili sprinkled with raw onions and jack cheese, but it was now my worst nightmare. My lifestyle had grow to be unbearable!</p>


<p> I have identified that it pays to help keep the meals which you consume as alkaline as feasible. I have <span style="color: #ff6600">Karen Millen Dresses sale</span> created some great recipes, including pasta primavera, baked chicken breasts on mushroom caps and sauteed white fish on mashed potatoes with green peas. You undoubtedly do not have to starve oneself, but I recommend that you simply stay away from individuals acid reflux disorder "trigger" foods, until you have healed your irritated esophagus.</p>


<p> So be sure to do not vacation resort to people "colored pills". Certainly, they are convenient, but potentially dangerous, within the extended operate. Consider command of your own well being and use natural <span style="color: #ff6600">Karen Millen Dress</span>
resources to cure acid reflux.</p>


<div class="img"><img title=" Karen Millen One Shoulder Dress Purple " alt="Karen Millen One Shoulder Dress Purple" src="http://www.karen-millen-km.co.uk/bmz_cache/b/be346cf43fb1edec5dd341b3808095af.image.220x342.jpg" width="220" height="342" /></div>

<div class="prod-info">Karen Millen One Shoulder Dress Purple

<div class="text">Description:* Polyester and silk* Dry clean only Buy Karen M...</div>

</div>


<p> </p>

<p> By consuming supper at least 3 hours before retiring, one can significantly decrease the odds of acid reflux. Chewing gum between foods truly minimizes belly acid, and in the event you actually have to have that pasta with tomato sauce, there's usually baking soda as a final vacation resort.</p>

Related articles:

 
Back
Top