Sign in to follow this  
Followers 0
MaCgyver

Script built for CSV file. How can I build for EXCEL?

20 posts in this topic

#1 ·  Posted (edited)

I made the following for a CSV file. Now I need to make one very similar for an Excel file.

;Select file to calculate

$message = "Select"

$var = FileOpenDialog($message, "C:\", "(*.csv)", 1 )

$file1 = FileOpen($var, 0)

$file2 = FileOpen('C:\au3\The Average.csv',2)

; Check if file opened for reading OK

If $file1 = -1 Then

MsgBox(0, "Error", "Unable to READ file.")

Exit

EndIf

FileClose($file1)

FileClose($file2)

Global $A_Percent = 0, $A_Count = 0

Global $B_Percent = 0, $B_Count = 0

$file1 = FileOpen($var, 0)

$file2 = FileOpen('C:\au3\The Average.csv',1)

; Read in lines of text until the EOF is reached

While 1

$line = FileReadLine($file1)

If @error = -1 Then ExitLoop

$data = StringSplit($line, ",")

If $data[1]= "A" AND $data[7] >= "0" Then

$A_Percent = $A_Percent + $data[7]

$A_Count = $A_Count + 1

FileWriteLine($file2,$line)

$APercentage = $A_Percent / $A_Count

EndIf

Wend

FileWriteLine($file2,$A_Count)

FileWriteLine($file2,$A_Percent)

FileWriteLine($file2,$APercentage)

FileClose($file1)

FileClose($file2)

$file1 = FileOpen($var, 0)

$file2 = FileOpen('C:\au3\The Average.csv',1)

; Read in lines of text until the EOF is reached

While 1

$line = FileReadLine($file1)

If @error = -1 Then ExitLoop

$data = StringSplit($line, ",")

If $data[1] = "B" AND $data[7] >= "0" Then

$B_Percent = $B_Percent + $data[7]

$B_Count = $B_Count + 1

FileWriteLine($file2,$line)

$BPercentage = $B_Percent / $B_Count

EndIf

Wend

FileWriteLine($file2,$B_Count)

FileWriteLine($file2,$B_Percent)

FileWriteLine($file2,$BPercentage)

FileClose($file1)

FileClose($file2)

Edited by MaCgyver

Share this post


Link to post
Share on other sites



HI,

I haven't looked closer to your script, but maybe the excel udf in Scripts & Scraps helps out.

Have a look!

So long,

Mega


Scripts & functions Organize Includes Let Scite organize the include files

Yahtzee The game "Yahtzee" (Kniffel, DiceLion)

LoginWrapper Secure scripts by adding a query (authentication)

_RunOnlyOnThis UDF Make sure that a script can only be executed on ... (Windows / HD / ...)

Internet-Café Server/Client Application Open CD, Start Browser, Lock remote client, etc.

MultipleFuncsWithOneHotkey Start different funcs by hitting one hotkey different times

Share this post


Link to post
Share on other sites

HI,

I haven't looked closer to your script, but maybe the excel udf in Scripts & Scraps helps out.

Have a look!

So long,

Mega

Ok I will....I was running into different problems but I got the major one solved.

This is basically the part I would like to see code for excel.

While 1

$line = FileReadLine($file1)

If @error = -1 Then ExitLoop

$data = StringSplit($line, ",")

If $data[1]= "A" AND $data[7] >= "0" Then

$A_Percent = $A_Percent + $data[7]

$A_Count = $A_Count + 1

FileWriteLine($file2,$line)

$APercentage = $A_Percent / $A_Count

EndIf

Wend

FileWriteLine($file2,$A_Count)

FileWriteLine($file2,$A_Percent)

FileWriteLine($file2,$APercentage)

Share this post


Link to post
Share on other sites

Hi,

something like this?

#include"ExcelCom.au3"
#include "Array2D.au3"
#include "_ArrayView2D1D.au3" ;
$FilePath2=@ScriptDir&"\Blank6.xls"
$FilePath1=@ScriptDir&"\Blank5.xls"
;~ FileDelete($FilePath1)
;~ FileDelete($FilePath2)
_XLCreateBlank($FilePath1)
_XLCreateBlank($FilePath2)
$DataString="A,7,A,9,23,45,A,17,18,9"&@CRLF&"3,12,7,6,9,23,45,3,17,18"&@CRLF&"11,12,8,6,9,23,45,3,17,18"&@CRLF&"3,12,9,6,9,23,45,3,17,18"
$DataString&=@crlf&$DataString
$DataString=StringReplace($DataString,",",@TAB)
$XLRange=_XLpaste($FilePath1,1,"A",1,$DataString,0)
;================================================
;START HERE WITH FILES KNOWN
;================================================
;~ $FilePath2=@ScriptDir&"\Blank6.xls"; input
;~ $FilePath1=@ScriptDir&"\The Average.csv";'C:\au3\The Average.csv'
local $XLArray2[1][1]
Global $A_Percent = 0, $A_Count = 0
Global $B_Percent = 0, $B_Count = 0
$XLArray1=_XLArrayRead($FilePath1,1,"usedrange")
_XLClose($FilePath1,1)
;=========================================
for $i=0 to UBound($XLArray1)-1
        $Array2DTo1String=_Array2DTo1String( $XLArray1, $i,  "Array contents",0 , 1)
    if $XLArray1[$i][0] = "A" AND $XLArray1[$i][6] >= "0" Then
        $A_Percent = $A_Percent + $XLArray1[$i][6]
        $A_Count = $A_Count + 1
        _ArrayInsert2Dst( $XLArray2, $Array2DTo1String)
        $APercentage = $A_Percent / $A_Count
    EndIf
Next
_ArrayInsert2Dst( $XLArray2, $A_Count)
_ArrayInsert2Dst( $XLArray2, $A_Percent)
_ArrayInsert2Dst( $XLArray2, $APercentage)
_XLArrayWrite($XLArray2, $FilePath2,1,1,1,0 ,1,0)
_XLShow($FilePath2,1)
Best, Randall

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

Thanks Randall...I am going to work on this in a few minutes. I just wanted to know why is this line structured this way?

Like why is certain numbers listed and others not?

$DataString="A,7,A,9,23,45,A,17,18,9"&@CRLF&"3,12,7,6,9,23,45,3,17,18"&@CRLF&"11,12,8,6,9,23,45,3,17,18"&@CRLF&"3,12,9,6,9,23,45,3,17,18"

Edited by MaCgyver

Share this post


Link to post
Share on other sites

Hi,

Just made a random file to work with some data...

you wouldn't need this a s you already have your file...

Best, Randall

Looks like I don't have this file "_ArrayView2D1D.au3". Can anyone point me to the link? I did a search with no results. :">

Share this post


Link to post
Share on other sites

Getting a message... " A file named '2.xls' already exists in this location. Do you want to replace it?"

It doesn't matter if I choose yet or no I eventually get this error "Error: Could not open 2 as an Excel Object"Then I get autoit errors in regards to newname.

This happens when I use the following code:

#include"ExcelCom.au3"

#include "Array2D.au3"

;#include "_ArrayView2D1D.au3" ;<-----Do I need? I don't have, so I don't know if this is the problem.

;Select file to calculate

$message = "Select"

$var = FileOpenDialog($message, "C:\", "(*.xls)", 1 )

$FilePath2 = FileOpen($var, 0)

$FilePath1 = FileOpen('C:\au3\The Average.xls',2 + 8 + 16)

FileClose($FilePath1)

FileClose($FilePath2)

local $XLArray2[1][1]

Global $A_Percent = 0, $A_Count = 0

Global $B_Percent = 0, $B_Count = 0

Global $APercentage = $A_Percent / $A_Count

$XLArray1=_XLArrayRead($FilePath1,1,"usedrange")

_XLClose($FilePath1,1)

;=========================================

for $i=0 to UBound($XLArray1)-1

$Array2DTo1String=_Array2DTo1String( $XLArray1, $i, "Array contents",0 , 1)

if $XLArray1[$i][0] = "A" AND $XLArray1[$i][6] >= "0" Then

$A_Percent = $A_Percent + $XLArray1[$i][6]

$A_Count = $A_Count + 1

_ArrayInsert2Dst( $XLArray2, $Array2DTo1String)

EndIf

Next

_ArrayInsert2Dst( $XLArray2, $A_Count)

_ArrayInsert2Dst( $XLArray2, $A_Percent)

_ArrayInsert2Dst( $XLArray2, $APercentage)

_XLArrayWrite($XLArray2, $File2,1,1,1,0 ,1,0)

_XLShow($File2,1)

And when I use the following code I don't get the desired results in the file. I get

0

0

-1.#IND

The code is this:

#include"ExcelCom.au3"

#include "Array2D.au3"

;#include "_ArrayView2D1D.au3" ;<-----Do I need?

$FilePath1=@ScriptDir&"\The Average.xls";'C:\au3\The Average.csv'

$FilePath2=@ScriptDir&"\Blank6.xls"; input

local $XLArray2[1][1]

Global $A_Percent = 0, $A_Count = 0

Global $B_Percent = 0, $B_Count = 0

Global $APercentage = $A_Percent / $A_Count

$XLArray1=_XLArrayRead($FilePath1,1,"usedrange")

_XLClose($FilePath1,1)

;=========================================

for $i=0 to UBound($XLArray1)-1

$Array2DTo1String=_Array2DTo1String( $XLArray1, $i, "Array contents",0 , 1)

if $XLArray1[$i][0] = "A" AND $XLArray1[$i][6] >= "0" Then

$A_Percent = $A_Percent + $XLArray1[$i][6]

$A_Count = $A_Count + 1

_ArrayInsert2Dst( $XLArray2, $Array2DTo1String)

EndIf

Next

_ArrayInsert2Dst( $XLArray2, $A_Count)

_ArrayInsert2Dst( $XLArray2, $A_Percent)

_ArrayInsert2Dst( $XLArray2, $APercentage)

_XLArrayWrite($XLArray2, $FilePath2,1,1,1,0 ,1,0)

_XLShow($FilePath2,1)

Share this post


Link to post
Share on other sites

This script works when working with .csv files. I would like something that works with .xls files. I don't want .csv involved at all. It would be lovely if I could just substitute everything that says ".csv" with ".xls" and my script would be done. Unfortunately it is not that easy. A bit frustrated at this point, so I will paypal someone 10 bux who can put togther a fully working script for me. I guess replace whatever in this script that will allow me to select the excel file that has the data and to save the new file as an excel file with the calculation(averages).

;Select file to calculate

$message = "Select"

$var = FileOpenDialog($message, "C:\", "(*.csv)", 1 )

$file1 = FileOpen($var, 0)

$file2 = FileOpen('C:\au3\The Average.csv',2)

; Check if file opened for reading OK

If $file1 = -1 Then

MsgBox(0, "Error", "Unable to READ file.")

Exit

EndIf

FileClose($file1)

FileClose($file2)

Global $A_Percent = 0, $A_Count = 0

Global $B_Percent = 0, $B_Count = 0

$file1 = FileOpen($var, 0)

$file2 = FileOpen('C:\au3\The Average.csv',1)

; Read in lines of text until the EOF is reached

While 1

$line = FileReadLine($file1)

If @error = -1 Then ExitLoop

$data = StringSplit($line, ",")

If $data[1]= "A" AND $data[7] >= "0" Then

$A_Percent = $A_Percent + $data[7]

$A_Count = $A_Count + 1

FileWriteLine($file2,$line)

$APercentage = $A_Percent / $A_Count

EndIf

Wend

FileWriteLine($file2,$A_Count)

FileWriteLine($file2,$A_Percent)

FileWriteLine($file2,$APercentage)

FileClose($file1)

FileClose($file2)

$file1 = FileOpen($var, 0)

$file2 = FileOpen('C:\au3\The Average.csv',1)

; Read in lines of text until the EOF is reached

While 1

$line = FileReadLine($file1)

If @error = -1 Then ExitLoop

$data = StringSplit($line, ",")

If $data[1] = "B" AND $data[7] >= "0" Then

$B_Percent = $B_Percent + $data[7]

$B_Count = $B_Count + 1

FileWriteLine($file2,$line)

$BPercentage = $B_Percent / $B_Count

EndIf

Wend

FileWriteLine($file2,$B_Count)

FileWriteLine($file2,$B_Percent)

FileWriteLine($file2,$BPercentage)

FileClose($file1)

FileClose($file2)

Share this post


Link to post
Share on other sites

#10 ·  Posted (edited)

Hi,

*** I think I could get this going if you posted an example xls file with the data you are using.. ****

Best, randall

PS for some reason you have left out

$APercentage = $A_Percent / $A_Count
?

Randall

QUESTIONS:

[1.Does your real data have blank lines?

2. Did you get decent output when you used my test script and data anyway?]

Edited by randallc

Share this post


Link to post
Share on other sites

Hi,

Sorry; my script was bugged; The Array transpose as Excel arrays around the other way.

#include"ExcelCom.au3"
#include "Array2D.au3"
#include "_ArrayView2D1D.au3" ;
$FilePath2=@ScriptDir&"\Blank6.xls"
$FilePath1=@ScriptDir&"\Blank5.xls"
;~ FileDelete($FilePath1)
;~ FileDelete($FilePath2)
_XLCreateBlank($FilePath1)
_XLCreateBlank($FilePath2)
$DataString="A,7,2,9,23,45,2,17,18,9"&@CRLF&"3,12,7,6,9,23,45,3,17,18"&@CRLF&"A,12,8,6,9,23,45,3,17,18"&@CRLF&"3,12,9,6,9,23,45,3,17,18"
$DataString&=@crlf&$DataString
$DataString=StringReplace($DataString,",",@TAB)
$XLRange=_XLpaste($FilePath1,1,"A",1,$DataString,0)
_XLSave($FilePath1,1)
;================================================
;START HERE WITH FILES KNOWN
;================================================
;~ $FilePath1=@ScriptDir&"\The Average.xls";input
;~ $FilePath2=@ScriptDir&"\Blank6.xls"; blank
local $XLArray2[1][1]
Global $A_Percent = 0, $A_Count = 0
Global $B_Percent = 0, $B_Count = 0
$XLArray1=_XLArrayRead($FilePath1,1,"usedrange")
_Array2DTranspose( $XLArray1)
;~ _ArrayView2D1D($XLArray1,"")
_XLClose($FilePath1,1)
;=========================================
for $i=0 to UBound($XLArray1)-1
    if $XLArray1[$i][0] = "A" AND $XLArray1[$i][6] >= "0" Then
        $A_Percent = $A_Percent + $XLArray1[$i][6]
        $A_Count = $A_Count + 1
        $Array2DTo1String=_Array2DTo1String( $XLArray1, $i,  "Array contents",0 , 1)
        _ArrayInsert2Dst( $XLArray2, $Array2DTo1String)
        $APercentage = $A_Percent / $A_Count
    EndIf
Next
_ArrayInsert2Dst( $XLArray2, $A_Count)
_ArrayInsert2Dst( $XLArray2, $A_Percent)
_ArrayInsert2Dst( $XLArray2, $APercentage)
_XLArrayWrite($XLArray2, $FilePath2,1,1,1,0 ,1,0)
_XLShow($FilePath2,1)
Best, randall

Share this post


Link to post
Share on other sites

Hi,

*** I think I could get this going if you posted an example xls file with the data you are using.. ****

Best, randall

PS for some reason you have left out

$APercentage = $A_Percent / $A_Count
?

Randall

QUESTIONS:

[1.Does your real data have blank lines?

2. Did you get decent output when you used my test script and data anyway?]

the data is calculated was two different cells with "0" and the third with something weird.

I was getting an error with $APercentage not being declared, so I put it as a global variable and I didn't get the error anymore. I will try again with what you posted up to see if I get better results. Yes some of the fields could be blank and that is why I have to check that field to make sure it = or > "0", so it can be included in the averages. Thanks for you attention in this matter.

Share this post


Link to post
Share on other sites

#13 ·  Posted (edited)

:whistle::):)

It worked. If you PM me or POST your email you use with paypal I can send you the 10 bux right now. Or if you want it in the mail just PM your address and your name and I will send it first thing Monday.

You earned dude. Now I just need to test to see if I am going to need a sort included before it calculates. Thanks again.

Edited by MaCgyver

Share this post


Link to post
Share on other sites

:whistle::):)

