Trouble with CSV file - data automation

**PowerExcel** · 10-14-2009, 01:50 PM

Hi there,

I am new on this forum, and I hope I could find the help I need on my excel issue.

Here is the background:

I need to create a CSV file where all data have to be displayed in 4 columns: A=Product / B=Geography / C=Account / D=Data.
My source file has 3 different dimensions, 2 dimensions are already displayed in columns (Product / Account) and 1 dimension is displayed in a row (Geography).

My question is this one: how can I tell Excel to create a CSV file when I have dimensions and data differently displayed in my source file? Is there a mean that I can use to simplify the creation of a CSV, by doing some formulas/macros?

Actually, the only solution I have found is the useof an Offset formula for the column creation in the CSV and the use of an Array formula (Sumproduct) for the data input in the CSV. However, when I used my methodology, I came up with a huge file and the number of rows in the CSV is up 65K. Would you know a good solution here to reduce the amount of rows, and to automatise the calculation process?

Please note that I am not at all familiar with Macros - but I am wondering if the use of Macros would be a good option here...?

See below an example of how the data are displayed in my different files (Source File and CSV file)

database example.jpg

I use MS Excel 2007, ASAP utilities on Windows XP

Any help would be highly appriciated!

Many thanks,

PowerExcel

**Cheeky Charlie** · 10-14-2009, 02:58 PM

Are you needing to do this regularly, or once only?

**PowerExcel** · 10-14-2009, 03:38 PM

I would need to do it on regular basis, at least once per month...

In fact, my real need is to create a Database Table from a Summary Table: The opposite operation of the regular Excel's pivot table feature.

I think I have found a beginning of answer over the web.

http://spreadsheetpage.com/index.php...summary_table/

Just need now to understand if I can modify the below code on VBA where I could get in final a database file with more than 3 columns...would you know?

Code:

Sub ReversePivotTable()
' Before running this, make sure you have a summary table with column headers.
' The output table will have three columns.
Dim SummaryTable As Range, OutputRange As Range
Dim OutRow As Long
Dim r As Long, c As Long

On Error Resume Next
Set SummaryTable = ActiveCell.CurrentRegion
If SummaryTable.Count = 1 Or SummaryTable.Rows.Count < 3 Then
MsgBox "Select a cell within the summary table.", vbCritical
Exit Sub
End If
SummaryTable.Select
Set OutputRange = Application.InputBox(prompt:="Select a cell for the 3-column output", Type:=8)
' Convert the range
OutRow = 2
Application.ScreenUpdating = False
OutputRange.Range("A1:C3") = Array("Column1", "Column2", "Column3")
For r = 2 To SummaryTable.Rows.Count
For c = 2 To SummaryTable.Columns.Count
OutputRange.Cells(OutRow, 1) = SummaryTable.Cells(r, 1)
OutputRange.Cells(OutRow, 2) = SummaryTable.Cells(1, c)
OutputRange.Cells(OutRow, 3) = SummaryTable.Cells(r, c)
OutputRange.Cells(OutRow, 3).NumberFormat = SummaryTable.Cells(r, c).NumberFormat
OutRow = OutRow + 1
Next c
Next r
End Sub

**Cheeky Charlie** · 10-14-2009, 04:52 PM

Well... please use code tags (edit - go advanced - select code - hit # button)

After that, I'm a big fan of using database tools (Access?) for database operations - Excel is designed for pivot tables etc. - i.e. what you have already said is the opposite of what you're trying to do.

If your data is literally as you describe (two columns to one) I could write a quick macro, but I don't have XL07 and am wary of create such enormous row counts. Caveats made,if you confirm your data is laid out exactly as per first post, I will write a test script and you can try it out.

CC

**PowerExcel** · 10-14-2009, 05:31 PM

In fact in my 1st post, I haven't shown the database correctly. See now the image I ve just uploaded in the 1st post.

Have just corrected the code format in the 2nd post

**Mikeopolo** · 10-15-2009, 03:56 AM

Try the attached workbook, which has a formula solution.

Regards
Mike

**Cheeky Charlie** · 10-15-2009, 07:15 AM

130k indirect formulae (volatile)... compounded with ifs?

Revised formulae below, but recommend macro beneath:

Revised formulae for formula-solution, not recommended:
F2=INDEX(A:A,ROW(4:4)/2)
F3=INDEX(B:B,ROW(4:4)/2)
H2=INDEX(C$1:D$1,MOD(ROW(2:2),2)+1)
H3=INDEX(C:D,ROW(4:4)/2,MOD(ROW(2:2),2)+1)

Code:

Sub unpivot()
' Insert column
Range("C:C").Insert
' Copy first description
Range(Range("C2"), Range("B2").End(xlDown)(1, 2)) = Range("D1")
' Copy row fields
Range(Range("A2"), Range("B2").End(xlDown)).Copy Range("A2").End(xlDown)(2)
' Copy second description
Range(Range("C2").End(xlDown)(2), Range("B2").End(xlDown)(1, 2)) = Range("E1")
' Copy second data
Range(Range("E2"), Range("E2").End(xlDown)).Cut Range("D2").End(xlDown)(2)
' Tidy
Range("D1:E1").ClearContents
End Sub

CC

**Mikeopolo** · 10-15-2009, 04:48 PM

Morning CC

Thanks for the formula improvements- I appreciate that using indirect(...) a lot is not best practice, but it worked, a least on a small scale

. I like your use of Index to pick up the data headers.

Regards
Mike

**Cheeky Charlie** · 10-15-2009, 05:10 PM

Cheers,
You did the logic, I just tidied it up

One of the nifty things about index is it automatically INT()s the row/column refs (as I understand it, it only accepts the arguments as integers, so they're automatically truncated)

CC