Previous Topic Next Topic

Extracting data from specified columns

Solved/Closed
shuvit - Mar 19, 2010 at 02:27 AM
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Mar 19, 2010 at 06:12 PM
I have some columns of data looking somewhat like this:

data1...data2...data3
val.........val........val
val.........val........val
val.........val........val
val.........val........val

(data1-3 are just names of the data in the columns)
I want to convert this more or less automatically to a .csv-file modified along these lines:

(data3)(data1)(data2)
val........-val.......val
val........-val.......val
val........-val.......val
val........-val.......val

Column-headers put inside () because they're not supposed to be showing in the .csv-file. The order of data1-3 is random in my example, I need to be able to use a different order. I know how to format the cells to change the values into negatives.

I would expect it to be something like a formula retrieving data from a named column in a file; the cells in the last column would look something like '=[path/sourcefile.xls]sheetname!column-values:"data2"'. This might be way off and not even possible in excel, and if it isn't I'd like to know.

Also the sourcefile will contain columns thats not going to be used in the csv-file. The process therefore needs to identify the specified columns in a specified file, copy and rearrange them and then pasting them into the .csv.

Any ideas on how to do this?

1 reply

Answer 1 / 1
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
Mar 19, 2010 at 06:12 PM
The code below presumes
1. The data is in sheet called Sheet1
2. A temporary sheet names myTempSheet can be created
3. The CSV file name to be saved in tempCSV.csv
4. No blank column is requried


To Use
After pasting the code,execute the macro. It will ask you how you want to have the column appear in the CSV. So if the first column you want is D, the correct answer is 4. Once you are done with informing the how columns would appear, just press ok without any value.

Code: 
Sub createFile()

Dim numOfCol As Integer
Dim colNum As Integer
Dim myTempSheet As String
Dim dataSheet As String
Dim colValue As Variant
Dim ColIndex() As Integer
Dim cvsName As String

    ThisWorkbook.Save
    
    myTempSheet = "myTempSeet"
    dataSheet = "Sheet1"
    cvsName = "tempCSV"
    
    Sheets(dataSheet).Select

    numOfCol = Cells(1, Columns.Count).End(xlToLeft).Column
    
    ReDim ColIndex(numOfCol)
    For colNum = 1 To numOfCol
        
        colValue = InputBox("What column should be in position " & colNum & " in the CVS file", "Col Position")
        If colValue = "" Then
            Exit For
        Else
            ColIndex(colNum) = colValue
        End If
       
    Next
    
    On Error Resume Next
    
    Sheets(myTempSheet).Delete
    
    On Error GoTo 0
    
    
    Sheets.Add
    ActiveSheet.Name = myTempSheet

    For colNum = 1 To numOfCol
    
        If ColIndex(colNum) > 0 Then
        
            Sheets(dataSheet).Select
            Columns(ColIndex(colNum)).Select
            Selection.Copy
            
            Sheets(myTempSheet).Select
            Columns(colNum).Select
            ActiveSheet.PasteSpecial xlValue
            
        Else
         Exit For
        
        End If

    Next
    
    Sheets(myTempSheet).Select
    numOfCol = Cells(1, Columns.Count).End(xlToLeft).Column
    
    For colNum = 1 To numOfCol
        
        If Cells(1, colNum) <> "" Then
            Cells(1, colNum) = "(" & Cells(1, colNum) & ")        "
        End If
    Next
    
    ActiveWorkbook.SaveAs _
        Filename:=cvsName & ".csv", _
        FileFormat:=xlCSV, _
        CreateBackup:=True
End Sub
1