automate routine sorting activity using macro

[kiranpat]

Hi all,

I am working on a project where I am required to audit invoices based on certain parameters.
I have listed the parameters on the attached spreadsheet.

I get a voluminous data running to several thousands of line per day and I need to choose only those lines that satisfy the given parameter.

my parameters are as below
1. all telecom invoices require audit (100%)
2. All invoices whose value is less than zero require 100% audit
3. all invoices with payment method as Wire - Direct require 100% audit
4. all invoices whose invoice amount is greater than 30000 require audit
5. All duplicate URN need 100% audit (sorting based on URN column on result sheet.

I tried to record this macro but not able get the desired output.

Could someone please help me with this?

Regards,
Kiran

[Alf]

Have you considered using "Auto sort"? You could have a macro looping through a number of sorting possibilities.

Alf

[kiranpat]

I havent tried that as I am not familier with macro.
I know only to record a macro....

Pleae assist.

[kiranpat]

any help in automating this is greately appriciated

Thanks

[mehmetcik]

Ok

I mave given you a few formulae to get you started.

o2 =IF(AND(ISNA(MATCH(N2,N3:N50000,0)),ISNA(MATCH(N2,N$1:N1,0))),0,1)
This searches for duplicates down and up ( Upto Line 50000 )

p2 =IF(ISNA(MATCH(M2,telecoms,0)),0,1)
searches the keyword telecoms for the word in field m2, keyword ={"AT&T","Bell","Telephone","PABX"}

modify the keyword to include more suppliers and telecoms terms

q2 =IF(J2<0,1,0)
checks your for credit

r2 =IF(K2="wire",1,0)

s2 =IF(J2>=30000,1,0)
checks for high value

T2 could have been =if(isna(MATCH(1,O3:S3,0)),0,1)
but =IF(AND(ISNA(MATCH(N2,N3:N50000,0)),ISNA(MATCH(N2,N$1:N1,0)),ISNA(MATCH(M2,telecoms,0)),K2<>"wire",J2>=0,J2<30000),0,1)
compresses all the above lines into one.

U2 lists all the audit categories.
=IF(O2=0,"",O$1&" ")&IF(P2=0,"",P$1&" ") & IF(Q2=0,"",Q$1&" ") & IF(R2=0,"",R$1&" ") & IF(S2=0,"",S$1&" ")

Cogitate on that and I will prepare and post a macro to insert these formulae automatically.

[mehmetcik]

Ok

two new macros for you

Try_me and Optimise

Try_me inserts your formulae

Optimise speeds up your macro

remove the ' before 'optimise in tryme to enable optimise.

enjoy

[mehmetcik]

Sorry use this instead

[kiranpat]

thanks a bunch for some roboust formule... they are really good

here are my suggestions and requirements

1. Formula for N2 is perfect
2. O2 is perfect
3. P2 needs to be modified as the its base information is in column M and if the data in column M starts with string "ACC" it is deemed to be telcom invoice
4. Q2 is fine
5. R2 is OK
6. S2 is OK
7. T2 is OK
8. U2 should list only one category and in case of multiple categories turning true, only the first one that turns tru shoul be inticated. I have listed the catogories in hierarcial order.

please let me know if you need any other information.

Your help is highly appriciated...

Regards,
kiran

[mehmetcik]

Ok

Try This

[mehmetcik]

If this is ok

Then the next stage is to sort your data to bring all the invoices requiring an audit to the top

conffirm this is what you need an I will create the macro in 20 mins,

[kiranpat]

awwa.... this is great.... I have attached a sample of my version and need the data to be populated in a new sheet named "Audit Report"&date and time.

please use your latest formulae for column U.

the output data should contain column A to N and T and U. (format as shown on the result sheet)

Also the formulae in the master sheet should vanish..

Thanks again... you have been of great help and really a good learnig of excel formula usage...

[mehmetcik]

I have played with this a bit.

only Acc is recognised as a telecom invoice is this right?

try this and let me know

[kiranpat]

This makes the tool more user friendly... thanks

yes, Telecom invoice is recoznized by string "ACC" in column M.
also, I noticed that formula for URN (column N) is not being populated. this is the key for sorting tha data as sorting based on URN
will help to get the duplicate URNs one below the other.

The formula for URN (a ten digit number) calculation is =IFERROR(IF(TYPE(LEFT(RIGHT(L2,25),10)*1)=1,LEFT(RIGHT(L2,25),10),""),"")

This derrived from column L

I need the audit required "yes" data in seperate sheet.
data required is Column A to N and T to U on seperate sheet.

Once required data is moved to the result sheet, the calculations on base sheet should vanish.

[kiranpat]

first of all, sorry for being a pest.... the validation part is growing intersting... may be because I did not list the requirments
and conditions clearly at the first go...

Also, I noticed that if any of the column L is blank (this field may have blanks or non numeric data which leads to column N yeilding
a blank)the current formula is flagging it for duplicate URN as YES which shoul not be happeing...

[mehmetcik]

Ok

This is my final input.

Enjoy.