Concatenate Date & Time based on Specific Criteria

ashish mehra · Dec 23, 2015

Hi,

I am posting a sample file containing 6k rows data wherein I have problem in converting the code to be able to work dynamically & if possible a more precise code since my colleague has 5.5 lacs rows data.

I tried using sub "DateTimeCalculationNotWorking" but somehow this contains bug.

Explanation:

There are 3 Sheets; Source, Criteria & Output

Source sheet contains Distributor, Entry Date & Entry Time
Criteria sheet contains Distributor & Entry Date

In Output sheet; Distributor & Entry Date need from Criteria Sheet to be match from Source Sheet; & then Date & Time to be concatenated.

Note: If there are 2 or more occurrence of same Distributor with same Date found then we need the first recorded Entry Time (refer Sorting & DuplicateRemover Macro)

Please check & update.

Regards,
AM

vletm · Dec 23, 2015

@ashish mehra
Note:

Code:

Sub DateTimeCalculation()
    Sheets("Output").Activate
    LR = Cells(Rows.Count, 1).End(xlUp).Row
    Sheets("Output").Range("A2:C" & LR).ClearContents

    Sheets("Source").Range("A1:C6822").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Sheets("Criteria").Range("A1:B5174"), CopyToRange:=Range( _
        "Output!$A$1:$C$1"), Unique:=True
End Sub

Why did You use everywhere LR with Your ... NotWorking?
Check this...
Ideas?

Code:

Sub DateTimeCalculationNotWorking()
'Please test this sub
    Application.ScreenUpdating = False
   
    Sheets("Output").Activate
    LR = Cells(Rows.Count, 1).End(xlUp).Row
    SR = Sheets("Source").Cells(Rows.Count, 1).End(xlUp).Row
    CR = Sheets("Criteria").Cells(Rows.Count, 1).End(xlUp).Row

    Sheets("Output").Range("A2:C" & LR).ClearContents
    Sheets("Source").Range("A1:C" & SR).AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Sheets("Criteria").Range("A1:B" & CR), CopyToRange:=Range( _
        "Output!$A$1:$C$1"), Unique:=True
    Application.ScreenUpdating = True
End Sub

ashish mehra · Dec 24, 2015

@vletm Since I am new to VBA & declaring LR in all sheets was silly on my
part.

Thanks for your help in making dynamic code.

My colleague has 5.5. lacs of data & he need to perform this kind of task on a regular basis; request to all to check if there is a faster way than the upper code.

Regards,
AM

Marc L · Dec 24, 2015

Hi !

For a huge number of rows, faster ways than an advanced filter :

• via SQL query

• via array variables

• via Access …

ashish mehra · Dec 25, 2015

Thanks Marc for the suggestion.

@vletm The code is tested & found working fine; however when I tried to declare LR, SR & CR variables as integer I found there is some bug that stops on Sorting Macro.

I am sharing 2 files; working file & not working file for reference.

goo.gl/zjPS6p

Regards,
AM

vletm · Dec 25, 2015

@ashish mehra
Copy Macros to Your Own file and test!
I cleared all data away!
Sorting works ...
You would sometimes tell to code, which sheet You want to use...

ashish mehra · Dec 25, 2015

@vletm

I test the macro again & found a bug; the Sorting macro runs fine till 7 times

but shows 1004 run time error on 8th round. Very Strange........

Please test on your system & update if it works on 8th round.

Regards,
AM

vletm · Dec 25, 2015

@ashish mehra
Add the .SortFields.Clear to Sub Sorting() like ...

Code:

    With Sheets("Output").Sort
        .SortFields.Clear
        .SortFields.Add ...

ashish mehra · Dec 25, 2015

vletm said:
@ashish mehra
Add the .SortFields.Clear to Sub Sorting() like ...

Code:

With Sheets("Output").Sort .SortFields.Clear .SortFields.Add ...

@vletm

This is still not working fine. Please check.

Code:

