Sign in to follow this  
Followers 0
kor

help formatting an excel sheet and transforming columns to rows

6 posts in this topic

#1 ·  Posted (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 spreadhseets

example.txt

Edited by kor

Share this post


Link to post
Share on other sites



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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

#4 ·  Posted (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 ÿ ÿ ÿ ÿ ÿ ÿ ÿ ÿ ÿ ÿ ÿ ÿ ÿ ÿ ÿ ÿ ÿ ý ý ý ý ý ý kylomas

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

Edited 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

#5 ·  Posted (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 by JoHanatCent

Share this post


Link to post
Share on other sites

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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0