John 3:30

He Must Increase, I Must Decrease

Just me

Delete duplicates in Excel

by

If this article doesn’t help you, consider dropping me a note with your concern. I have thousands of lines of code for hundreds of Oracle problems I’ve faced and would publish ones that people told me would be helpful. I have written hundreds of macros for Excel to solve basic to even quite complex tasks.

Once in a while I want to write an article on technology. Here is the first one.

Note: In Excel 2010 – this feature is built in and much more sophisticated than this script.

Have you ever had a large dataset (or small) that you imported into Excel somehow and you just want to remove all the rows that duplicate on a certain column? Here’s a couple quick macros you can use to solve your problem. Just copy and paste this code into a module in Excel VBA and run them like any other macro.

This first macro is really good if you just have a single column of data. Your focus (the currently selected cell) must be in the column of your data, and your first row of data should be in row 1. Additionally, you should not have any blanks in the dataset. If you do, just sort the data first and run this macro. It can remove a few hundred duplicates in seconds. If you are in the thousands…be patient, it can run a little while. I just ran it against a dataset with 10000 rows with only 386 unique values and it finished in just over 3 minutes. This doesn’t sound like long, but if you’re sitting there while excel is flashing, you may be tempted to end it early.

Sub DeleteDups()

    Application.CutCopyMode = False
    Dim offset, firstRow, lastRow, currCol, i As Integer
    currCol = ActiveCell.Column
    firstRow = 1
    Cells(firstRow, currCol).Select
    Selection.End(xlDown).Select
    lastRow = ActiveCell.Row
    
    Cells(firstRow, currCol).Select
    Range(Selection, Selection.End(xlDown)).Select
        
    Selection.sort Key1:=Cells(ActiveCell.Row, ActiveCell.Column), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    
    offset = 0
    For i = firstRow To (lastRow - 1) Step 1
      If Cells(i - offset, currCol) = Cells(i + 1 - offset, currCol) Then
        Rows(i - offset).Delete Shift:=xlUp
        offset = offset + 1
      End If
    Next i
End Sub

The second macro is good for any situation where you have multiple columns of data. You must first SORT the data on the field you are going to remove the duplicates based upon. Your focus (the currently selected cell) must be in the column of your data we are checking for duplicates as well, and your first row of data should be in row 1. It can remove a few hundred duplicates in seconds. If you are in the thousands…be patient, it can run a little while.

Sub DeleteDupsNoSort()

    Application.CutCopyMode = False
    Dim offset, firstRow, lastRow, currCol, i As Integer
    currCol = ActiveCell.Column
    firstRow = 1
    Cells(firstRow, currCol).Select
    Selection.End(xlDown).Select
    lastRow = ActiveCell.Row
    
    Cells(firstRow, currCol).Select
    Range(Selection, Selection.End(xlDown)).Select
        
    offset = 0
    For i = firstRow To (lastRow - 1) Step 1
      If Cells(i - offset, currCol) = Cells(i + 1 - offset, currCol) Then
        Rows(i - offset).Delete Shift:=xlUp
        offset = offset + 1
      End If
    Next i
End Sub

As always, SAVE your work before running this macro. You may even make a copy of your data first, just in case. If I had a penny for every iteration of an infinite loop I’ve written…

If anyone has any other programming needs, especially little things you might just want to be able to do at home, like track a budget, let me know. I’ll post more or just help you one on one.

If this article doesn’t help you, consider dropping me a note with your concern. I have thousands of lines of code for hundreds of Oracle problems I’ve faced and would publish ones that people told me would be helpful. I have written hundreds of macros for Excel to solve basic to even quite complex tasks.

Is the Church part of the problem today?

by

Let’s assume there is a problem today. The problem is that not a lot of people seem to be getting saved. Can you play along? This article is for Christians only. I want brothers and sisters to consider what I am going to say because I believe it is important. Just thinking about it brings conviction to my soul. To be more specific, it brings me shame and distress. Thankfully, I don’t stay there long as I lay my sin at the foot of the cross and trust that Christ has wiped them away completely, but once in while He gives me an all-too-clear glimpse of my truly disgraceful sin nature and tendency to succumb to the flesh.

The sin I am going to talk about is the worst sin possible. It is the sin of unbelief. In fact, it is the only unforgivable sin, if committed fully against the Son of God, Jesus Christ. But for those of us who have had hearts regenerated by His Holy, Infinite Grace, unbelief still creeps in. Often it occurs in subtle and deceitful ways. In my case, and I believe in many others, it can be spawned by a misapplication of scripture. Not a misinterpretation or misunderstanding in this case, but a mis-applying of the often well-understood, clearly believed Truths.

