Require Reduction in Macro Running time and small modification

Dear excel Gurus,
I have recorded multiple macros for a specific task of discrepancy work-out and for the same created new macro for running all the macro at once in order, it working fine, however, it execution time is very high i.e. 12-13 minutes, as per the said task should not take more than 2-3 minutes for running the same macro.
Further, it require following small modification…
Drawback: Following Lists are appearing without serial number and with duplicates.
DUPLICATE IN LIST, IMO MISMATCH, ISO MISMATCH, Line Mismatch, POD CHANGE, STATUS MISMATCH, RELEASED LIST, NOT IN LIST, NOT IN YARD
# Container No. Weight ISO Status IMO POL POD Cat Mode Line Rfr Sts Temp
1 GLDU5267198 22.3 2210 F NSA JED E T USC N
2 GLDU5267198 22.3 2210 F NSA JED E T USC N
24 TCLU5098407 15.79 4510 F NSA SPE E R USC N
830 TCLU5098407 15.79 4510 F NSA SPE E R USC N
831 TCLU5098407 15.79 4510 F NSA SPE E R USC N
832 TCLU5098407 15.79 4510 F NSA SPE E R USC N
833 TCLU5098407 15.79 4510 F NSA SPE E R USC N
834 TCLU5098407 15.79 4510 F NSA SPE E R USC N

Require :
1. All above lists are to be appeared with NO DUPLICATES
2. All Above list are to be appeared in serial number
# Container No. Weight ISO Status IMO POL POD Cat Mode Line Rfr Sts Temp
1 GLDU5267198 22.3 2210 F NSA JED E T USC N
2 TCLU5098407 15.79 4510 F NSA SPE E R USC N

Please ref. attached file for your ready ref.
Please help in this regards.

- Ravi

Did 1, so you have idea how to short code

Please Login or Register  to view this content.

Kind regards
Leo

Sir,
Thanks for your prompt response.
Your given code is really short (due change in a said / single macro, execution time of all the code is reduced by 3 minutes) , however, the output in sheet STATUS MISMATCH reflecting all MATCHING as well as MISMATCHING; the requirement is only MISMATCHING records in sheet STATUS MISMATCH.

Please advise.

- Ravi

Did other 1

best you let me know in wich order youre macro's has to run

Please Login or Register  to view this content.

Cheers
Leo

Sir,
Thank you so much for spending valuable time and share these 2 codes , by which change in a said / 2 macros, execution time of all the code is reduced by 6 minutes , Thanks a lot. Great Job.
Thanks and Regards especially for your contribution. Thanks a lot.
HOWEVER, FOLLOWING ARE DRAWBACKS...
>> In DUPLICATE IN LIST
There are following 2 containers are duplicate in EAL
1. GLDU5267198
2. TCLU5098407
But appearing only one container i.e. GLDU5267198
>>In IMO MISMATCH
There is only one IMO Changei.e. SPPU2211268
IMO as per EAL(List) is 6.1 and IMO as per SPARCS (In Yard) is 3.
however, appearing 287 containers and following 2 column are appearing blank
1. IMO as per EAL(List) and 2. IMO as per SPARCS (In Yard)
>> In STATUS MISMATCH
There is only 6 containers having STATUS mismatch, however, Appearing 19 containers list.
>> Further, Serial Numbers in following lists are not in order/serial
DUPLICATE IN LIST, IMO MISMATCH, ISO MISMATCH, Line Mismatch, POD CHANGE, STATUS MISMATCH, RELEASED LIST, NOT IN LIST, NOT IN YARD
Please ref. attached file after modifications of 2 codes.

Sir, I am 100% sure that you are one, who will definitely help me to complete my task. Thanks in advance.

- Ravi

Ravi,

before i go furder let me know
wich code runs first, wich second, wich third and so on

Kind regards
Leo

Ok i found


Cheers
Leo

Sir,

Any Update?

- Ravi

no, this take long time but working on it


cheers
Leo

Ok Sir,

Anyway thanks a lot Sir for your Prompt response.

Thanks in advance.

- Ravi

First 5

1 and 5

Please Login or Register  to view this content.

2

Please Login or Register  to view this content.

3

Please Login or Register  to view this content.

4

Please Login or Register  to view this content.

5 is in first

Kind regards
Leo

Sir,

The fields in "(2) System_as it is" may / will not in sequence / order; it will be in any order/sequence as per user

hence,i had defined Sub SYS_LIST() (first find specific fields and then make table "2) System_list" in required order)