It worked. If you PM me or POST your email you use with paypal I can send you the 10 bux right now. Or if you want it in the mail just PM your address and your name and I will send it first thing Monday.

You earned dude. Now I just need to test to see if I am going to need a sort included before it calculates. Thanks again.

Whoa!! First I've seen someone live up to it.

[center]Common sense plays a role in the basics of understanding AutoIt... If you're lacking in that, do us all a favor, and step away from the computer.[/center]

Share this post


Link to post
Share on other sites

I have to iron out the script a little. It worked fine on the test file, but on the big file had a save problem. I think I can figure it out. When I tried this using the big file when its a csv file I got an exceeded range error. However when I sorted that same file and ran the script it worked fine. I will see if this is the case this time in this incident. You still earned the 10 bux so I'm just waiting for you to PM me or POST your info.

Share this post


Link to post
Share on other sites

Whoa!! First I've seen someone live up to it.

I am a man of my word.

Share this post


Link to post
Share on other sites

Well it is working with the main xls file now. What happened was the first sheet was wrong, so I deleted it. The info it was supposed to work off of was in sheet 2 and now that I've made sheet 2 to sheet 1 it worked. Now I have to do some more testing and then finish the script by adding C_Count/C_Percentage through Z_Count/Z_Percentage.

Share this post