For now, I need to establish some assumptions I will trust axiomatically for purposes of this post. These are not un-provable, but I’m going to choose not to prove them now. Additionally, if you are of a different ‘camp’ in this often disputed eschatological argument, I’d encourage you to judge the content of the article NOT based on whether you agree with my view of end times, but rather, whether the statements I make do, in fact, logically follow from my assumptions, and whether the application I’ll try to encourage myself and others to employ as the result is actually biblical.

Ok. Long story short. “Too late,” you say? Sorry. 🙂 Let’s begin here. I’m what I believe is commonly called a pretribulational, premillenialist. (I think I got that right) What this means is that I believe that the next event that is to occur, prophetically speaking, is what is called the rapture of the church. I trust it is imminent and that nothing is ‘holding that back’ prophetically that still needs to occur; that is, it could happen at any time. It also means that I believe there will be 7 years of tribulation on the Earth after that event, followed by a literal 1000 year Earthly reign of Christ on Earth. As I said these are oft disputed items that are not to be proved in this article.

Now, what I am going to say is the result of this may also be shared by people of other end-times views. People who share the same views as I do may have different understandings than what I’m about to share as well. I’m just one man, and I may or may not represent the whole of my ‘camp.’ In other words, don’t throw out a baby because I’m in the bathwater!

So what? So I believe this rapture is imminent and the millennial reign is future? Why does that matter? Well, here is why. I believe that the sin cursed world we live in is only getting worse. The degradation of EVERYTHING is continuing without regression. If it had velocity, I’d tell you it was accelerating. At the least, it is not slowing down. The bible declares that scoffers will come in the last days (2 Peter 3:3). It is also clear that not a lot of people will be saved! (Matthew 7:13-14) So what’s my problem? Well, I think that at times, I take these verses and biblical ideas too far! I am shocked when someone wants to talk about Christ. If I hear that God has graciously saved someone, I am sometimes very skeptical! Reminds me of a strange story. The other day I saw I license plate that said, “CYNIC.” I glanced over at the guy and said, “I don’t believe you are a cynic.” LOL, eh?

Anyway, the point is that I think part of the problem is that my focus is NOT on the Power and Glory and Grace of God! It’s like I’ve lost hope and faith that God is really going to work in the hearts and lives of people. Instead of preaching or witnessing, EXPECTING God to perform a miracle, I sometimes realize that I actually EXPECT THE OPPOSITE! I may be doing the right thing, actually in true loving, obedience to my Savior and Lord. But I am not being really faithful that He CAN and WILL save! I chalk it up to, “Well, it’s the end times. We’re just going to get scoffers and mockers and others who will not come to saving faith.”

I’m ashamed. Biblically, before 100 A.D. it was considered the end-times as well. Thank God the men and women of the faith in those times didn’t give up hope, even in the face of GREATER PERSECUTION than I’ve ever seen. Thank God early 20th century preachers didn’t give up just because they could see the end coming. We trust that the Lord could come any day…but do we ever think that maybe He could come 500 years from now? Maybe we are not even getting a glimpse of what “end-times” Christians will experience because the things we lament today will be SO MUCH WORSE years from now, maybe after we’ve gone to be with the Lord. My fear is that our attitude of unbelief is actually contributing to the problem. My sin has consequences in this world still, even if Jesus has paid for them. My sin still hurts people, and myself, as well as being an offense to my perfectly loving and graceful Savior.

Is it too late? It is too late to pray like we believe that God might bring revival to the USA? Many Christians, including me are sure God has taken His protective hand off of us. That the changes we’ve seen in our government lately is an indicator that we’ve already been left to our own sinful desires. EVEN IF THIS IS TRUE, I ASK, DOES THAT MEAN WE OUGHT NOT BELIEVE GOD CAN AND WILL RESTORE US? Isn’t that Israel’s basic problem? Wasn’t God, in the OT, for example, always ready to restore them to Himself? He was waiting for them all the time, inasmuch as you can ascribe the human quality of waiting to our Lord. Let’s just say he was patient, we He not? And how many times did He pick them back up after they fell? And He is going to do it again still! Pray to that end for the USA! Pray for your friends and family, but do it like you believe it. Not because your positive attitude will affect the effectiveness of your prayer, but because it is a display of our absolute TRUST in the Goodness and Faithfulness of the God of creation! It is an exhibition of His Glory when we pray for things that seem impossible!

