Inventory Maco help needed.....

C

chefdt

Board Regular
Joined
Jul 1, 2008
Messages
163
I have a WORKBOOK named "Inventory". It consists of several WORKSHEETS that divide my food inventory into categories such as "MEAT", "DAIRY PRODUCTS", etc.

I recorded a macro that will import my inventory from an online ordering system, dump it to a WORKSHEET named "IMPORT" and normalize the data.

I want to create a macro that looks through the import data and parses it into the appropriate WORKSHEET.

Column "I" of the "IMPORT" sheet contains the name of the WORKSHEET that the data needs to be copied to.

The other worksheets will already have data in them, starting at "A8". As each item is copied to the appropriate sheet, I want to verify if the item already exists on the sheet and do one of two options.....A) If it exists, copy only "H" which is the case cost, and flag by color or bold text if the new price is different from the current B) If it doesn't exist, add the entire row of data to the bottom row of data in the appropriate sheet.

Here is a sample of "IMPORT" and "DAIRY"

Dairy Products

ABCDEFGHIJKLM
BUTTERMILK 1% LOW FAT
CHEESE AMER YEL 160 SLI
CHEESE CHDR YEL MILKD SHRD FTHR
CHEESE CUBE HAVARTI/GOUDA/MUFN

<colgroup> <col style="WIDTH: 30px; FONT-WEIGHT: bold"> <col style="WIDTH: 49px"> <col style="WIDTH: 209px"> <col style="WIDTH: 61px"> <col style="WIDTH: 35px"> <col style="WIDTH: 71px"> <col style="WIDTH: 73px"> <col style="WIDTH: 81px"> <col style="WIDTH: 54px"> <col style="WIDTH: 30px"> <col style="WIDTH: 30px"> <col style="WIDTH: 54px"> <col style="WIDTH: 48px"> <col style="WIDTH: 54px"></colgroup> <tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #c0c0c0, colspan: 2, align: right"]Todays Date:[/TD]
[TD="bgcolor: #c0c0c0, colspan: 2, align: center"]10/31/2013[/TD]

