I don't know if I've been looking at this too long but I need help completing the ">7 Invoice" tab and the "Duplicates Tab"

I need this by 1/15 at 11:50 pm

HELP!!!

Aging Schedule Audit for Year Ended 3/31/2015 VENDOR_NO INVOICE_DATE 1663 3808 2433 1663 2130 3411 2433 721 2433 3411 787 534 4913 534 2433 1435 2636 4438 134 2130 1837 2701 134 101 787 1663 1922 1475 1663 2701 3411 1475 4438 2433 2701 1475 1837 2433 1922 134 448 879 3411 3136 2701 25 12/20/2014 12/29/2014 12/6/2014 11/13/2014 11/7/2014 11/24/2014 11/13/2014 12/3/2014 11/8/2014 12/26/2014 1/2/2015 1/24/2015 1/15/2015 1/12/2015 1/6/2015 1/13/2015 1/10/2015 1/9/2015 1/26/2015 1/7/2015 1/17/2015 1/29/2015 1/3/2015 1/30/2015 1/24/2015 1/14/2015 1/12/2015 1/4/2015 1/2/2015 1/29/2015 1/28/2015 1/16/2015 1/11/2015 1/14/2015 2/24/2015 3/2/2015 1/31/2015 2/24/2015 2/12/2015 2/26/2015 2/3/2015 2/1/2015 2/26/2015 2/16/2015 2/26/2015 2/13/2015 Page 1 INVOICE_NO 8180759 7530122 8567365 8394759 891345 45947495 8595165 231961 8588065 40141495 915335 20114587 5723084 24783587 8815565 28931543 65082694 968772 71073 811195 1440522 325562 74841 1760445 942725 8681159 873209 5387187 8362159 321622 48491495 5393587 902542 8682565 375412 5251287 1333722 8935565 867679 70075 5260926 1515795 40683495 193835 375362 340562 Aging Schedule 25 134 721 1837 101 25 787 4438 2130 4438 1475 721 1475 787 1435 134 448 1475 1009 1922 25 3411 2130 1475 4438 25 2701 1435 448 721 3136 4438 101 2636 2248 2576 559 3928 2230 2576 3373 720 2248 720 2289 3864 4299 3928 559 3864 2/11/2015 2/7/2015 3/2/2015 2/7/2015 2/17/2015 3/2/2015 2/8/2015 1/31/2015 2/6/2015 2/2/2015 2/16/2015 2/4/2015 3/1/2015 2/27/2015 2/12/2015 3/2/2015 2/23/2015 3/1/2015 3/20/2015 3/27/2015 3/10/2015 3/8/2015 3/28/2015 3/18/2015 3/3/2015 3/19/2015 3/7/2015 3/20/2015 2/28/2015 3/12/2015 3/3/2015 3/6/2015 3/20/2015 3/6/2015 3/18/2015 3/3/2015 3/9/2015 3/7/2015 3/12/2015 3/22/2015 3/23/2015 3/16/2015 3/12/2015 3/12/2015 3/28/2015 3/12/2015 3/23/2015 3/23/2015 3/9/2015 3/14/2015 Page 2 305922 78025 240861 1057122 1474245 392152 973805 937322 887825 927102 5717087 210531 5498387 941815 26778543 70936 5062026 5238387 26530 827569 379362 45765495 843035 5340187 946982 321952 393882 24133543 5386426 220881 175065 936392 1605045 69398694 9006855 9710736 8631735 5763457 74284 9091836 7434343 60749 9713655 68010 7311454 57163106 92576109 5422657 8384735 50602106 Aging Schedule 4090 3928 1247 4599 3440 1247 2576 559 3928 2230 2576 3373 720 2248 720 2289 3864 4299 3928 559 3864 4090 3928 1247 4599 1837 2433 1922 134 448 879 3411 3136 2701 25 25 134 721 1837 101 3/26/2015 3/5/2015 3/22/2015 3/14/2015 3/21/2015 3/2/2015 3/3/2015 3/12/2015 3/4/2015 3/13/2015 3/7/2015 3/29/2015 3/10/2015 3/21/2015 3/26/2015 3/2/2015 3/24/2015 3/24/2015 3/21/2015 3/6/2015 3/30/2015 3/6/2015 3/30/2015 3/22/2015 3/28/2015 2/28/2015 3/13/2015 3/23/2015 3/27/2015 3/14/2015 3/10/2015 3/17/2015 3/4/2015 3/13/2015 3/14/2015 3/9/2015 3/8/2015 3/25/2015 3/25/2015 3/17/2015 3579998 5829657 7189965 9363654 1891255 7204365 9723240 8635870 5772166 78410 9105366 7453636 68811 9736424 70249 7316429 57176249 92589424 5443270 8400885 50605959 3596339 5840542 7202262 9364843 1344035 8949226 873060 73866 5263477 1537936 40696592 200971 395701 353800 327044 82790 264376 1080924 1490052 TOTAL Page 3 Aging Schedule Formulas Used for aging= INVOICE_AMOUNT =$B$1-B3' "=IF($E330,$E360,$E390,$D3,"-")" 31-60 DAYS 61-90 DAYS OVER 90 DAYS $323.40 $5,446.27 $4,475.74 $455.39 $432.54 $939.47 $113.18 $2,448.00 $575.11 $91.45 $217.53 $588.06 $1,837.91 $212.52 -$4,037.04 $5,837.40 $1,218.00 $1,065.72 $2,077.80 $1,405.95 $1,527.59 $4,470.25 $16,300.28 $3,984.45 $32,857.01 $986.58 $1,260.14 $1,091.44 $718.63 $36.12 $52.53 $1,615.35 $3,338.09 $101.57 $5,551.05 - $840.89 $9,923.58 $8,432.79 $9,595.54 $11,163.61 $10,091.84 $4,567.68 $320.88 $432.00 $29.31 - Page 7 Aging Schedule $1,437.48 $2,480.20 $1,421.15 $63.50 $393.81 $881.89 $25,638.37 $8,286.62 $1,436.23 $490.77 $2,818.38 $823.45 $654.37 $213.86 - - Page 8 - Aging Schedule $4,675.55 - $67,821.77 $83,509.34 Page 9 $55,398.12 Aging Schedule Page 10 Aging Schedule Page 11 Aging Schedule Page 12 Audit for Year Ended VENDOR 1663 3808 2433 1663 2130 3411 2433 721 2433 3411 3/31/2015 INVOICE_NO 41993 42002 41979 41956 41950 41967 41956 41976 41951 41999 INVOICE_DATE 12/20/2014 12/29/2014 12/6/2014 11/13/2014 11/7/2014 11/24/2014 11/13/2014 12/3/2014 11/8/2014 12/26/2014 Create a new table using the join table function in INVOICE_AMOUNT 840.89 9,923.58 8,432.79 9,595.54 11,163.61 10,091.84 4,567.68 320.88 432.00 29.31 DAYS_OS 101 92 115 138 144 127 138 118 143 95 Calculate days outstanding based on year ended 3/ this was caclulated on the "Aging Schedule" On th Create a filter for days outstanding greater than 90 "IF($A3=" "," ",$B$1-$C3)" this will caclulate only if Also the columns A-D only fill in if "Days_OS > 90 U . The "!A3" changes for the other columns =IF('Aging Schedule'!$E3>90,'Aging Schedule'!A3," join table function in ACL that includes the information in columns A-D ased on year ended 3/31/2015 ging Schedule" On this >90 the "Days_OS will only fill in if its > 90 nding greater than 90 days this will caclulate only if Columns A-D have info in them in if "Days_OS > 90 Using formula below for Vendor. ther columns ,'Aging Schedule'!A3," ")' >7 invoices Audit for Year Ended VENDOR_NO 3/31/2015 INVOICE_DATE INVOICE_NO Hint: you can use the aging schedule and use the subtotal formula in excel to count by vendor no. CHERYL: I have no idea how to do this or what they want to show up Page 25 INVOICE_AMOUNT >7 invoices Page 26 >7 invoices TOTAL Page 27 0 >7 invoices DAYS_OUTSTANDING 03/31/15 03/31/15 03/31/15 03/31/15 03/31/15 03/31/15 03/31/15 count how many times vendor shows in vendor list so it count Che 4 In t 1 form 7 to c num 4 how 4 the 6 aut 7 form 5 7 6 DIRECTIONS: Use formula to 4 ON THE >7 TAB, PREP calculate days 2 with seven or more ou outstanding based on 1 the year ended date 2 of 3/31/15 7 3 2 6 7 4 5 6 7 4 4 4 4 7 4 6 6 7 6 7 6 7 5 7 4 7 4 2 6 3 6 7 7 7 Page 28 >7 invoices 5 5 4 7 4 6 4 6 7 5 7 4 3 7 4 7 1 4 7 6 4 7 6 7 6 3 4 5 3 6 4 2 3 4 4 6 2 4 2 4 3 4 2 4 2 6 4 4 2 6 Page 29 >7 invoices 3 2 1 3 4 4 6 2 4 2 4 3 4 2 4 2 6 4 4 2 6 3 2 5 7 4 7 4 2 6 3 6 7 7 7 5 5 4 Page 30 >7 invoices vendor list so it counts the number of invoices each vendor has Cheryl: In this column this formula I Put in to count dup vendor numbers but don't know how to have the rest of the information in col A-D auto fill in with a subtotal formula DIRECTIONS: ON THE >7 TAB, PREPARE A TABLE that indicates vendors with seven or more outstanding invoices Page 31 >7 invoices Page 32 >7 invoices Page 33 Audit for Year Ended VENDOR 3/31/2015 INVOICE_NO INVOICE_DATE INVOICE_AMOUNT DAYS_OS NO IDEA WHAT THEY ARE ASKING FOR. DIRECTIONS: On the duplicates tab, explain how the duplicates can be found in the Trans Table Cheryl: I don't think they are looking for me to actually do anything but explain this but Im not sure what they are asking for sincethe columns A-E are part of the file they provided. Maybe I'm supposed to have a list of duplicate vendors and all their invoices but not sure Use conditional formatting Y ARE ASKING FOR. ab, explain how the duplicates can be found in the Trans Table Cheryl: I don't think they are looking for me to actually do anything but explain this but Im not sure what they are asking for sincethe columns A-E are part of the file they provided. Maybe I'm supposed to have a list of duplicate vendors and all their invoices but not sure