Sorting dates in Excel File - (Moved)

[don00]

Hello;

 

I'm new member in the community, and i appreciate your Assistance.

 

I have a small Excel project, which i need to modify, the following script is working fine but i just wanted to add some functions into the same loop if possible.

The main function for the whole script is, to open target Excel file, create 3 new work sheets, and color them, (so at this point there will be 4 total work sheets, one is the (Main) which includes the data, and 3 new once to be used later on.

 

then Store the numbers of the last row and column of the default work sheet. then split column (B) in this worksheet so that only date would be visible. till here all is done in the script.

 

i need to add the following:

A) to include in the same (for) loop which already exist in the script, sort the dates in a way that newest items on top (for the main sheet),

b) to find data items, where column (A) include work of "Asap", then copy the found rows to the second worksheet (TargetRed), highlight only the row with Red color in the (main) worksheet

c) to fined the rows where there is "DTA" in column "A", and empty space in Column "F". copy the found rows to  (TargetGreen), also highlight this row in green, and put comment "ok" somewhere in the end of the row (in TargetGreen )

c) if a row doesn't satisfy conditions from step (b) and (C), then highlight the row in orange color, copy it to  (TargetOrange).

D) show message box to user says:

"number of green items:..."

"number of Red items:..."

"number of Orange items:..."

 

 

#include <Excel.au3>

 

Local $_oExcel1 = _Excel_Open()

Local $sFilePath = "C:\Users\Desktop\Files\project.xlsx"
Local $_oWorkbook = _Excel_BookOpen($_oExcel1, $sFilePath)

_Excel_SheetAdd($_oWorkbook, Default, False, 3, "Target Green|Target Orange|Target Red")

Local $oWsTargetGreen = $_oWorkbook.Worksheets("Target Green")
$oWsTargetGreen.Tab.ColorIndex = 10

Local $oWsTargetOrange = $_oWorkbook.Worksheets("Target Orange")
$oWsTargetOrange.Tab.ColorIndex = 45

Local $oWsTargetRed = $_oWorkbook.Worksheets("Target Red")
$oWsTargetRed.Tab.ColorIndex = 3

Local $oWsSource = $_oWorkbook.Worksheets("Source")

Local $iLastRow = $oWsSource.Range("A1").SpecialCells($xlCellTypeLastCell).Row
Local $LastiColumn = $oWsSource.Range("A1").SpecialCells($xlCellTypeLastCell).Column

MsgBox(0, "The number of Rows", $iLastRow)
MsgBox(0, "The number of Columns", $LastiColumn)

for $i = 2 to $iLastRow

    Local $sTransactionDateTime = $oWsSource.Range("B" & $i).value
    Local $aTransactionDateTime = StringSplit($sTransactionDateTime, " ", 2)

    $oWsSource.Range("B" & $i).value = $aTransactionDateTime[0]
Next

[Melba23]

Moved to the appropriate forum.

Moderation Team

[Nine]

I think the easiest way would be to load all the source tab data into a 2D array.  Then you can perform all the tasks you want using _ArraySort, etc.  When ready, write those infos to each Excel tab.

When posting code, please this tool.

Edited February 9, 2021 by Nine

[water]

.A ... sort the dates ... Use function _Excel_RangeSort

.B ... find "Asap" ... Use function _Excel_RangeFind. Use function _Excel_RangeCopyPaste to copy the data to another sheet.

.C ... find "DTA" ... Use function _Excel_RangeFind. Column 2 of the returned array holds the address of the cell. use .offset to get the value of column B and check for empty

[junkew]

Why would you not write it in VBA macro?

D 😉

#include <MsgBoxConstants.au3>
msgbox($MB_SYSTEMMODAL, "Title","number of green items:...")
msgbox($MB_SYSTEMMODAL, "Title","number of Red items:...")
msgbox($MB_SYSTEMMODAL, "Title","number of Orange items:...")

 

[don00]

doing it in VBA is easy, just the project i work in it requires Autoit

[junkew]

It helps if you make it in VBA and partly in AutoIt and post both pieces of coding as then people can help you easier in translating the magic numbers or the less obvious sometimes in AutoIt to deal with ranges instead of iterating line by line

[don00]

Hello;

 

