kor 5 Posted March 8, 2011 (edited) 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 spreadhseetsexample.txt Edited March 8, 2011 by kor Share this post Link to post Share on other sites
water 2,387 Posted March 8, 2011 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 My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - WikiOutlookEX (NEW 2020-12-15 - Version 1.6.3.1) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2020-06-27 - Version 1.3.2.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsPowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & SupportExcel - Example Scripts - WikiWord - WikiTask Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - WikiTutorials:ADO - Wiki, WebDriver - Wiki Share this post Link to post Share on other sites
kor 5 Posted March 8, 2011 we've looked at the transpose and it will not do what we need it to do. It garbles the data. Share this post Link to post Share on other sites
kylomas 416 Posted March 8, 2011 (edited) 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 ÿ ÿ ÿ ÿ ÿ ÿ ÿ ÿ ÿ ÿ ÿ ÿ ÿ ÿ ÿ ÿ ÿ ý ý ý ý ý ý kylomasEdit: Nevermind, I see that you want us to download the TXT file and open it in Excel...doh! Edited March 8, 2011 by kylomas Forum Rules Procedure for posting code "I like pigs. Dogs look up to us. Cats look down on us. Pigs treat us as equals." - Sir Winston Churchill Share this post Link to post Share on other sites
JoHanatCent 13 Posted March 9, 2011 (edited) 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 Share this post Link to post Share on other sites
water 2,387 Posted March 9, 2011 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) My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - WikiOutlookEX (NEW 2020-12-15 - Version 1.6.3.1) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2020-06-27 - Version 1.3.2.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsPowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & SupportExcel - Example Scripts - WikiWord - WikiTask Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - WikiTutorials:ADO - Wiki, WebDriver - Wiki Share this post Link to post Share on other sites