Cells.Find(What:="Equipment ID" --- Selection.Copy --- Application.CutCopyMode = False



- Ravi

i dont see this different users in youre code


Cheers
Leo

Sir,

In attached file there is a example, however, the fields in "(2) System_as it is" may / will not in sequence / order; it will be in any order/sequence as per user, hence, hence,i had defined Sub SYS_LIST() (first find specific fields and then make table "2) System_list" in required order)

Cells.Find(What:="Equipment ID" --- Selection.Copy --- Application.CutCopyMode = False .........................

- Ravi

this part "as per user"

i dont see in your code


cheers
Leo

Sir,

If your see my first post with attachment, as per code SYS_LIST() it find followings fields from available sheet "(2) System_as it is"...
Equipment ID, Wgt-MTn, Type, Sts, IMO, POL, POD, Cat, Inbound Carrier, Line, Temp respectively
and make a table...
using... Cells.Find(What:="Equipment ID",

irrelevant to data order it may be in sequence or nor, it will find and prepare desired table

- Ravi

Any update Sir.

- Ravi

All done like i think it has to work,
just run the 1 that runs them all



Cheers
Leo

Sir,
It’s great work, however, it contains following discrepancies...
1. Order / sequence of fields/columns in “(2) System_as it is”; it should find the desired fields from anywhere from data “(2) System_as it is”
As per the requirement and request in previous posts, SYS_LIST() should find followings fields from available sheet "(2) System_as it is"...
Equipment ID, Wgt-MTn, Type, Sts, IMO, POL, POD, Cat, Inbound Carrier, Line, Temp respectively
Irrelevant / irrespective to data order it may be in sequence or not, it will find the above fields and prepare desired table in proper sequence, which is not happening in this case; for test, I have re-aligned some fields order in "(2) System_as it is” and run the macro, but it is not working…

2. Duplicates containers
There are following 2 containers are duplicate in EAL
1. GLDU5267198
2. TCLU5098407
However, it should be appear 1 time (not multiple times) in all the relevant lists.
GLDU5267198 (for example showing 2 times)
GLDU5267198 (for example showing 35 times)
And same cases in all the relevant lists.

3. Further, Serial Numbers in following lists are not in order/serial
DUPLICATE IN LIST, IMO MISMATCH, ISO MISMATCH, Line Mismatch, POD CHANGE, STATUS MISMATCH, RELEASED LIST, NOT IN LIST, NOT IN YARD

4. In following list, fields appearing as 0
>> In “(2) System_list” , POL field appearing as 0
>> In DUPLICATE IN LIST, ISO MISMATCH, STATUS MISMATCH, RELEASED LIST, NOT IN YARD, Line Mismatch and IMO field appearing as 0
>> In POD CHANGE and NOT IN LIST, POL field appearing as 0
Please check and advise.

- Ravi

Few modifications, if i forgot something just let me know


Kind regards
Leo

Ravi,

how does it go ?


cheers
Leo

Sir,

Considering following criteria (formula)...

Please Login or Register  to view this content.

Weight Mismatch is not working...


- Ravi

Modification in This_WGT

Kind regards
Leo

Sir,

Thanks for your great efforts, however, in case of Weight mismatches..

As per formula, we have in matching weights considering
-0.5 or +0.5, and after adding or subtracting 0.5, if MATCHING it should not reflected as MISMACTH

"Weight as per EAL(List)" - 8.07 AND "Weight as per SPARCS (In Yard)" - 8.1 - MATCHING AFTER -0.5 or +0.5, IT SHOULD NOT REELECTED AS MISMACTHES

thanks in advance for your really great works.
- RAVI

Ravi,

modification in DIS_WGT for the weight mismatch
and in EAL_T2C, first list created without duplicates


Kind regards
Leo

Sir,
Duplicate showing NIL, whereas, the EAL ((1) EAL_as it is) is having...

GLDU5267198 (Duplicate 2 times in EAL)
TCLU5098407 (Duplicate 35 times in EAL)

It's OK that duplicates are removed from everywhere, however, following reports should have appears including DUPLICATES for calculation purposes to co-relate all the discrepancy(workout)i.e. EAL list, System list, Released list, Not in list, not in yard etc.

DUPLICATE IN LIST
# Container No. Weight ISO Status IMO POL POD Cat Mode Line Rfr Sts Temp
1 GLDU5267198 22.3 2210 F 0 NSA JED E T USC N
2 TCLU5098407 15.79 4510 F 0 NSA SPE E R USC N
GLDU5267198 (Duplicate 2 times in EAL)
TCLU5098407 (Duplicate 35 times in EAL)

Thanks in advance.

- Ravi

With Duplicates



Kind regards
Leo

Ravi,

if you have no more questions please mark the case as solved

Kind regards
Leo

I have also similar files and workouts I have check but it stuck at line mdif = Abs(arr(x, 3) - r)
of Sub DIS_WGT()

Ref. attached file for details

Hi Leo,
I m sure you can rectify this.
K Ravindra

With emptys

Please Login or Register  to view this content.

or without emptys

Please Login or Register  to view this content.

Thanks Leo for your prompt response,
Kindly advise about code usage.. which code to be used here .. With emptys or without emptys
K Ravindra

try both and you find out

cheers
Leo

OK, trying with considering following...
Sub DIS_WGT_1()--With emptys
and
Sub DIS_WGT_2()--without emptys
Putting above 2 codes instead of Sub DIS_WGT()
So it will execute result from both the perspective i.e.With emptys and without emptys

Let you know the result after testing
K Ravindra

Leo,
Can u please tell me the full form of OP

No, and Not hijacked I m just trying to solving my task, which I am doing through MS Access and luckily u are resolving case here is very helpful for all global users who are using NAVIS SPARCS and EDI.
EAL (Export Advance List) related queries are same for all global users who are using Navi SPARCS and EDI files

Hi Leo,
1.

Stuck at line of DIS_IMO

Please Login or Register  to view this content.

(findings- it is not working when all IMOs are blanks)

2
And, in case of 45G1 [in (1) EAL_as it is], it is converting 45 into (1) EAL_List

file enclosed for your reference

even so, not the correct way to handle this,

please start a new tread, you can refer to this tread by giving url,
https://www.excelforum.com/excel-pro...ml#post4707350

Kind regards
Leo