To ask Him to protect you on your way home from work is perfectly ok, and I trust He does that for any of us that makes it home safely. But really, do you pray that prayer with urgency? And when you do, do you find it improbable in your mind that you weren’t going to make it home anyway? God doesn’t need a head start or a lead! When we pray to God, we can pray for the most unlikely thing, and if it is in His will, it will be done. So be bold! Ask God to save your mom or dad or your brother or sister. Ask Him to regenerate the heart of Barack Obama, because He can, and because you’ve had a heart change yourself, by which you are actually concerned for where our President will spend eternity, not because he annoys you. Do it because you deserve Hell as much as the President, and you will not be going there by the grace of God through faith in Christ alone.

So have you given up hope? Are you like me, a sinner in need of a Savior, not only from the punishment of your sin, but from the bondage of sin? The chains that keep me locked up in the mindset that if a camel cannot go through the eye of a needle in my mind, then it is not happening. With God, all things are possible! Luke 18:27

Dear Christian reader: do not despair, but rather rejoice in the Glory of our Lord Jesus Christ! Trust His Sovereign Grace to penetrate the hearts of sinners. And although you know that there are few that will find it, pray to Him knowing He can save more than we may think “a few” is. Believe on the Lord Jesus Christ!! Truly trust Him, in Him and Him alone. Stop being a part of the problem. Stop looking at every scoffer and mocker as “just an enemy of the gospel,” and start seeing them as sinners, like yourself, who desperately need a savior. And chances are they need a friend like you to introduce them by sharing with them what Christ did for them and why. If you really believe the rapture is coming, you should spend any moment you can sharing the gospel with an unsaved person! Do not misapply the scripture; Christ’s imminent return is cause to “be busy,” (Matthew 24:42,44-46) not be idle. It is a reason to have hope, not give up. As Tony says, “Don’t Quit!”

A Tribute to my Bride

by

What can I say? I owe you! Erin, I love you!

Being your husband means so much to me. I can’t do what I do without you!

I go to work, and I don’t worry about my kids. I don’t worry about my house being clean. I don’t worry about my meals being cooked. I don’t worry about the groceries. I don’t worry about our finances, or you running around on me, or leaving me. I focus on work, and can be successful. (Prov 12:4, Prov 31:11-12)

I stay up late reading the bible and writing. I go out late to evangelize. I coach three kids sports…year round. You clean all our clothes, you have them ready and laid out and ironed for us. Snacks packed, bookbags ready for school, lunch packed for work, shirt and tie picked out. You are my treasure, you are my sanctuary in this world that Christ gave me to love and honor and serve. I do not do those things enough for His sake, nor for all you do for me.

You sacrifice what most other women do not. You do not get to go on as many dates or wear as nice stuff. You choose a humble life and attire because of my goals for ministry. You do not get to spend as much time with me since I go out to witness and preach, and you stay home, taking care of my children. You’ve shared your child with me, and I am eternally grateful. I love you so much.

You are a prize, a crown. Your reward in Heaven will be greater than most, because you are putting up with more than many will have to in this world. You did not get a good man, you got a work-in-progress, and I thank you for the patience you’ve shown. I am so grateful for your love, and I love you now and forever. I would marry you again today in a heartbeat, and I’d take every minute we’ve been together and redo them all over again, for better or for worse.

God bless you, Erin. My bride, to whom I still owe a decent honeymoon, I won’t forget, sweetie.

Michael

Getting a little personal…

by

Listening to KLOVE: Holy is the Lord – Chris Tomlin

Alright, I’ll admit it.  I haven’t exactly made this blog very personal.  I thought I’d use it to write “theological” essays periodically, but more often it would be an online journal.  I guess I haven’t felt much need to do that as of yet.

I used to journal a lot, but time doesn’t permit me anymore…you can see that it is now almost 1AM, so ask me if this was a good idea tomorrow, (errr today?)

Anyway, I find great joy in reading Abbie‘s and Erin‘s blog posts.  And I don’t want to deprive my readers of getting a glimpse of me from a different perspective than just the “guy-always-talking-about-the-bible” perspective you all must have about me already.  That being said, I have two more Sunday school teachings to upload, but I want to adjust the audio slightly.

So what am I doing up so late?  I suppose I’m celebrating.  God is Good, and all occasions are a reason to celebrate His Glory.   There I go again! I won’t apologize…I just can’t get enough of Him.  But as a matter of fact, God’s grace is the reason to celebrate.  Let me give you some background.  If you are bored easily…well if you are bored easily, you aren’t still reading I’d suppose.

Thursday was an exciting day; I took 2 of my kids to a pizza challenge.  This worked out nicely because one of my kids misbehaved and had to stay home.  So I had my big ones with me and no wife to hold me back from eating “all you can eat pizza.”  She knows my tendencies and tried to warn me, but I heeded not! We had a good time, we all ate a little too much pizza, me maybe more than a little too much.  But we made it home safely and got to bed.  I had a full day of work ahead of me, and a busy Saturday and Sunday to look ahead to.