Sub Sorting()
LR = Cells(Rows.Count, 1).End(xlUp).Row

    ActiveWorkbook.Worksheets("Output").Sort.SortFields.Add Key:=Range("A2:A" & LR _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Output").Sort.SortFields.Add Key:=Range("B2:B" & LR _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Output").Sort.SortFields.Add Key:=Range("C2:C" & LR _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Output").Sort
        .SortFields.Clear
        .SortFields.Add
        .SetRange Range("A1:C" & LR)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

vletm · Dec 25, 2015

@ashish mehra
I wrote in #& Reply like Copy Macros to Your Own file and test!
You didn't copy!
And as You notice #8 Reply looks different than #9 ...
If You are using Your own version ...
then of course my help cannot solve You challenge!
and I asked to copy only ONE row with #8 Reply

ashish mehra · Dec 25, 2015

@vletm

I did follow your instruction.

I replaced the code provide by you in reply#6 with excel file.

Of course I need your help buddy!

vletm · Dec 25, 2015

@ashish mehra
Did You open my previous file (#6 Reply)? (Maybe?)
Did You copied all Macros from that? (No!/Maybe!)
Did You pasted those to Your 'file that not work' You're using? (No!)
Did You add ONE row like #8 Reply? (No!)
You can try to compare from #8 Reply and #9 Reply, (Yes!)
You cannot find 'With Sheets("Output").Sort'-text from #9 Reply! (Yes!)

Go back to #6 Reply ... and try to follow my instructions.
Go back to #8 Reply ... and try to follow my instructions.

ashish mehra · Dec 26, 2015

@vletm

Maybe I skip your instruction but you can provide your version of excel file which is working fine.

Anyways I am questioning on excel.

If same code can works fine till 7 times then why not on 8th

go. This is what I trying to learn & interested in reason for not working.

Regards,
AM

vletm · Dec 26, 2015

@ashish mehra
Not maybe .. You skip .. You can compare #8 and #9 photos!
You add Sorting fields .. add Sorting fields .. add Sorting fields .. add Sorting fields .. until Sorting said NO MORE fields!
Even here, it have to make cleaning ... like in normal life.
This file should work and
it don't have any data,
it's empty!
If You'll skip to copy data ...

ashish mehra · Dec 26, 2015

@vletm

Oops....I added two lines instead of adding single line as per reply 8

Thanks for your efforts.

As I am new to VBA; if a code is giving correct result on first go then it should give correct result on 8th round logically.

What you say Yes/No/Maybe

In case you know the reason then please clarify.

Regards,
AM

vletm · Dec 26, 2015

@ashish mehra
1) You didn't make a copy after #6 Reply
2) YES, You add TWO rows in wrong place.
3) If code works well one time .. two times ..seven times ...
or more there still can be error, like with 'Sorting'.
This case is like .. hmm ...
You can eat an egg, You can eat 2nd egg ... You can eat ...
until You cannot or You don't want to eat any more.
Maybe the reason would be Your full stomach.
Same thing happened with 'Sorting',
the code Add ... Add ... Add ...
until it cannot handle more and
if finally gave 'runtime error 1004'.
As I wrote with #14, clearing is important with coding too!
What would You do with egg case?
Continue eating or what?

ashish mehra · Dec 26, 2015

I can eat 2 eggs in a day.

Regards,
AM

Concatenate Date & Time based on Specific Criteria

ashish mehra

Active Member

Attachments

vletm

Excel Ninja

ashish mehra

Active Member

Marc L

Excel Ninja

ashish mehra

Active Member

vletm

Excel Ninja

Attachments

ashish mehra

Active Member

vletm

Excel Ninja

ashish mehra

Active Member

vletm

Excel Ninja

ashish mehra

Active Member

vletm

Excel Ninja

ashish mehra

Active Member

vletm

Excel Ninja

Attachments

ashish mehra

Active Member

vletm

Excel Ninja

ashish mehra

Active Member