Complex Micro/Formula to Search from one File and Return Value in Another File

[atthershabbir]

PINCODE - Search.xls this is a file with 3 courier pincode list.
i have set Vlook and Index formulas to search the pincodes service available or not.
If available it shows the place name else #N/A means not available.
But i want automation on my other file which is SALESHISTORY.csv
The file SalesHistory first 2 columns will be Blank which i fill manually and need the first Field "COURIER" to be automated.

What i do manually is.....
First Open both the files together "PINCODE - Search.xls" and "SalesHistory.csv"
Take the PINCODE field one by one and paste in PINCODEsearch.xls to see which courier is available.
There are chances for one pincode available in all couriers (Bluedart, Aramex, DTDC) else only 1 or 2 if not all 3.

I search manually and assign each order in "COURIER" column manually to particular courier depending on the price range which is
if total Sale value is between Rs 0- Rs 500 goes in Aramex then 500-999 goes in DTDC and finally 1000-higher whose in BLUEDART.
but if we sort by price range and if the courier service is not support then there is a priority as first always BLUEDART if service not available then DTDC and finally Aramex.

What i really want is... once i open SALESHISTORY.csv file which is downloaded on daily basis as my daily orders list.
I should run a micro which first go to the first record and checks the total sales if value is between 0-500 then searchs the PINCODE in PINCODESEARCH file which is separate file if found in ARAMEX should mention ARAMEX in COURIER field, if not then search in DTDC else Bluedart.
if the value of between 501-999 then should search for DTDC and so on.... for each order.


Its really complex but am sure it can be done in excel by combination of few formulas hoping to hear few comments on this.

[dangelor]

This may work, but it needs named ranges for the courier lists...

Please Login or Register  to view this content.

[atthershabbir]

thanks for the code... wow looks great but sorry its all over my head...
you can take me as a new user in micros, codes and formulas can you please guide me step by step how to work this code up.. it will be a great help.. plzzz
i use Microsoft Excel 2007.

[atthershabbir]

plz is sumone can help .......

[dangelor]

This code needs to be placed in a module in the PincodeSearch.xls file. You also need to create named ranges for each of the courier lists on the Search worksheet (e.g. Cells E3:F11381 would be the ARAMEX named range) and have the SalesHistory.csv file open before running the code.

[atthershabbir]

Well tried to work with your code but the results are not as it should be.
when i run the code almost all the field are filled with BLUEDART Couriers.
Can you please rewrite the code as below:

i want is:
Excel first starts the search in first row checks the
If 'Total Amount' field >= Rs 1000
Searchs the 'PINCODE' field first in BLUEDART if available displays BLUEDART in 'Courier' Field and goes to next row
If not in BLUEDART then searchs in DTDC
else finally ARAMEX

If 'Total Amount' field between Rs 500 - Rs 999
Searchs the 'PINCODE' field first in DTDC if available displays DTDC in 'Courier' Field and goes to next row
If not in DTDC then searchs in Aramex
else finally Bluedart

If 'Total Amount' field between Rs 0 - Rs 499
Searchs the 'PINCODE' field first in Aramex if available displays Aramex in 'Courier' Field and goes to next row
If not in Aramex then searchs in DTDC
else finally Bluedart

If Pincode do not exist in any courier list then COURIER field remains blank.

[atthershabbir]

buddy plz reply...

[atthershabbir]

Still waiting... "dangelor" whr r u? need urgent help plzzz

[dangelor]

Try this...

Please Login or Register  to view this content.

[atthershabbir]

Thanks "dangelor" for the code after applying on first try got a - RUN TIME ERROR '13' Type Mismatch
Clicked on Debug the code line as : vTS(x, 1) = Mid(vTS(x, 1), 4) * 1

Can you fix it plzzzz or guide me i would try out, as the code is too complex for me to even understand...

[dangelor]

The code runs OK for me on the files you've attached. That particular line of code strips off the first three characters in each of the items in the column of Total Sales, starting from line 2. This is to remove the "Rs." in front of the numeric value. If your data does not conform to this standard, it will not work.

[atthershabbir]

The code runs OK for me on the files you've attached. That particular line of code strips off the first three characters in each of the items in the column of Total Sales, starting from line 2. This is to remove the "Rs." in front of the numeric value. If your data does not conform to this standard, it will not work.

Thanks for your reply.. well actually my file do not have "Rs." actually the column has a default format of Currency hence it displays "Rs." in front of the value but in actual the field only contains the numbers which are between 1 to 10000.
hope you can help me with this. Thanks.

[dangelor]

Try this...

Please Login or Register  to view this content.

[atthershabbir]

thanks buddy.. now the code runs perfectly as required....
thanks a tons it will save me lots of time on daily basis........... thanks once again luv u guys

[atthershabbir]

thanks buddy.. now the code runs perfectly as required....
thanks a tons it will save me lots of time on daily basis........... thanks once again luv u guys

[dangelor]

Glad it works!

[atthershabbir]

Hi there... firstly as said earlier thanks a lots for your complex micro on search pincode and generate courier name
which was coolest.. in continuation on same file if possible i also require data sorting by courier names in separate
micro.
once the courier names are assigned we have to manually give AirWayBill numbers to each shipment once that is done
we make 3 sheets for each courier on same file then email each sheet to particular courier service as record for our
daily shipments and print out for barcode scanning to enter shipping details.
Sample Excel SalesHistory with full data here.

What Micro Should do is :
1) Create 3 new sheets with name Aramex, Bluedart & DTDC.
2) Sort data and Search Aramex from 'SalesHistory' Sheet and paste the data on 'Aramex' Sheet and so on for other 2
couriers (data only be copy and pasted in new sheet, original sheet remains unchanged)
3) Now on each new sheet change "Courier" field and make it as 'Sr No'
4) Change the formatting of AWB Number.
a) first add '*' in front and back of the AWB number that is for Barcode scanning.
b) change the Font for the AWB field as "CODE39" and font size as 28.
5) As you can see in sample excel not all fields are required hence after sorting delete the extra fields, vary for each courier data.

Hoping to get your reply buddy... thanks for everything.....