exporting data from Excel to a load file

It's not so easy to export data from Excel to a load file with a quote qualifier and comma delimiter - the format required by many applications. Saving the file in the .csv format will omit the quotation marks:

. . . it's a pain to accurately add in the missing quotation marks by altering the file in a text editor, and Excel doesn't provide an easy way to add them in the export. Luckily, Microsoft has posted a macro here, which will export data from an Excel spreadsheet in the correct format.

Enter the below code in a module for the worksheet with the data:

Select the data on the worksheet that you want to export to a new file. Run the macro and it will prompt you to enter a path for the new file:

A new file with comma delimiters and quotation mark qualifiers appears:

This is the complete code but it is not formatted correctly so use the code at this link or edit the code before running it.

Sub QuoteCommaExport()

' Dimension all variables.

Dim DestFile As String

Dim FileNum As Integer

Dim ColumnCount As Long

Dim RowCount As Long

' Prompt user for destination file name.

DestFile = InputBox("Enter the destination filename" _

& Chr(10) & "(with complete path):", "Quote-Comma Exporter")

' Obtain next free file handle number.

FileNum = FreeFile()

' Turn error checking off.

On Error Resume Next

' Attempt to open destination file for output.

Open DestFile For Output As #FileNum

' If an error occurs report it and end.

If Err <> 0 Then

MsgBox "Cannot open filename " & DestFile

End

End If

' Turn error checking on.

On Error GoTo 0

' Loop for each row in selection.

For RowCount = 1 To Selection.Rows.Count

' Loop for each column in selection.

For ColumnCount = 1 To Selection.Columns.Count

' Write current cell's text to file with quotation marks.

Print #FileNum, """" & Selection.Cells(RowCount, _

ColumnCount).Text & """";

' Check if cell is in last column.

If ColumnCount = Selection.Columns.Count Then

' If so, then write a blank line.

Print #FileNum,

Else

' Otherwise, write a comma.

Print #FileNum, ",";

End If

' Start next iteration of ColumnCount loop.

Next ColumnCount

' Start next iteration of RowCount loop.

Next RowCount

' Close destination file.

Close #FileNum

End Sub

Creating a random series of numbers in Excel for QC

If you need to generate a list of random numbers in Excel you may want to avoid using the RANDBETWEEN formula. It will return duplicate numbers:

This is not ideal if your aim is to select numbered entries on a spreadsheet to review at random. You can generate a random list of numbers without duplicates by using the RAND formula instead. Enter the RAND formula in a column adjacent to the data set you are QCing.

The RAND formula will generate new numbers each time the spreadsheet is edited. To get a static list of randomly generated numbers copy the results and use the paste values option. Then you can sort the data by the formula results to randomly select entries to review:

Listing multiple hits horizontally with Excel's FILTER function

If you have ever wondered if there is an easy way to modify VLOOKUP to pull more than one result from an array, note that Excel's new FILTER function can be used to easily return multiple hits.

FILTER can search for when a given value matches another value in the array B:B, and then pull the value from A:A.   In this example, on the worksheet we have a list of last names in column B. There are multiple entries for common last names. The full names are give in column A. For select last names listed in column H, we want to list each result in the A:B array in the columns on the right. The aim is to see if any names listed in column G match those in column A inexactly. With the data arranged in columns J, K, L, etc., we can see at a glance if the names are the same but for the addition of a maiden name, middle initial, abbreviated form of a first name, or some other common change. For example, we can see on row 2 that 'Bob Smith' is a match for 'Robert Smith'.

The FILTER formula pulls the results from the listed array (B:B) when a value on the same row in the second listed array (A:A) matches the value in the cell reference given at the end after the equals sign.

=TRANSPOSE(FILTER(A:A,B:B=H2))

FILTER will return all of the results at once. It's not necessary to use CTRL + D to add them to successive cells.

If you precede FILTER with the TRANSPOSE formula the values will be displayed horizontally automatically.