[TD="bgcolor: #c0c0c0, colspan: 2, align: center"]Total:[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #c0c0c0, colspan: 2, align: right"]Date of Last Inventory:[/TD]
[TD="bgcolor: #c0c0c0, colspan: 2, align: center"]3/12/2010[/TD]

[TD="bgcolor: #c0c0c0, colspan: 2, align: center"]$ - [/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #c0c0c0, colspan: 2, align: right"]Days Since Last Inventory:[/TD]
[TD="bgcolor: #c0c0c0, colspan: 2, align: center"]1329[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #c0c0c0, colspan: 2, align: right"]Last Person to Inventory:[/TD]
[TD="bgcolor: #c0c0c0, colspan: 2"] [/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="colspan: 3"] [/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="bgcolor: #800000, align: center"]SUPC[/TD]
[TD="bgcolor: #800000, align: center"]Item Description[/TD]
[TD="bgcolor: #800000, align: center"]Brand[/TD]
[TD="bgcolor: #800000, align: center"]Count[/TD]
[TD="bgcolor: #800000, align: center"]Package Size[/TD]
[TD="bgcolor: #800000, align: center"]Mfg #[/TD]
[TD="bgcolor: #800000, align: center"]Re-order Count[/TD]
[TD="bgcolor: #800000, align: center"]Case Cost[/TD]
[TD="bgcolor: #800000, align: center"]Case[/TD]
[TD="bgcolor: #800000, align: center"]Each[/TD]
[TD="bgcolor: #969696, align: center"]Unit Cost [/TD]
[TD="bgcolor: #969696, align: center"]Units in Stock [/TD]
[TD="bgcolor: #969696, align: center"]Total $ [/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: center"]8965881[/TD]

[TD="align: left"]WHLFARM[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"].5 GAL[/TD]

[TD="align: right"]$ 19.05 [/TD]

[TD="bgcolor: #ccccff, align: center"]$ 2.12 [/TD]
[TD="bgcolor: #ccccff, align: right"] [/TD]
[TD="bgcolor: #ccccff, align: right"]$ - [/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="bgcolor: #d9d9d9, align: center"]6697890[/TD]

[TD="align: left"]BBRLCLS[/TD]
[TD="align: center"]4[/TD]
[TD="bgcolor: #d9d9d9, align: center"]5 LB[/TD]
[TD="bgcolor: #d9d9d9"] [/TD]
[TD="bgcolor: #d9d9d9"] [/TD]
[TD="bgcolor: #d9d9d9, align: right"]$ 37.68 [/TD]

[TD="bgcolor: #d9d9d9"] [/TD]
[TD="bgcolor: #d9d9d9, align: center"]$ 9.42 [/TD]
[TD="bgcolor: #d9d9d9, align: right"] [/TD]
[TD="bgcolor: #d9d9d9, align: right"]$ - [/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="align: center"]2404135[/TD]

[TD="align: left"]CASASOL[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5 LB[/TD]

[TD="align: right"]$ 45.80 [/TD]

[TD="align: center"]$ 11.45 [/TD]
[TD="bgcolor: #ccccff, align: right"] [/TD]
[TD="bgcolor: #ccccff, align: right"]$ - [/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="bgcolor: #d9d9d9, align: center"]7234958[/TD]
[TD="bgcolor: #d9d9d9"]CHEESE CUBE CHDR/COJCK/PEP JCK[/TD]
[TD="bgcolor: #d9d9d9, align: left"]BBRLIMP[/TD]
[TD="bgcolor: #d9d9d9, align: center"]3[/TD]
[TD="bgcolor: #d9d9d9, align: center"]5 #[/TD]
[TD="bgcolor: #d9d9d9"] [/TD]

[TD="bgcolor: #d9d9d9, align: right"]$ 48.00 [/TD]
[TD="bgcolor: #d9d9d9"] [/TD]
[TD="bgcolor: #d9d9d9"] [/TD]
[TD="align: center"]$ 16.00 [/TD]
[TD="bgcolor: #d9d9d9, align: right"] [/TD]
[TD="bgcolor: #d9d9d9, align: right"]$ - [/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]
[TD="align: center"]4791386[/TD]

[TD="align: left"]SCHRBER[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]5 LB[/TD]

[TD="align: right"]$ 46.74 [/TD]

[TD="align: center"]$ 15.58 [/TD]
[TD="align: right"] [/TD]
[TD="bgcolor: #ccccff, align: right"]$ - [/TD]

</tbody>


Import

ABCDEFGHI
DescBrandCase $ Cat
CHEESE BLUE CRUMBLESSYS IMP Dairy Products
CHEESE BRIE DOMSTC 1 KGBBRLIMP Dairy Products
CHEESE CHDR JACK SHRD FCYCASASOL Dairy Products
CHEESE CHDR MILD YEL PRNTBBRLIMP Dairy Products
CHEESE CHDR SHRP PRNT YELBBRLIMP Dairy Products
CHEESE CHDR SMKDTILAMOK Dairy Products
CHEESE CHDR XSHRP WHITETILAMOK Dairy Products
CHEESE FETA CRUMBLE DOMSYS IMP Dairy Products

<colgroup> <col style="WIDTH: 30px; FONT-WEIGHT: bold"> <col style="WIDTH: 119px"> <col style="WIDTH: 332px"> <col style="WIDTH: 72px"> <col style="WIDTH: 35px"> <col style="WIDTH: 55px"> <col style="WIDTH: 63px"> <col style="WIDTH: 64px"> <col style="WIDTH: 63px"> <col style="WIDTH: 108px"></colgroup> <tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: left"]SUPC[/TD]

[TD="align: center"]Pack[/TD]
[TD="align: center"]Size[/TD]
[TD="align: center"]Mfr #[/TD]
[TD="align: center"]Par[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: left"]DAIRY PRODUCTS[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: left"]0671677[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]5 LB[/TD]
[TD="align: center"]10000847[/TD]

[TD="align: right"]$ 12.59 [/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: left"]5469259[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]2.2 LB[/TD]
[TD="align: center"]10007830[/TD]

[TD="align: right"]$ 17.28 [/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: left"]2406189[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]5 LB[/TD]
[TD="align: center"]10000253[/TD]

[TD="align: right"]$ 47.78 [/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: left"]2599793[/TD]

[TD="align: center"]1[/TD]
[TD="align: center"]10 LB[/TD]
[TD="align: center"]10000253[/TD]

[TD="align: right"]$ 23.70 [/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: left"]6159263[/TD]

[TD="align: center"]1[/TD]
[TD="align: center"]10 LB[/TD]
[TD="align: center"]10000253[/TD]

[TD="align: right"]$ 27.32 [/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: left"]2357598[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]2 LB[/TD]
[TD="align: center"]10007703[/TD]

[TD="align: right"]$ 61.70 [/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: left"]2357580[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]5 LB[/TD]
[TD="align: center"]10007703[/TD]

[TD="align: right"]$ 53.95 [/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="align: left"]2220143[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]5 LB[/TD]
[TD="align: center"]10000847[/TD]

[TD="align: right"]$ 36.21 [/TD]

</tbody>

Any help appreciated!
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
You must log in or register to reply here.

Similar threads

C
  • Question Question
Inventory Control help....
2
Replies
12
Views
2K
chefdt
C
C
  • Question Question
Import data for Inventory
Replies
0
Views
584
chefdt
C
C
  • Question Question
Copy from one worksheet to another based on cell value...
Replies
8
Views
1K
chefdt
C
C
  • Question Question
Inventory Macro - Copy rows to new worksheets
Replies
0
Views
630
chefdt
C

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top