help formatting an excel sheet and transforming columns to rows

[kor]

I've attached the sample worksheet I need to work with.

The first few columns and rows is what our software spits out. The data starting at row 17 is what we need the data to look like. The problem we are having is we aren't sure the logic needed to transform data from columns into rows. We have brainstormed on a white board and think that loops and an array combine might be needed, but aren't sure exactly.

From the example we have an employee with ID # 1356. I've only included 5 columns (there are over 50 for each employee). Each column is a piece of data for that employee with a date. If there is no date then the column is empty for that row. From the example employee 1356 has 3 out of the 4 data sets. Thus that would translate into 3 rows under the new format. Each row would be a data set from each column. We understand that the comments column will get duplicated for each row which we are fine with.

EDIT: you'll need to change the txt to xls. the forum won't let me upload excel spreadhseets

example.txt

Edited March 8, 2011 by kor

[water]

Use the excel transpose function on the range (has to have the same number of columns and rows).

https://office.microsoft.com/en-us/excel-help/transpose-HP005209319.aspx

[kor]

we've looked at the transpose and it will not do what we need it to do. It garbles the data.

[kylomas]

kor,

when I open your attachment I get >>> B °a À = œ ¯ ¼ = hx L;‚#8 X@ " · Ú 1 È ÿ XA r i a l 1 È ÿ XA r i a l 1 È ÿ XA r i a l 1 È ÿ XA r i a l 1 ÿ XA r i a l "$"#,##0_);\("$"#,##0\)! "$"#,##0_);[Red]\("$"#,##0\)" "$"#,##0.00_);\("$"#,##0.00\)' " "$"#,##0.00_);[Red]\("$"#,##0.00\)7 * 2 _("$"* #,##0_);_("$"* \(#,##0\);_("$"* "-"_);_(@_). ) ) _(* #,##0_);_(* \(#,##0\);_(* "-"_);_(@_)? , : _("$"* #,##0.00_);_("$"* \(#,##0.00\);_("$"* "-"??_);_(@_)6 + 1 _(* #,##0.00_);_(* \(#,##0.00\);_(* "-"??_);_(@_)$ ¤ [$-409]dddd\,\ mmmm\ dd\,\ yyyyà õÿ À à õÿ ô À à õÿ ô À à õÿ ô À à õÿ ô À à õÿ ô À à õÿ ô À à õÿ ô À à õÿ ô À à õÿ ô À à õÿ ô À à õÿ ô À à õÿ ô À à õÿ ô À à õÿ ô À à À à + õÿ ø À à ) õÿ ø À à , õÿ ø À à * õÿ ø À à õÿ ø À à À “ €ÿ“ €ÿ“ €ÿ“ €ÿ“ € ÿ“ €ÿ` … € Sheet1… ½ Sheet2… Ä Sheet3Œ Á Á "¾ ü k ID# dtp1 dtp2 dtp3 dtp4 type date comment mexico arizona utah test commentÿ ê $ F c c ˆ ÍÁÀ < \ d ü©ñÒMbP?_ * + ‚ € % ÿ Á ƒ „ ¡ " ÿ à? à?tpU ÿ ÿ ÿ ÿ ÿ ÿ ÿ ÿ ÿ ÿ ÿ ÿ ÿ ÿ ÿ ÿ ÿ ý ý ý ý ý ý kylomas

Edit: Nevermind, I see that you want us to download the TXT file and open it in Excel...doh!

Edited March 8, 2011 by kylomas

[JoHanatCent]

The problem we are having is we aren't sure the logic needed to transform data from columns into rows. We have brainstormed on a white board and think that loops and an array combine might be needed, but aren't sure exactly.

Yes it seems to be the only way.

To get you started look at this?

#include <Excel.au3>
#include <Array.au3>

$file = "e:\Example.xls"
$oExcel = _ExcelBookOpen($file, 1)
If @error = 1 Then
    MsgBox(0, "Error!", "Unable to Create the Excel Object")
    Exit
ElseIf @error = 2 Then
    MsgBox(0, "Error!", "File does not exist - Shame on you!")
    Exit
EndIf
_ExcelSheetActivate($oExcel, "sheet1")
$aArray = _ExcelReadSheetToArray($oExcel)
MsgBox(0, @error, @extended)
_ArrayDisplay($aArray, "Array using Default Parameters")

Edited March 9, 2011 by JoHanatCent

[water]

I think you need something like this:

#include <Excel.au3>
#include <Array.au3>

Global $sInputFile  = "C:\temp\Test.xls"
Global $sOutputFile = "C:\temp\TestOutput.xls"
Global $oExcel = _ExcelBookOpen($sInputFile, 0)
If @error <> 0 Then Exit MsgBox(16, @ScriptDir, "Error reading file '" & $sInputFile & "'")
Global $aArrayInput = _ExcelReadSheetToArray($oExcel)
_ExcelBookClose($sInputFile, 0)
Global $aArrayOutput[10000][4] = [["",4],["ID#","type","date","comment"]]
Global $iIndexOut = 1
For $iIndex1 = 2 To $aArrayInput[0][0]
    For $iIndex2 = 2 To $aArrayInput[0][1]-1
        If $aArrayInput[$iIndex1][$iIndex2] <> "" Then
            $iIndexOut += 1
            $aArrayOutput[$iIndexOut][0] = $aArrayInput[$iIndex1][1]
            $aArrayOutput[$iIndexOut][1] = $aArrayInput[1][$iIndex2]
            $aArrayOutput[$iIndexOut][2] = $aArrayInput[$iIndex1][$iIndex2]
            $aArrayOutput[$iIndexOut][3] = $aArrayInput[$iIndex1][$aArrayInput[0][1]]
        EndIf
    Next
Next
ReDim $aArrayOutput[$iIndexOut+1][4]
$aArrayOutput[0][0] = UBound($aArrayOutput,1)-1
$oExcel = _ExcelBookNew()
_ExcelWriteSheetFromArray($oExcel, $aArrayOutput, 1, 1, 1, 0)