Jump to content

Sorting dates in Excel File - (Moved)


Recommended Posts

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

Link to post
Share on other sites
  • Moderators

Moved to the appropriate forum.

Moderation Team

Public_Domain.png.2d871819fcb9957cf44f4514551a2935.png Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind

Open spoiler to see my UDFs:

Spoiler

ArrayMultiColSort ---- Sort arrays on multiple columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Link to post
Share on other sites

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 by Nine
Link to post
Share on other sites

.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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2021-06-05 - Version 1.5.4.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2021-06-14 - Version 1.6.5.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Link to post
Share on other sites

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:...")

 

Link to post
Share on other sites

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.

#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

 

Link to post
Share on other sites

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).

Link to post
Share on other sites

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

 

Link to post
Share on other sites

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:

 

image.png.daf4151fae40c180c44acf020643d08f.png

Link to post
Share on other sites

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

 

Link to post
Share on other sites
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 by Nine
Link to post
Share on other sites

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 by Nine
Link to post
Share on other sites

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.

Link to post
Share on other sites

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

 

Link to post
Share on other sites

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

 

 

 

 

 

#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

image.thumb.png.d2f383c442aa095e8c619505e287d1fb.png

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
  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...