Here is a VBA macro that will remove all the newline characters from an Excel file. They look like little squares [] or they add an Enter to a cell. I suggest saving this macro in your PERSONAL.xls file so it is available in all your Excel files. Then just run the macro and it will fix your file. This often happens when transfering data from Unix based systems to Windows based systems. The newline character is sometimes refered by ‘\n‘ or ‘\r‘ or even ‘\n\r‘. The trick is Excel refers to it by ‘vbNewLine‘, thats why your Find & Replace doesn’t work.
Sub RemoveNL()
'
' RemoveNL Macro shared by FrankBaris.com
'
s = vbNewLine
r = " "
Cells.Replace What:=s, Replacement:=r
'
End Sub
Hope this comes in handy!
Finding duplicates in a set of data can be pretty daunting at some points. I thought I’d share some of my knowledge, since I work with large sets of data everyday, and finding duplicates is something I have had to figure out.
First, let me show you how to find duplicate rows in a MySQL table. It’s actually quite easy. Just throw this statement in, replace t with the name of the table your looking in, and col with the field your looking for dupes in.
SELECT col, count(*)
FROM t
GROUP BY col,
HAVING count(*) > 1
Now, let me give you a VBA macro that you can use in Excel to find duplicate data. This macro is a good one to have saved in your PERSONAL.xls file, so that it is available in all your Excel files. You need to first sort the column, then select just the block of data you want to look for dupes in, and then run this macro… it will replace all the dupes (except the first one) with —. Then you can re-sort all your data by that column, and all the duplicate rows will be grouped together where the data was replaced with —.
Sub DeDupe()
'
' DeDupe Macro shared by FrankBaris.com
'
Dim RowNdx As Long
Dim ColNum As Integer
ColNum = Selection(1).Column
For RowNdx = Selection(Selection.Cells.Count).Row To _
Selection(1).Row + 1 Step -1
If Cells(RowNdx, ColNum).Value = Cells(RowNdx - 1, ColNum).Value Then
Cells(RowNdx, ColNum).Value = "----"
End If
Next RowNdx
End Sub
I hope these help some of you out, as I know, trying to figure these out on your own can really hurt ones brain :-0