I would appreciate if you could assist, the idea is that i need to run function on the (source) sheet, to check all column A for the string "SLS", if that happened, then just copy this row into the (target red) sheet, and also Mark this row red in the (source file).

 

i just need here inside the (For) Loop, i did assigned the Column A as dynamic variable (since it will need to include the whole column), and then i need to run inside this cycle conditional statement , 

 >> "if value of range A1 includes "SLS" (of the source sheet) >> then >>copy the found rows to worksheet (Target Red), and also highlight the row with red color in the worksheet (source).

 

am not sure how to correctly adjust the IF statement inside the loop. to give the required result at the end.

expandcollapse popup

#include <Excel.au3>

Local $sFilePath = "\\Disktop\Files\project.xlsx"
Local $_oExcel1 = _Excel_Open()
Local $_oWorkbook = _Excel_BookOpen($_oExcel1, $sFilePath)

_Excel_SheetAdd($_oWorkbook, Default, False, 3, "Target Green|Target Orange|Target Red")

Local $oWsTargetGreen = $_oWorkbook.Worksheets("Target Green")
$oWsTargetGreen.Tab.ColorIndex = 10

Local $oWsTargetOrange = $_oWorkbook.Worksheets("Target Orange")
$oWsTargetOrange.Tab.ColorIndex = 45

Local $oWsTargetRed = $_oWorkbook.Worksheets("Target Red")
$oWsTargetRed.Tab.ColorIndex = 3

Local $oWsSource = $_oWorkbook.Worksheets("Source")


Local $iLastRow = $oWsSource.Range("A1").SpecialCells($xlCellTypeLastCell).Row
Local $iLastColumn = $oWsSource.Range("A1").SpecialCells($xlCellTypeLastCell).Column
Local $sLastColumn = _Excel_ColumnToLetter($iLastColumn)

MsgBox(0, "The number of Rows", $iLastRow)
MsgBox(0, "The number of Columns", $iLastColumn)


For $i = 2 To $iLastRow

    Local $sTransactionDateTime = $oWsSource.Range("B" & $i).value
    Local $aTransactionDateTime = StringSplit($sTransactionDateTime, " ", 2)

    $oWsSource.Range("B" & $i).value = $aTransactionDateTime[0]

    Local $ColumnA = $i

    Local $read1 = _Excel_RangeRead($_oWorkbook, $oWsSource, "A" & $i, 3)
    StringInStr("SLS", 0)

    If $read1 = "SLS"

        Then _Excel_RangeWrite($_oWorkbook, $oWsTargetRed, $read1, "A" & $i)
    EndIf


Next

 

[don00]

sorry for typing mistake.. here is what the if function supposed to do :

 

>> "if value of range A1 till the last row....includes "SLS" (of the source sheet) >> then >>copy the found rows to worksheet (Target Red), and also highlight the row with red color in the worksheet (source).

[Nine]

Try this loop :

Local $aRow, $iOut = 0
For $i = 2 To $iLastRow
  If StringInStr($oWsSource.Range("A" & $i).Value, "SLS", $STR_CASESENSE) Then
    $iOut += 1
    $aRow = _Excel_RangeRead($_oWorkbook, $oWsSource, "A" & $i & ":" & $sLastColumn & $i)
    _Excel_RangeWrite($_oWorkbook, $oWsTargetRed, $aRow, "A" & $iOut)
    $oWsSource.Range("A" & $i & ":" & $sLastColumn & $i).Interior.ColorIndex = 3
  EndIf
Next

 

[don00]

Thanks alot @Nine , thats exactly what i need.

 

Only small issue, it does copy the mentioned row from the sheet (source) and paste it to the (Target Red )sheet. the problem is that it paste it in a different format. since there are dates and numbers,  is there anyway to arrange it so that i could copy and paste exactly same format as it was originally in the source?  here is screenshot of the (source) screenshot:

 

[Nine]

Try with :

_Excel_RangeCopyPaste

Instead of read/write.

[don00]

i tried to combine both source and target in same line. but nothing happens. neither copy not paste. please advise what might be wrong in it.  also am sure that i wrote more lines than what is actually needed. i just try all possible scenarios.

