Last week, I was working with some data when I encountered the need to merge two staggered columns.
That is to say, I needed to merge two columns where the data was blank in either one column or the other.
.
Here is an example.
I needed to bring placements and pulls into one column.
.
Piece of cake!
I simply copy the data in one column – with all the blanks.
.
Then I place my cursor on the other column, right-click, then activate the Paste Special dialog box.
.
On the Paste Special dialog box, I choose the Skip Blanks option, and then press OK.
.
As you can see, all the data from the Pulls column has been copied to the Placements column without overwriting the data that was originally in Placements.
.
Now, I can reformat the table so that only one column shows.
.
See that – no need for formulas or code. Brilliant!
This may be the only case where the term “merging” should be used in excel.
Brilliant! Love the example.
Another way:
Select column B
Goto (Ctrl+G) | Special | Blanks
Type = and hit right arrow
Type Ctrl+Enter
The “Goto | Special | Blanks” method has other neat applications as well. E.g., if you’ve ever had a table like this http://www.mrexcel.com/articles/fill-pivot-blank-cells.php you can quickly fill in the missing category labels.
If this is something repetitive that has to be repetitively, then perhaps a VB solution would be useful. Here is a macro that should execute fairly quickly...
I think Skip Blank would be a cool name.
I just looked back at my posting and am wondering how that second line of code got in there. Here is the code without it although I did add code to turn off the screen updating at the beginning (and back on at the end) to speed things along a little bit...
Sub FillInTheBlanks()On Error Resume Next
Application.ScreenUpdating = False
Columns("B").SpecialCells(xlBlanks).FormulaR1C1 = "=IF(RC[1]="""","""",RC[1])"
Columns("B").Value = Columns("B").Value
Columns("C").Delete
Application.ScreenUpdating = True
End Sub
Mr. Rothstein,
I didn't know any other way to contact you except to hijack this forum so to those members that are following this thread I apologize. I had a question regarding finding text in a string. Right now I'm using
Function ExactWordInString(Text As String, Word As String) As Boolean
'ExactWordInString = " " & UCase(Text) & " " Like "*[!A-Z]" & UCase(Word) & "[!A-Z]*"
This is good but it only finds the full word. For example if I search for the word "close" it will return the lines that have the word "close" in it but not the lines that have "closed" because it has the letter d on it. how do I adapt my code for it to also include words longer than the searched word that includes the same letters? I would much appreciate your input and again my apologies to those following this thread.
Brian
@Brian,
Where did you see my ExactWordInString function posted at? I would think you could have posted your question there. Anyway, I have some follow-up questions for you and I don't want to run our dialog here in this thread. Please contact me at rickDOTnewsATverizonDOTnet (replace upper case letters with the symbols they spell) and repeat your question in your email message so I will know who you are and why you wrote to me.