Macro Automation, even when formats change

[lookupyourv]

Hello All,

I'm new to this forum so please take it easy on me and thank you in advance to anyone looking to help.

I recieve sales data from my wholesalers every month and I continually have to format them to fit the structure of our in-house database. I wanted to design a macro that would automate this process. However, in some months, the files are recieved in a format that is a bit different from the wholesaler's usual format.

Anyone have any suggestions on automating this process?

Is there such thing as an initial "litmus" test where I could try running the macro and if it doesn't fit the usual structure, there's an error code and I could do it by hand?

Any other suggestions? Anything helps!

[ARGK]

Hi
If possible could we see a sample of the data you get from your supplier exactly as you get it? This will help us consider the way to help you.
Tony

[lookupyourv]

One example is a list that may come in like this:


CITY ST ZIP DESCRIPTION QTY PRICE UNIT EXT PRICE Invoice Date
FARGO ND 58102 TAPE 3/4 X 60" 10 $2.45 EA $24.50 5/1/2014
ALBANY MN 56307 TAPE 1 1/2 X 44" VINYL TELE 4 $2.51 EA $10.04 5/1/2014
ALBANY MN 56307 TAPE 3/4 X 60" 20 $2.45 EA $49.00 5/1/2014
ERSKINE MN 56535 2 PORT SPLICE CONN 100/BX 10000 $6.38 C $638.00 5/1/2014
ERSKINE MN 56535 KIT SPLICE FOR 50PR 1 1/2" 18 $34.02 EA $612.36 5/1/2014
Alexandria MN 56308 3 PORT SPLICE CONN 100/BX 100 $7.31 C $7.31 5/1/2014
FARGO ND 58102 TAPE 3/4 X 60" 20 $2.45 EA $49.00 5/2/2014
PERHAM MN 56573 2 PORT SPLICE CONN 100/BX 3000 $6.38 C $191.40 5/2/2014
FLASHER ND 58535 CONN SHLD BOND FOR WIRE 600 $0.69 EA $414.00 5/2/2014
WILLISTON ND 58801 REENTERABLE ENCAPSULANT 1500 GM 12 $23.38 EA $280.56 5/2/2014
WILLISTON ND 58801 REENTERABLE ENCAPSULANT 3000 GM 13 $47.23 EA $613.99 5/2/2014
ANNANDALE MN 55302 SPLICE UNIV OPTICAL FBR 24 $7.32 EA $175.68 5/2/2014
SIOUX FALLS SD 57104 TAPE 3/4 X 60" 10 $2.45 EA $24.50 5/2/2014
Bemidji MN 56601 3 PORT SPLICE CONN 100/BX 100 $7.31 C $7.31 5/2/2014
KENNEBEC SD 57544 3 PORT SPLICE CONN 100/BX 1000 $7.31 C $73.10 5/5/2014
WILLISTON ND 58801 MARKER BALLS 4" ORG EMS EXTENDED 60 $6.70 EA $402.00 5/5/2014
BRANDON SD 57005 TAPE 3/4 X 60" 10 $2.45 EA $24.50 5/5/2014
PIERRE SD 57501 TAPE 3/4 X 60" 2 $2.45 EA $4.90 5/5/2014
GETTYSBURG SD 57442 3 PORT SPLICE CONN 100/BX 200 $7.31 C $14.62 5/5/2014
RAPID CITY SD 57703 TAPE 1 1/2 X 44" VINYL TELE 1 $2.51 EA $2.51 5/5/2014
MITCHELL SD 57301 2 PORT SPLICE CONN 100/BX 1000 $6.38 C $63.80 5/6/2014
PERHAM MN 56573 TAPE 1 1/2 X 44" VINYL TELE 100 $2.51 EA $251.00 5/6/2014
DEVILS LAKE ND 58301 3 PORT SPLICE CONN 100/BX 200 $7.31 C $14.62 5/6/2014
FARGO ND 58102 3 PORT SPLICE CONN 100/BX 100 $7.31 C $7.31 5/6/2014
BISMARCK ND 58501 CONN ST MULTI MODE HOT MELT 83 $4.15 EA $344.45 5/6/2014
PLATTE SD 57369 CONN SHLD BOND FOR WIRE 1000 $0.69 EA $690.00 5/6/2014
WILLISTON ND 58801 MARKER BALLS 4" ORG EMS EXTENDED 60 $6.70 EA $402.00 5/6/2014
SEBEKA MN 56477 TAPE 3/4 X 60" 1 $2.45 EA $2.45 5/6/2014
WEST UNION IA 52175 3 PORT SPLICE CONN 100/BX 3 $8.11 C $0.24 5/6/2014
PIERRE SD 57501 TAPE 3/4 X 60" 1 $2.45 EA $2.45 5/7/2014
HOFFMAN MN 56339 CONN SHLD BOND FOR 395 $0.79 EA $312.05 5/7/2014
Alexandria MN 56308 TAPE 3/4 X 60" 2 $2.45 EA $4.90 5/7/2014
FLASHER ND 58535 CONN SHLD BOND FOR WIRE 1000 $0.69 EA $690.00 5/7/2014
ELKADER IA 52043 3 PORT SPLICE CONN 100/BX 100 $8.11 C $8.11 5/7/2014
ABERDEEN SD 57401 TIE CBL & BASE ASSY TIE 7.18 100 $20.00 C $20.00 5/8/2014
FARGO ND 58102 KIT CBL ADDITION FOR 2178L/S 1 $73.51 EA $73.51 5/8/2014
PIERRE SD 57501 TAPE 3/4 X 60" 1 $2.45 EA $2.45 5/8/2014
MITCHELL SD 57301 2 PORT SPLICE CONN 100/BX 100 $6.38 C $6.38 5/8/2014
STANLEY ND 58784 TAPE 3/4 X 60" 60 $2.45 EA $147.00 5/8/2014
FARGO ND 58102 3 PORT SPLICE CONN 100/BX 100 $7.31 C $7.31 5/8/2014
KINDRED ND 58051 3 PORT SPLICE CONN 100/BX 200 $7.31 C $14.62 5/8/2014
FARGO ND 58102 KIT DBL ADPTR BLT FOR 2 1 $42.40 EA $42.40 5/8/2014
FARGO ND 58102 KIT CBL ADDITION FOR 2178L/S 1 $73.51 EA $73.51 5/8/2014
La Crosse WI 54603 3 PORT SPLICE CONN 100/BX 1000 $8.11 C $81.10 5/8/2014
PERHAM MN 56573 TAPE 3/4 X 60" 200 $2.45 EA $490.00 5/9/2014
PERHAM MN 56573 2 PORT SPLICE CONN 100/BX 6000 $6.38 C $382.80 5/9/2014
PERHAM MN 56573 3 PORT SPLICE CONN 100/BX 6000 $7.31 C $438.60 5/9/2014
ERSKINE MN 56535 KIT SPLICE FOR 50PR 1 1/2" 2 $34.02 EA $68.04 5/9/2014
HALSTAD MN 56548 TAPE 3/4 X 60" 20 $2.45 EA $49.00 5/9/2014
SEBEKA MN 56477 TAPE 3/4 X 60" 10 $2.45 EA $24.50 5/9/2014
WINONA MN 55987-4952 3 PORT SPLICE CONN 100/BX 100 $8.11 C $8.11 5/9/2014
ALBANY MN 56307 TAPE 1 1/2 X 44" VINYL TELE 4 $2.51 EA $10.04 5/12/2014
WORDEN MT 59088 3M710-TMK10-A1 3M TOOL BASE 1 $56.97 EA $56.97 5/12/2014
WORDEN MT 59088 4M710-TMK10-A2 3M TOOL CLAMP 1 $110.45 EA $110.45 5/12/2014
ABERCROMBIE ND 58001 TAPE 3/4 X 60" 40 $2.45 EA $98.00 5/12/2014
HERREID SD 57632 TAPE 3/4 X 60" 100 $2.45 EA $245.00 5/12/2014
DEVILS LAKE ND 58301 0180 TAPE 3/4 X 60" 100 $2.45 EA $245.00 5/13/2014
FARGO ND 58102 3 PORT SPLICE CONN 100/BX 100 $7.31 C $7.31 5/13/2014
WILLISTON ND 58801 REENTERABLE ENCAPSULANT 1500 GM 8 $23.38 EA $187.04 5/13/2014
WILLISTON ND 58801 REENTERABLE ENCAPSULANT 3000 GM 37 $47.23 EA $1,747.51 5/13/2014
HOFFMAN MN 56339 2 PORT SPLICE CONN 100/BX 1000 $6.38 C $63.80 5/14/2014
HOFFMAN MN 56339 CONN SHLD BOND FOR 605 $0.79 EA $477.95 5/14/2014
WILLISTON ND 58801 TAPE 1" X 36' 30 $1.67 EA $50.10 5/14/2014
WILLISTON ND 58801 TAPE 1 1/2 X 44" VINYL TELE 30 $2.51 EA $75.30 5/14/2014
WILLISTON ND 58801 TAPE 3/4 X 60" 30 $2.45 EA $73.50 5/14/2014
LA CROSSE WI 54603-2940 3 PORT SPLICE CONN 100/BX 200 $8.11 C $16.22 5/14/2014
ALBANY MN 56307 KIT SPLICE FOR 50PR 1 1/2" 8 $34.02 EA $272.16 5/15/2014
FARGO ND 58102 KIT SPLICE FOR 50PR 1 1/2" 10 $34.02 EA $340.20 5/15/2014
CARRINGTON ND 58421 TAPE 3/4 X 60" 30 $2.45 EA $73.50 5/15/2014
LINCOLN MT 59639 TAPE 3/4 X 60" 100 $2.45 EA $245.00 5/15/2014
SCOBEY MT 59263 3M 2273-U3T3 LOCATOR 1 $2,873.17 EA $2,873.17 5/15/2014
GLEN ULLIN ND 58631 3 PORT SPLICE CONN 100/BX 2000 $7.31 C $146.20 5/15/2014
BINFORD ND 58416 TAPE 3/4 X 60" 10 $2.45 EA $24.50 5/15/2014
PILLAGER MN 56473 TAPE 1 1/2 X 44" VINYL TELE 20 $2.51 EA $50.20 5/15/2014
PILLAGER MN 56473 TAPE 3/4 X 60" 40 $2.45 EA $98.00 5/15/2014
AMERY WI 54001-0139 TAPE 3/4 X 60" 20 $2.45 EA $49.00 5/15/2014
SOMERSET WI 54025 TAPE 3/4 X 60" 10 $2.45 EA $24.50 5/15/2014
MITCHELL SD 57301 2 PORT SPLICE CONN 100/BX 100 $6.38 C $6.38 5/16/2014
ABERDEEN SD 57401 TIE CBL & BASE ASSY TIE 7.18 100 $20.00 C $20.00 5/16/2014
FARGO ND 58102 TAPE 1 1/2 X 44" VINYL TELE 180 $2.51 EA $451.80 5/16/2014
GRAND FORKS ND 58201 TAPE 3/4 X 60" 2 $2.45 EA $4.90 5/16/2014
PERHAM MN 56573 2 PORT SPLICE CONN 100/BX 3000 $6.38 C $191.40 5/16/2014
PERHAM MN 56573 CONN SHLD BOND FOR WIRE 600 $0.69 EA $414.00 5/16/2014
FARGO ND 58104-6922 CLOTH LINT FREE BAG OF 100 4 $10.25 EA $41.00 5/16/2014
FARGO ND 58104-6922 2 PORT SPLICE CONN 100/BX 1000 $6.38 C $63.80 5/16/2014
ROTHSAY MN 56579 CONN SHLD BOND FOR WIRE 100 $0.69 EA $69.00 5/16/2014
ROTHSAY MN 56579 TAPE 1" X 36' 8 $1.67 EA $13.36 5/16/2014
ABERDEEN SD 57401 3 PORT SPLICE CONN 100/BX 100 $7.31 C $7.31 5/16/2014
FARGO ND 58104-6922 CONN SC HOT MELT S/MODE 120 $6.06 EA $727.20 5/19/2014
SCOBEY MT 59263 CONN SHLD BOND FOR WIRE 200 $0.69 EA $138.00 5/19/2014
SEBEKA MN 56477 CONN SHLD BOND FOR WIRE 200 $0.69 EA $138.00 5/19/2014
SEBEKA MN 56477 TAPE 3/4 X 60" 30 $2.45 EA $73.50 5/19/2014
WILLISTON ND 58801 TAPE 1" X 36' 100 $1.67 EA $167.00 5/19/2014
WILLISTON ND 58801 TAPE 1 1/2 X 44" VINYL TELE 100 $2.51 EA $251.00 5/19/2014
WILLISTON ND 58801 TAPE 3/4 X 60" 100 $2.45 EA $245.00 5/19/2014
STANLEY ND 58784 3 PORT SPLICE CONN 100/BX 500 $7.31 C $36.55 5/19/2014
DEVILS LAKE ND 58301 0180 CONN SHLD BOND FOR 200 $0.79 EA $158.00 5/20/2014
MELROSE MN 56352 CONN SHLD BOND FOR 100 $0.79 EA $79.00 5/20/2014
MELROSE MN 56352 TAPE 1 1/2 X 44" VINYL TELE 100 $2.51 EA $251.00 5/20/2014
MELROSE MN 56352 TAPE 3/4 X 60" 100 $2.45 EA $245.00 5/20/2014
MELROSE MN 56352 REENTERABLE ENCAPSULANT 1500 GM 6 $23.38 EA $140.28 5/20/2014
MELROSE MN 56352 REENTERABLE ENCAPSULANT 3000 GM 6 $47.23 EA $283.38 5/20/2014
MELROSE MN 56352 REENTERABLE ENCAPSULANT 750 GM 6 $13.76 EA $82.56 5/20/2014
MELROSE MN 56352 SPLICE CLSR KIT 18PR 0.90" 3 $25.59 EA $76.77 5/20/2014
MELROSE MN 56352 SPLICE CLSR KIT 50PR 1.25" 6 $40.98 EA $245.88 5/20/2014
MELROSE MN 56352 SPLICE CLSR KIT 100PR 2.25" 6 $78.00 EA $468.00 5/20/2014
MELROSE MN 56352 SPLICE CLSR KIT 400PR 3.50" 3 $136.04 EA $408.12 5/20/2014
FARGO ND 58102 2 PORT SPLICE CONN 100/BX 100 $6.38 C $6.38 5/20/2014
BISMARCK ND 58501 3 PORT SPLICE CONN 100/BX 100 $7.31 C $7.31 5/20/2014
MITCHELL SD 57301 2 PORT SPLICE CONN 100/BX 1000 $6.38 C $63.80 5/21/2014
MITCHELL SD 57301 TAPE 3/4 X 60" 3 $2.45 EA $7.35 5/21/2014
BISMARCK ND 58501 CONN ST MULTI MODE HOT MELT 36 $4.15 EA $149.40 5/21/2014
WILLISTON ND 58801 2 PORT SPLICE CONN 100/BX 100 $6.38 C $6.38 5/21/2014
WILLISTON ND 58801 3 PORT SPLICE CONN 100/BX 100 $7.31 C $7.31 5/21/2014
LA CROSSE WI 54603-2940 3 PORT SPLICE CONN 100/BX 100 $8.11 C $8.11 5/21/2014
HOFFMAN MN 56339 CONN SHLD BOND FOR WIRE 1000 $0.69 EA $690.00 5/22/2014
RICE LAKE WI 54868 TAPE 3/4 X 60" 10 $2.45 EA $24.50 5/22/2014
HALSTAD MN 56548 2 PORT SPLICE CONN 100/BX 1000 $6.38 C $63.80 5/22/2014
FARGO ND 58102 3 PORT SPLICE CONN 100/BX 300 $7.31 C $21.93 5/22/2014
La Crosse WI 54603 3 PORT SPLICE CONN 100/BX 297 $8.11 C $24.09 5/22/2014
STUART NE 68780 CONN SHLD BOND FOR WIRE 600 $0.69 EA $414.00 5/23/2014
SCOBEY MT 59263 2178-XSB-EG-1T 3M FIBER CASE 5 $112.81 EA $564.05 5/23/2014
MITCHELL SD 57301 SHIELD BOND CONN 10 $0.77 EA $7.70 5/23/2014
AMERY WI 54001-0139 CONN SHLD BOND FOR 100 $0.96 EA $96.00 5/23/2014
BRANDON SD 57005 TAPE 3/4 X 60" 5 $2.45 EA $12.25 5/23/2014
ABERDEEN SD 57401 2 PORT SPLICE CONN 100/BX 100 $6.38 C $6.38 5/23/2014

[JOHN H. DAVIS]

What is the structure of your in-house database?