how to using marcro to check double condition

hi,
i want to make a macro to do below
while the column "booking Number" in sheet2 = "Booking#" in Sheet1
then check the POR in sheet1, if POR = "AES" keep the row in sheet2. otherwise delete the whole row in Sheet2
at botton of sheet2 list of count the total "Container Number" & unique "Booking Number"


the difficult is in sheet1, customer could combine several bookings into 1 BL. so that there are several booking # listed using ";" we need to check all of single bookings. if 1 of the combined bookings has list a "AES" will also need to be shown in Sheet2, can not be deleted.

i know it is quicker to use Access the only problem is the double booking numbers sometimes i manually create a new table items to move the second booking number over. but this is taking lots of times.

someone please help

but the point is i want to do this for my co-worker (old guy) who dont know very well of excel.
i just want him to click a botton then done.

Hi TMS, i tested your fomula, however it return delete for the double booking number cell which should keep

hi Jaslake,


the most i saw are around 11 bookings all combined into 1 BLs.

thanks Alansidman

hi Xladept, according to this code, you use the booking number in Sheet2 to check if it = to BL number in Sheet1, but i dont think that has any relationship in between, we still could use different BL number it is not a must to use the same number for BL and bookings.

thanks Jaslake, however, this code runs really slow and it seems like it is not really delete the right sections.

hi SNB,

thanks for your code, however it delete the wrong section, we should keep the "AES" and delete the rest. i think we should overwrite (If sn(j, 2) = "AES") into (If sn(j, 2) <> "AES").

i am testing on SNB's code right now, if that works well, i dont have to get you rewrite your code, i need to rewrite a bit from that due to my coworker might not use the same column with me. thanks anyway Jaslake.

Originally Posted by jaslake

Hi frez

Ah well, we tried...if you could expand on this a bit (based on the Sample Data)...I might look at it again...if not...so be it.

hi SNB, i tested on this code, only 1 problem, looks like it miss the first row if it is double booking number in it.

Originally Posted by snb

Please Login or Register  to view this content.

hi xladept, Macro stopped running due to BNum type doesnt match on highlighted lines, i updated the type to string then it didnt delete anything but returned the total of BLs and containers.

Originally Posted by xladept

Another attempt:

Please Login or Register  to view this content.

here i attached a new one, this is a bit longer than the pervious.

hi Xladept

i think there might be some miscommunication, what i want is keep the containers with AES and delete the rest, and get a total of how many containers with AES.

i tried your code and it doesnt delete any lines, only show the total of booking numbers and total of container type. or do we need to change the column order?

Originally Posted by xladept

I tried the program on both and it ran handily

on file.xlsx should return 4 BLs, 53 containers and 3 bookings
on File2 should return 36 BLs, 305 containers and 43 bookings.

i just re-upload file2 just now. please kindly download it again and try.

containers are in sheet2 Column A, the booking number for each container should serch in Sheet1 -Booking#, then we check column B, if it has a AES we keep the line for that container, if not we delete the line in Sheet2.

Originally Posted by xladept

Hi Frez,


You have no containers on your latest download but I got 52 bookings

after run your new code, the first 100 containers do not belongs to AES. should delete as well, but the code didnt.
first container's booking number is 8004676430 in Sheet1 it returned SUM instead of AES

yes, i run the code for those 2 files are both works fine. let me try several more first then i can solve it

Originally Posted by xladept

I didn't specify which book - try it now:

thanks a lot Xladept, this works really well. i tested several files already, it all returned the correct counts. thanks.