Link to post
Share on other sites

Hi,

Thanks for the donatio to AutoIt donations..

I have put a new func in "Array2d.au3"

which will be 5x quicker on large files;

_ArrayInsert2DFrom2Dline( $XLArray3, $XLArray1, $i)

There is also sort function for Execelcom; see examples at the zip of examples on the link.

_XLSort(ByRef $s_FilePath,$Columns=1,$Direction1=1,$Columns2=1,$Direction2=1,$SortRange="UsedRange",$s_i_Sheet=1,$s_i_Visible=0 ,$i_Suppress=0)oÝ÷ Ù«­¢+Øí}a1ÉÉå
MXµ±¥­¹ÔÌÁ|Ì(¥¹±ÕÅÕ½Ðíá±
½´¹ÔÌÅÕ½Ðì(¥¹±ÕÅÕ½ÐíÉÉäɹÔÌÅÕ½Ðì(¥¹±ÕÅÕ½Ðí}ÉÉåY¥ÜÉŹÔÌÅÕ½Ðìì(ÀÌØí¥±AÑ ÈõMÉ¥ÁѥȵÀìÅÕ½ÐìÀäÈí  ±¹¬Ø¹á±ÌÅÕ½Ðì(ÀÌØí¥±AÑ ÄõMÉ¥ÁѥȵÀìÅÕ½ÐìÀäÈí    ±¹¬Ô¹á±ÌÅÕ½Ðì(íø¥±±Ñ ÀÌØí¥±AÑ Ä¤(íø¥±±Ñ ÀÌØí¥±AÑ È¤)}a1
ÉÑ    ±¹¬ ÀÌØí¥±AÑ Ä¤)}a1
ÉÑ    ±¹¬ ÀÌØí¥±AÑ È¤(ÀÌØíÑMÑÉ¥¹ôÅÕ½Ðí°Ü°È°ä°ÈÌ°ÐÔ°È°ÄÜ°Äà°äÅÕ½ÐìµÀí
I1µÀìÅÕ½ÐìÌ°ÄÈ°Ü°Ø°ä°ÈÌ°ÐÔ°Ì°ÄÜ°ÄàÅÕ½ÐìµÀí
I1µÀìÅÕ½Ðí°ÄÈ°à°Ø°ä°ÈÌ°ÐÔ°Ì°ÄÜ°ÄàÅÕ½ÐìµÀí
I1µÀìÅÕ½ÐìÌ°ÄÈ°ä°Ø°ä°ÈÌ°ÐÔ°Ì°ÄÜ°ÄàÅÕ½Ðì(ÀÌØíÑMÑÉ¥¹õMÑÉ¥¹IÁ± ÀÌØíÑMÑÉ¥¹°ÅÕ½Ðì°ÅÕ½Ðì±Q¤)½ÈÀÌØíôÄѼà(ÀÌØíÑMÑÉ¥¹µÀìõ
I1µÀìÀÌØíÑMÑÉ¥¹)¹áÐ(ÀÌØía1I¹õ}a1ÁÍÑ ÀÌØí¥±AÑ Ä°Ä°ÅÕ½ÐíÅÕ½Ðì°Ä°ÀÌØíÑMÑÉ¥¹°À¤)}a1MÙ ÀÌØí¥±AÑ Ä°Ä¤(ìôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôô(íMQIP!I]%Q %1L-9=]8(ìôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôô(íøÀÌØí¥±AÑ ÄõMÉ¥ÁѥȵÀìÅÕ½ÐìÀäÈíQ¡Ùɹá±ÌÅÕ½Ðìí¥¹ÁÕÐ(íøÀÌØí¥±AÑ ÈõMÉ¥ÁѥȵÀìÅÕ½ÐìÀäÈí ±¹¬Ø¹á±ÌÅÕ½Ðìì±¹¬)±½°ÀÌØía1ÉÉäÉlÅulÅt°ÀÌØía1ÉÉäÍlÅulÅt°ÀÌØíAɹѰÀÌØí}AɹаÀÌØí}
½Õ¹Ð)±½°ÀÌØí}AɹÐôÀ°ÀÌØí}
½Õ¹ÐôÀ)±½°ÀÌØí }AɹÐôÀ°ÀÌØí  }
½Õ¹ÐôÀ(ÀÌØía1ÉÉäÄõ}a1ÉÉåI ÀÌØí¥±AÑ Ä°Ä°ÅÕ½ÐíÕÍɹÅÕ½Ðì¤)}ÉÉäÉQɹÍÁ½Í ÀÌØía1ÉÉäĤ(íø}ÉÉåY¥ÜÉÅ ÀÌØía1ÉÉäÄ°ÅÕ½ÐìÅÕ½Ðì¤)}a1
±½Í ÀÌØí¥±AÑ Ä°Ä¤(ìôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôôô(ÀÌØíÑ¥µÉÍѵÀÌõQ¥µÉ%¹¥Ð ¤)½ÈÀÌØí¤ôÀѼU  ½Õ¹ ÀÌØía1ÉÉäĤ´Ä(%¥ÀÌØía1ÉÉäÅlÀÌØí¥ulÁtôÅÕ½ÐíÅÕ½Ðì9ÀÌØía1ÉÉäÅlÀÌØí¥ulÙtÐìôÅÕ½ÐìÀÅÕ½ÐìQ¡¸($$ÀÌØí}AɹÐôÀÌØí}AɹЬÀÌØía1ÉÉäÅlÀÌØí¥ulÙt($$ÀÌØí}
½Õ¹ÐôÀÌØí}
½Õ¹Ð¬Ä($%}ÉÉå%¹ÍÉÐÉɽ´É±¥¹ ÀÌØía1ÉÉäÌ°ÀÌØía1ÉÉäÄ°ÀÌØí¤¤($$ÀÌØíAɹÑôÀÌØí}AɹмÀÌØí}
½Õ¹Ð(%¹%)9áÐ)
½¹Í½±]É¥Ñ ÅÕ½ÐíÉÑèÅÕ½ÐìµÀíɽչ¡Q¥µÉ¥ ÀÌØíÑ¥µÉÍѵÀ̤¤µÀìÅÕ½ÐìµÍ½¹ÌÅÕ½ÐìµÀí±¤)}ÉÉå%¹ÍÉÐÉÍÐ ÀÌØía1ÉÉäÌ°ÀÌØí}
½Õ¹Ð¤)}ÉÉå%¹ÍÉÐÉÍÐ ÀÌØía1ÉÉäÌ°ÀÌØí}AɹФ)}ÉÉå%¹ÍÉÐÉÍÐ ÀÌØía1ÉÉäÌ°ÀÌØíAɹѤ)}a1ÉÉå]É¥Ñ ÀÌØía1ÉÉäÌ°ÀÌØí¥±AÑ È°Ä°Ä°Ä°À°Ä°À¤)}a1M¡½Ü ÀÌØí¥±AÑ È°Ä¤(
Best, Randall

Share this post


Link to post
Share on other sites

Hi,

Changed too often;

;_XLArrayCSV-like.au3 0_9
#include"ExcelCom.au3"
#include "Array2D.au3"
$xlAscending = 1
$xlDescending = 2
$FilePath2=@ScriptDir&"\Blank6.xls"
$FilePath1=@ScriptDir&"\Blank5.xls"
_XLCreateBlank($FilePath1)
_XLCreateBlank($FilePath2)
$DataString="A,7,2,9,23,45,2,17,18,9"&@CRLF&"B,12,7,6,9,23,45,3,17,18"&@CRLF&"A,12,8,6,9,23,45,3,17,18"&@CRLF&"3,12,9,6,9,23,45,3,17,18"
$DataString=StringReplace($DataString,",",@TAB)
for $a=1 to 8
$DataString&=@CRLF&$DataString
next
$XLRange=_XLpaste($FilePath1,1,"A",1,$DataString,0)
_XLSave($FilePath1,1)
;======================================
$timerstampTOTAL=TimerInit()
local $XLArray2[1][1],$XLArray3[1][1],$APercentage,$A_Percent,$A_Count,$B_Percent , $B_Count 
_XLsort($FilePath1,"A1",$xlDescending,"G1",$xlDescending,"usedrange",1)
$XLArray1=_XLArrayRead($FilePath1,1,"usedrange")
_Array2DTranspose( $XLArray1)
for $i=0 to UBound($XLArray1)-1
    if $XLArray1[$i][0] == "A" AND $XLArray1[$i][6] >= "0" Then
        $A_Percent +=  $XLArray1[$i][6]
        _ArrayInsert2DFrom2Dline(  $XLArray3, $XLArray1, $i)
    EndIf
Next
_ArrayInsert2Dst( $XLArray3, UBound($XLArray3))
_ArrayInsert2Dst( $XLArray3, $A_Percent)
_ArrayInsert2Dst( $XLArray3, $A_Percent / UBound($XLArray3))
_XLArrayWrite($XLArray3, $FilePath2,1,1,1,0 ,1,0)
$XLLastRow=UBound($XLArray3)+1
;~ $XLLastRow=_XLLastRow( $FilePath2,1)
;======================================
redim $XLArray3[1][1]
for $i=0 to UBound($XLArray1)-1
    if $XLArray1[$i][0] == "B" AND $XLArray1[$i][6] >= "0" Then
        $B_Percent +=  $XLArray1[$i][6]
        _ArrayInsert2DFrom2Dline(  $XLArray3, $XLArray1, $i)
    EndIf
Next
_ArrayInsert2Dst( $XLArray3, UBound($XLArray3))
_ArrayInsert2Dst( $XLArray3, $B_Percent)
_ArrayInsert2Dst( $XLArray3, $B_Percent / UBound($XLArray3))
_XLArrayWrite($XLArray3, $FilePath2,1,1,$XLLastRow,0 ,1,0)
ConsoleWrite("$timerstampTOTAL :"&round(TimerDiff($timerstampTOTAL)) & " mseconds "&@lf)
_XLClose($FilePath1,1)
_XLShow($FilePath2,1)
;~ _XLexit($FilePath2)
Best, randall

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