If StringInStr($oWsSource.Range("A" & $i).Value, "SLS", $STR_CASESENSE) Then
        $iOut += 1
        $aRow = _Excel_RangeRead($_oWorkbook, $oWsSource, "A" & $i & ":" & $sLastColumn & $i)


        Local $RangeSource = $oWsSource.range("A" & $i & ":" & $sLastColumn & $i)
        Local $RangeTarget = $oWsTargetRed.range("A" & $iOut)
        _Excel_RangeCopyPaste($_oWorkbook, $RangeSource, $RangeTarget)
        $oWsSource.Range("A" & $i & ":" & $sLastColumn & $i).Interior.ColorIndex = 3
    EndIf

 

[Nine]

1 hour ago, don00 said:

i tried to combine both source and target in same line

You can't. You need two different statements.  See examples in help file...

Edited February 11, 2021 by Nine

[Nine]

That is false.  You can do it in a single line.  I always used it with 2, but it is feasible to do it in a single line.  Just tested it...Sorry for confusion.

Edited February 11, 2021 by Nine

[don00]

yes i have tested it now in a single line, its just there is no data get pasted, always empty. not sure if the range data what i put is wrong or what could be the issue. if you have tested it kindly share me to see what was my mistake.

[Nine]

Here :

Local $oRange, $iOut = 0
For $i = 2 To $iLastRow
  If StringInStr($oWsSource.Range("A" & $i).Value, "SLS", $STR_CASESENSE) Then
    $iOut += 1
    $oRange = $oWsTargetRed.Range("A" & $iOut & ":" & $sLastColumn & $iOut)
    _Excel_RangeCopyPaste($oWsSource, "A" & $i & ":" & $sLastColumn & $i, $oRange, False, $xlPasteAll)
    $oWsSource.Range("A" & $i & ":" & $sLastColumn & $i).Interior.ColorIndex = 3
  EndIf
Next

 

[don00]

Here is the full script from my end, shows error, please advise.

 

 

 

 

 

expandcollapse popup

#include <Excel.au3>

Local $sFilePath = "C:\Users\ahmed\OneDrive\Desktop\training\Assignment 1.xlsx"
Local $_oExcel1 = _Excel_Open()
Local $_oWorkbook = _Excel_BookOpen($_oExcel1, $sFilePath)

_Excel_SheetAdd($_oWorkbook, Default, False, 3, "Target Green|Target Orange|Target Red")

Local $oWsTargetGreen = $_oWorkbook.Worksheets("Target Green")
$oWsTargetGreen.Tab.ColorIndex = 10

Local $oWsTargetOrange = $_oWorkbook.Worksheets("Target Orange")
$oWsTargetOrange.Tab.ColorIndex = 45

Local $oWsTargetRed = $_oWorkbook.Worksheets("Target Red")
$oWsTargetRed.Tab.ColorIndex = 3

Local $oWsSource = $_oWorkbook.Worksheets("Source")


Local $iLastRow = $oWsSource.Range("A1").SpecialCells($xlCellTypeLastCell).Row
Local $LastiColumn = $oWsSource.Range("A1").SpecialCells($xlCellTypeLastCell).Column

MsgBox(0, "The number of Rows", $iLastRow)
MsgBox(0, "The number of Columns", $LastiColumn)

Local $oRange, $iOut = 0
For $i = 2 To $iLastRow

    Local $sTransactionDateTime = $oWsSource.Range("B" & $i).value
    Local $aTransactionDateTime = StringSplit($sTransactionDateTime, " ", 2)

    $oWsSource.Range("B" & $i).value = $aTransactionDateTime[0]

    If StringInStr($oWsSource.Range("A" & $i).Value, "SLS", $STR_CASESENSE) Then
        $iOut += 1
        $oRange = $oWsTargetRed.Range("A" & $iOut & ":" & $LastiColumn & $iOut)
        _Excel_RangeCopyPaste($oWsSource, "A" & $i & ":" & $LastiColumn & $i, $oRange, False, $xlPasteAll)
        $oWsSource.Range("A" & $i & ":" & $LastiColumn & $i).Interior.ColorIndex = 3
    EndIf

Next

[Nine]

You need to use the string $sLastColumn not the numeric value.

[don00]

got it, all is ok now, i appreciate your support.