Then I couldn’t sleep around midnight that night.  Without getting graphic, I became VERY ILL.  Now I felt terrible.  I knew I should have listened.  I felt like I was being punished for gluttony.  I felt like I was suffering for not listening to my wife’s loving plea for me to take care of myself.  I spent hours praying, a rarity for most people probably, including me.

I am blessed with an extremely compassionate wife, so I was able to stay in bed all morning and was tended to.  I called into work (which I’m not a fan of since I am hourly), and spent the day on the couch.  By now, I’ve committed myself to better discipline.  I am convinced I had food poisoning at this point.  Later I thought it might have been a flu.  Who knows.  Either way, my body was utterly useless.  I found 9 minutes later that day to sit at the computer and check messages before I got tired.  My lovely wife cooked some AMAZING smelling food that day that just nauseated me due to my condition.  She’s an affectionate woman and I continuously pushed her away and hurt her feelings because I felt so ill I didn’t want to be touched most of the day.  A day of work was trashed, a day with my wife was gone and my evening with my children was fading quickly.  I don’t remember much more…I think I slept most of it.  Although, my son made me a nice card I saw this morning… 🙂

If that was the end of the story, I suppose some would feel for me.  But then Saturday morning there was a huge mixup at my daughter’s soccer which I am the head coach for.  I had a conflict with my other children’s flag football team which I also coach.  It sounds like I really disappointed a lot of people that day.  I already felt terrible, and then that.  I figured I could get through it.  I do believe that God allows things to happen.  In the meantime, I’m headed to the OSU spring game to pass out tracts and, hopefully, preach and witness.  All we ended up doing was passing out tracts, but it was worth it because all 3 of my kids came.  That’s ok, I need to get home anyway to prepare for Sunday School and work on the church website which I was falling behind on!

Then Sunday morning comes, I’m still exhausted.  I’m finally eating normally.  I started my discipline already and was doing ok with it…good news.  Then I stand up at church in the morning service to encourage my church family for their commitment to prayer.  I made a quick reference to the fact that if they didn’t pray on Saturday according to their commitment that they should consider why they missed this important date.  SOUNDS GREAT NOW AND BEFORE, BUT WHEN I ACTUALLY SAID IT, IT SOUNDED LIKE I WAS ASKING PEOPLE TO WRITE DOWN A CONFESSION AS TO WHY THEY DIDN’T PRAY.  I didn’t realize this either.  NOW I FEEL REALLY STUPID.

I feel terrible.  I don’t have any idea how many people I hurt or to what extent.  But here is the good part: TONIGHT I CAN CELEBRATE.  Because I am saved by grace through faith alone.  With Jesus Christ there is always hope.  Years ago…maybe months ago…maybe weeks ago…I don’t know because this all happened this weekend, I would have been JUMPING to my own defense.  I would have really fretted over all this.  I do regret my mistakes, misspeaking, gluttony and poor planning, but not in my formerly self-pitying way.  I simply want to see reconciliation in all this.  I have already apologized to the soccer team via an email, and I’m mentally committed to making sure nothing like that happens again, as much as that’s in my power.  And I’m willing to do whatever I need to do to make things right at church.  I just desire forgiveness from the offended.  I do want them to know that I never intended what it sounded like, but does it really matter in the long run?  I mean, it might help some, but ultimately, if someone really thought I said what they thought I said, couldn’t they still find it in their heart to forgive me?  And if they couldn’t…they may have a bigger problem than I do, so I’m not going to worry about my reputation, but rather, my attitude toward others.

This is reason to celebrate.  A gift of humility.  God has granted me an ability I never had…NEVER WANTED…to set aside my pride and desire to be liked, exalted, though highly of, and has allowed me to identify with Jesus.  Isa 53:7 ESV  He was oppressed, and he was afflicted, yet he opened not his mouth; like a lamb that is led to the slaughter, and like a sheep that before its shearers is silent, so he opened not his mouth.  To me, this verse speaks volumes of humility.  Keeping in mind that Jesus truly was blameless, and had the power at any time to stop the crucifixion, it is an even more exciting display of His Holy Perfection of Humility than we could ever really fathom.  I’ll be happy to tell people I’m sorry and ask for forgiveness and leave the rest up to God….it’s up to Him anyway, isn’t it? By the way, I’m not imagining myself being led to a slaughter, I’m just identifying with the part where he ‘opened not his mouth.’ I am making a choice, by the grace of the Holy Spirit, to not get defensive, which is a big improvement in my life. I’ll enjoy resting in Christ.