need formula to pull info from another sheet

[Toney]

I need to pull prev yr sales data from a certain customer on a tab that has all customers data listed to another tab. I need to match customer # and item # to get correct specific info. Any suggestions?
[TABLE="class: grid, width: 1500"]
<tbody>[TR]
[TD="width: 64, align: left"]Item #[/TD]
[TD="width: 101, align: left"]cust #[/TD]
[TD="width: 101, align: left"]Customer name[/TD]
[TD="width: 134, align: left"]Description[/TD]
[TD="width: 64, align: left"]Packaging[/TD]
[TD="width: 64, align: left"]Case Qty[/TD]
[TD="width: 64, align: left"]Total Sales $[/TD]
[/TR]
[TR]
[TD="align: left"]100382[/TD]
[TD="align: left"]111111[/TD]
[TD="align: left"]SLAY'S FIREWORKS[/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]2044.62[/TD]
[/TR]
[TR]
[TD="align: left"]100748[/TD]
[TD="align: left"]111111[/TD]
[TD="align: left"]SLAY'S FIREWORKS[/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]3925[/TD]
[/TR]
[TR]
[TD="align: left"]200095[/TD]
[TD="align: left"]111111[/TD]
[TD="align: left"]SLAY'S FIREWORKS[/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]633.5[/TD]
[/TR]
[TR]
[TD="align: left"]100382[/TD]
[TD="align: left"]111112[/TD]
[TD="align: left"]ADAY's FIREWORKS[/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]237.56[/TD]
[/TR]
[TR]
[TD="align: left"]100748[/TD]
[TD="align: left"]111112[/TD]
[TD="align: left"]ADAY's FIREWORKS[/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]1411.12[/TD]
[/TR]
[TR]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Joyner]

Hello Toney,

It's hard to tell exactly what you need from the description. Can you provide what is on the other sheet and what the expected results are. I think either you need to use SUMIFS using both the Item# and Cust # or you need a lookup function concatenating the item and cust number, but again I am not sure.

 

[Toney]

here is the sheet I where I what to see the info from 1st sheet posted. see ???. I want to pull previous year's customer sales info for same item over as I am writing new order for customer.



[TABLE="width: 643"]
<colgroup><col width="83" style="width: 62pt; mso-width-source: userset; mso-width-alt: 3035;"> <col width="233" style="width: 175pt; mso-width-source: userset; mso-width-alt: 8521;"> <col width="76" style="width: 57pt; mso-width-source: userset; mso-width-alt: 2779;"> <col width="63" style="width: 47pt; mso-width-source: userset; mso-width-alt: 2304;"> <col width="118" style="width: 89pt; mso-width-source: userset; mso-width-alt: 4315;"> <col width="77" style="width: 58pt; mso-width-source: userset; mso-width-alt: 2816;"> <col width="86" style="width: 65pt; mso-width-source: userset; mso-width-alt: 3145;"> <col width="61" style="width: 46pt; mso-width-source: userset; mso-width-alt: 2230;"> <col width="59" style="width: 44pt; mso-width-source: userset; mso-width-alt: 2157;"> <tbody>[TR]
[TD="class: xl85, width: 83, bgcolor: #F8CBAD"]Acct #[/TD]
[TD="class: xl102, width: 233, bgcolor: transparent"]722353[/TD]
[TD="class: xl87, width: 76, bgcolor: #C6E0B4"]Type[/TD]
[TD="class: xl109, width: 63, bgcolor: #C6E0B4"]C[/TD]
[TD="class: xl78, width: 118, bgcolor: #DBDBDB"]CS Prod[/TD]
[TD="class: xl93, width: 77, bgcolor: #DBDBDB"] [/TD]
[TD="class: xl83, width: 86, bgcolor: #00B0F0"] Total W# [/TD]
[TD="class: xl98, width: 61, bgcolor: #00B0F0"] [/TD]
[TD="class: xl69, width: 59, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl86, bgcolor: #F8CBAD"]CustName[/TD]
[TD="class: xl86, bgcolor: #F8CBAD"]SLAY'S FIREWORKS[/TD]
[TD="class: xl92, bgcolor: #C6E0B4"] SA [/TD]
[TD="class: xl109, bgcolor: #C6E0B4"]245[/TD]
[TD="class: xl94, bgcolor: #DBDBDB"]CS Promo[/TD]
[TD="class: xl95, bgcolor: #DBDBDB"] [/TD]
[TD="class: xl96, bgcolor: #00B0F0"] # PALLETS [/TD]
[TD="class: xl99, bgcolor: #00B0F0"] [/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl85, bgcolor: #F8CBAD"]Date[/TD]
[TD="class: xl86, bgcolor: #F8CBAD"] [/TD]
[TD="class: xl88, bgcolor: #C6E0B4"]PL[/TD]
[TD="class: xl109, bgcolor: #C6E0B4"]L8[/TD]
[TD="class: xl79, bgcolor: #DBDBDB"] Total Cost CS [/TD]
[TD="class: xl81, bgcolor: #DBDBDB"] [/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl97, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl89, bgcolor: #B4C6E7"]RBU[/TD]
[TD="class: xl90, bgcolor: #B4C6E7"] [/TD]
[TD="class: xl87, bgcolor: #C6E0B4"]Terms[/TD]
[TD="class: xl109, bgcolor: #C6E0B4"]WNX[/TD]
[TD="class: xl80, bgcolor: #DBDBDB"] Total w/Prod Lia [/TD]
[TD="class: xl81, bgcolor: #DBDBDB"] [/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl97, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl89, bgcolor: #B4C6E7"]BP Ship[/TD]
[TD="class: xl91, bgcolor: #B4C6E7"] [/TD]
[TD="class: xl87, bgcolor: #C6E0B4"] [/TD]
[TD="class: xl87, bgcolor: #C6E0B4"] [/TD]
[TD="class: xl77, bgcolor: #92D050"] [/TD]
[TD="class: xl82, bgcolor: #92D050"] [/TD]
[TD="class: xl72, bgcolor: transparent"] Act Ship BP [/TD]
[TD="class: xl100, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl84, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] "]Order Notes[/TD]
[TD="class: xl118, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , colspan: 5"] [/TD]
[TD="class: xl69, bgcolor: transparent"]Order #[/TD]
[TD="class: xl97, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"] [/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl101, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl103, width: 76, bgcolor: transparent"]Tot CS Ord[/TD]
[TD="class: xl111, bgcolor: transparent"]L8[/TD]
[TD="class: xl103, width: 118, bgcolor: transparent"]Tot $ Ord[/TD]
[TD="class: xl105, width: 77, bgcolor: transparent"]Tot CS COG EXT[/TD]
[TD="class: xl110, width: 86, bgcolor: transparent"] ORD GM% [/TD]
[TD="class: xl105, width: 61, bgcolor: transparent"]PY Tot CS[/TD]
[TD="class: xl114, width: 59, bgcolor: transparent"] PY Tot $ [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl104, bgcolor: transparent"]4[/TD]
[TD="class: xl106, bgcolor: transparent"] [/TD]
[TD="class: xl117, bgcolor: transparent"]$455.28[/TD]
[TD="class: xl117, bgcolor: transparent"]$273.11[/TD]
[TD="class: xl116, bgcolor: transparent"]40.0%[/TD]
[TD="class: xl106, bgcolor: transparent"] [/TD]
[TD="class: xl115, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl73, width: 83, bgcolor: transparent"]Item #[/TD]
[TD="class: xl73, width: 233, bgcolor: transparent"]Item Description[/TD]
[TD="class: xl74, width: 76, bgcolor: transparent"]CS QTY ORD[/TD]
[TD="class: xl73, width: 63, bgcolor: transparent"]CS Cost[/TD]
[TD="class: xl74, width: 118, bgcolor: transparent"]Tot EXT ORD $[/TD]
[TD="class: xl73, width: 77, bgcolor: transparent"]EXT CS COG[/TD]
[TD="class: xl73, width: 86, bgcolor: transparent"]Item GM%[/TD]
[TD="class: xl74, width: 61, bgcolor: transparent"]2017 CS PUR[/TD]
[TD="class: xl74, width: 59, bgcolor: transparent"]2017 $ PUR[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]101789[/TD]
[TD="class: xl67, bgcolor: transparent"]ABLAZE TRAY 'C'[/TD]
[TD="class: xl66, bgcolor: transparent"]2[/TD]
[TD="class: xl108, bgcolor: transparent"]78.48[/TD]
[TD="class: xl112, bgcolor: transparent"]156.96[/TD]
[TD="class: xl112, bgcolor: transparent"]101.59[/TD]
[TD="class: xl113, bgcolor: transparent"]35.3%[/TD]
[TD="class: xl107, bgcolor: transparent"]???[/TD]
[TD="class: xl107, bgcolor: transparent"]???[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]100899[/TD]
[TD="class: xl67, bgcolor: transparent"]ABSOLUTE POWER 'C'[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl108, bgcolor: transparent"]83.66[/TD]
[TD="class: xl112, bgcolor: transparent"]0.00[/TD]
[TD="class: xl112, bgcolor: transparent"]0.00[/TD]
[TD="class: xl113, bgcolor: transparent"]39.7%[/TD]
[TD="class: xl107, bgcolor: transparent"]#N/A[/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Toney]

Hi Joyner,
I am pretty close to getting the formula. I need to pull previous year's info from 2nd sheet. I need drill down in the 2nd sheet to find how many cases were purchased by account #, then Item # so this info shows on sheet 1 under 2017 CS PUR.

[TABLE="width: 690"]
<colgroup><col width="83" style="width: 62pt; mso-width-source: userset; mso-width-alt: 3035;"> <col width="233" style="width: 175pt; mso-width-source: userset; mso-width-alt: 8521;"> <col width="76" style="width: 57pt; mso-width-source: userset; mso-width-alt: 2779;"> <col width="63" style="width: 47pt; mso-width-source: userset; mso-width-alt: 2304;"> <col width="63" style="width: 47pt; mso-width-source: userset; mso-width-alt: 2304;"> <col width="118" style="width: 89pt; mso-width-source: userset; mso-width-alt: 4315;"> <col width="77" style="width: 58pt; mso-width-source: userset; mso-width-alt: 2816;"> <col width="86" style="width: 65pt; mso-width-source: userset; mso-width-alt: 3145;"> <col width="61" style="width: 46pt; mso-width-source: userset; mso-width-alt: 2230;"> <col width="59" style="width: 44pt; mso-width-source: userset; mso-width-alt: 2157;"> <tbody>[TR]
[TD="class: xl85, width: 83, bgcolor: #F8CBAD"]Acct #[/TD]
[TD="class: xl102, width: 233, bgcolor: transparent"]722353[/TD]
[TD="class: xl87, width: 76, bgcolor: #C6E0B4"]Type[/TD]
[TD="class: xl109, width: 63, bgcolor: #C6E0B4"]C[/TD]
[TD="class: xl88, width: 63, bgcolor: #C6E0B4"] [/TD]
[TD="class: xl78, width: 118, bgcolor: #DBDBDB"]CS Prod[/TD]
[TD="class: xl93, width: 77, bgcolor: #DBDBDB"] [/TD]
[TD="class: xl83, width: 86, bgcolor: #00B0F0"] Total W# [/TD]
[TD="class: xl98, width: 61, bgcolor: #00B0F0"] [/TD]
[TD="class: xl69, width: 59, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl86, bgcolor: #F8CBAD"]CustName[/TD]
[TD="class: xl86, bgcolor: #F8CBAD"]SLAY'S FIREWORKS[/TD]
[TD="class: xl92, bgcolor: #C6E0B4"] SA [/TD]
[TD="class: xl109, bgcolor: #C6E0B4"]245[/TD]
[TD="class: xl88, bgcolor: #C6E0B4"] [/TD]
[TD="class: xl94, bgcolor: #DBDBDB"]CS Promo[/TD]
[TD="class: xl95, bgcolor: #DBDBDB"] [/TD]
[TD="class: xl96, bgcolor: #00B0F0"] # PALLETS [/TD]
[TD="class: xl99, bgcolor: #00B0F0"] [/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl85, bgcolor: #F8CBAD"]Date[/TD]
[TD="class: xl86, bgcolor: #F8CBAD"] [/TD]
[TD="class: xl88, bgcolor: #C6E0B4"]PL[/TD]
[TD="class: xl109, bgcolor: #C6E0B4"]L8[/TD]
[TD="class: xl88, bgcolor: #C6E0B4"] [/TD]
[TD="class: xl79, bgcolor: #DBDBDB"] Total Cost CS [/TD]
[TD="class: xl81, bgcolor: #DBDBDB"] [/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl97, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl89, bgcolor: #B4C6E7"]RBU[/TD]
[TD="class: xl90, bgcolor: #B4C6E7"] [/TD]
[TD="class: xl87, bgcolor: #C6E0B4"]Terms[/TD]
[TD="class: xl109, bgcolor: #C6E0B4"]WNX[/TD]
[TD="class: xl88, bgcolor: #C6E0B4"] [/TD]
[TD="class: xl80, bgcolor: #DBDBDB"] Total w/Prod Lia [/TD]
[TD="class: xl81, bgcolor: #DBDBDB"] [/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl97, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl89, bgcolor: #B4C6E7"]BP Ship[/TD]
[TD="class: xl91, bgcolor: #B4C6E7"] [/TD]
[TD="class: xl87, bgcolor: #C6E0B4"] [/TD]
[TD="class: xl87, bgcolor: #C6E0B4"] [/TD]
[TD="class: xl87, bgcolor: #C6E0B4"] [/TD]
[TD="class: xl77, bgcolor: #92D050"] [/TD]
[TD="class: xl82, bgcolor: #92D050"] [/TD]
[TD="class: xl72, bgcolor: transparent"] Act Ship BP [/TD]
[TD="class: xl100, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl84, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] "]Order Notes[/TD]
[TD="class: xl118, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , colspan: 6"] [/TD]
[TD="class: xl69, bgcolor: transparent"]Order #[/TD]
[TD="class: xl97, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"] [/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl101, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl103, width: 76, bgcolor: transparent"]Tot CS Ord[/TD]
[TD="class: xl111, bgcolor: transparent"]L8[/TD]
[TD="class: xl111, bgcolor: transparent"]UOM[/TD]
[TD="class: xl103, width: 118, bgcolor: transparent"]Tot $ Ord[/TD]
[TD="class: xl105, width: 77, bgcolor: transparent"]Tot CS COG EXT[/TD]
[TD="class: xl110, width: 86, bgcolor: transparent"] ORD GM% [/TD]
[TD="class: xl105, width: 61, bgcolor: transparent"]PY Tot CS[/TD]
[TD="class: xl114, width: 59, bgcolor: transparent"] PY Tot $ [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl104, bgcolor: transparent"]8[/TD]
[TD="class: xl106, bgcolor: transparent"] [/TD]
[TD="class: xl106, bgcolor: transparent"] [/TD]
[TD="class: xl117, bgcolor: transparent"]#N/A[/TD]
[TD="class: xl117, bgcolor: transparent"]$969.84[/TD]
[TD="class: xl116, bgcolor: transparent"]#N/A[/TD]
[TD="class: xl106, bgcolor: transparent"] [/TD]
[TD="class: xl115, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl73, width: 83, bgcolor: transparent"]Item #[/TD]
[TD="class: xl73, width: 233, bgcolor: transparent"]Item Description[/TD]
[TD="class: xl74, width: 76, bgcolor: transparent"]CS QTY ORD[/TD]
[TD="class: xl73, width: 63, bgcolor: transparent"]CS Cost[/TD]
[TD="class: xl73, width: 63, bgcolor: transparent"][/TD]
[TD="class: xl74, width: 118, bgcolor: transparent"]Tot EXT ORD $[/TD]
[TD="class: xl73, width: 77, bgcolor: transparent"]EXT CS COG[/TD]
[TD="class: xl73, width: 86, bgcolor: transparent"]Item GM%[/TD]
[TD="class: xl74, width: 61, bgcolor: transparent"]2017 CS PUR[/TD]
[TD="class: xl74, width: 59, bgcolor: transparent"]2017 $ PUR[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]101789[/TD]
[TD="class: xl67, bgcolor: transparent"]ABLAZE TRAY 'C'[/TD]
[TD="class: xl66, bgcolor: transparent"]1[/TD]
[TD="class: xl108, bgcolor: transparent"]78.48[/TD]
[TD="class: xl108, bgcolor: transparent"]CS[/TD]
[TD="class: xl112, bgcolor: transparent"]78.48[/TD]
[TD="class: xl112, bgcolor: transparent"]50.80[/TD]
[TD="class: xl113, bgcolor: transparent"]35.3%[/TD]
[TD="class: xl107, bgcolor: transparent"][/TD]
[TD="class: xl107, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]100899[/TD]
[TD="class: xl67, bgcolor: transparent"]ABSOLUTE POWER 'C'[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl108, bgcolor: transparent"]83.66[/TD]
[TD="class: xl108, bgcolor: transparent"]CS[/TD]
[TD="class: xl112, bgcolor: transparent"]0.00[/TD]
[TD="class: xl112, bgcolor: transparent"]0.00[/TD]
[TD="class: xl113, bgcolor: transparent"]39.7%[/TD]
[TD="class: xl107, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]101111A[/TD]
[TD="class: xl67, bgcolor: transparent"]BIG BANG 'C'[/TD]
[TD="class: xl66, bgcolor: transparent"]4[/TD]
[TD="class: xl108, bgcolor: transparent"]248.73[/TD]
[TD="class: xl108, bgcolor: transparent"]CS[/TD]
[TD="class: xl112, bgcolor: transparent"]994.91[/TD]
[TD="class: xl112, bgcolor: transparent"]697.15[/TD]
[TD="class: xl113, bgcolor: transparent"]29.9%[/TD]
[TD="class: xl107, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]101206[/TD]
[TD="class: xl67, bgcolor: transparent"]BIG BANG (CA VER)[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl108, bgcolor: transparent"]103.26[/TD]
[TD="class: xl108, bgcolor: transparent"]CS[/TD]
[TD="class: xl112, bgcolor: transparent"]0.00[/TD]
[TD="class: xl112, bgcolor: transparent"]0.00[/TD]
[TD="class: xl113, bgcolor: transparent"]39.0%[/TD]
[TD="class: xl107, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]100072[/TD]
[TD="class: xl67, bgcolor: transparent"]BIG BOMB POLYBAG 'C'[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl108, bgcolor: transparent"]70.30[/TD]
[TD="class: xl108, bgcolor: transparent"]CS[/TD]
[TD="class: xl112, bgcolor: transparent"]0.00[/TD]
[TD="class: xl112, bgcolor: transparent"]0.00[/TD]
[TD="class: xl113, bgcolor: transparent"]39.4%[/TD]
[TD="class: xl107, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]102175[/TD]
[TD="class: xl67, bgcolor: transparent"]BIG BOMB TRAY 'C'[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl108, bgcolor: transparent"]92.18[/TD]
[TD="class: xl108, bgcolor: transparent"]CS[/TD]
[TD="class: xl112, bgcolor: transparent"]0.00[/TD]
[TD="class: xl112, bgcolor: transparent"]0.00[/TD]
[TD="class: xl113, bgcolor: transparent"]38.4%[/TD]
[TD="class: xl107, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Toney]

Hi Joyner,
I need pull cases sold from previous year by matching account/ customer # and item # on sheet 2. Here is sheet 1 where I want the info to show from sheet 2.



Sheet 2 is listed first.
[TABLE="width: 1523"]
<tbody>[TR]
[TD]Item #[/TD]
[TD]New Item[/TD]
[TD]comp item[/TD]
[TD]cust #[/TD]
[TD]Customer #[/TD]
[TD]Business Unit[/TD]
[TD]Category[/TD]
[TD]BP[/TD]
[TD]Season[/TD]
[TD]Year[/TD]
[TD]PL[/TD]
[TD]Customer name[/TD]
[TD]Description[/TD]
[TD]Packaging[/TD]
[TD]Case Qty[/TD]
[TD]UOM[/TD]
[TD]Total Sales $[/TD]
[/TR]
[TR]
[TD]100064[/TD]
[TD][/TD]
[TD][/TD]
[TD]1411573[/TD]
[TD="align: right"]1411573[/TD]
[TD][/TD]
[TD]10[/TD]
[TD]22068[/TD]
[TD]JULY[/TD]
[TD="align: right"]2017[/TD]
[TD]L6[/TD]
[TD]T & T GIFTS L.L.C.[/TD]
[TD]BARGAIN BAG C NYP[/TD]
[TD]2/12[/TD]
[TD="align: right"]6.0[/TD]
[TD]CS[/TD]
[TD="align: right"]288[/TD]
[/TR]
[TR]
[TD]100072[/TD]
[TD][/TD]
[TD][/TD]
[TD]2203355[/TD]
[TD="align: right"]2203355[/TD]
[TD][/TD]
[TD]10[/TD]
[TD]22041[/TD]
[TD]JULY[/TD]
[TD="align: right"]2017[/TD]
[TD]L7[/TD]
[TD]4 WAY STOP II[/TD]
[TD]BIG BOMB POLYBAG C[/TD]
[TD]6/1[/TD]
[TD="align: right"]1.0[/TD]
[TD]CS[/TD]
[TD="align: right"]74.01[/TD]
[/TR]
[TR]
[TD]100072[/TD]
[TD][/TD]
[TD][/TD]
[TD]748051[/TD]
[TD="align: right"]748051[/TD]
[TD][/TD]
[TD]10[/TD]
[TD]22060[/TD]
[TD]JULY[/TD]
[TD="align: right"]2017[/TD]
[TD]L8[/TD]
[TD]ADDISON CHEVRON DBA FISH'S FIREWORKS[/TD]
[TD]BIG BOMB POLYBAG C[/TD]
[TD]6/1[/TD]
[TD="align: right"]5.0[/TD]
[TD]CS[/TD]
[TD="align: right"]351.52[/TD]
[/TR]
[TR]
[TD]100072[/TD]
[TD][/TD]
[TD][/TD]
[TD]821141[/TD]
[TD="align: right"]821141[/TD]
[TD][/TD]
[TD]10[/TD]
[TD]22060[/TD]
[TD]JULY[/TD]
[TD="align: right"]2017[/TD]
[TD]L8[/TD]
[TD]ALLEN'S FIREWORKS COMPANY[/TD]
[TD]BIG BOMB POLYBAG C[/TD]
[TD]6/1[/TD]
[TD="align: right"]6.0[/TD]
[TD]CS[/TD]
[TD="align: right"]421.8[/TD]
[/TR]
[TR]
[TD]100072[/TD]
[TD][/TD]
[TD][/TD]
[TD]2722243[/TD]
[TD="align: right"]2722243[/TD]
[TD][/TD]
[TD]10[/TD]
[TD]22041[/TD]
[TD]JULY[/TD]
[TD="align: right"]2017[/TD]
[TD]L5[/TD]
[TD]AMANDA FOSKUHL[/TD]
[TD]BIG BOMB POLYBAG C[/TD]
[TD]6/1[/TD]
[TD="align: right"]1.0[/TD]
[TD]CS[/TD]
[TD="align: right"]82[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup>[/TABLE]

sheet 1

[TABLE="width: 690"]
<colgroup><col width="83" style="width: 62pt; mso-width-source: userset; mso-width-alt: 3035;"> <col width="233" style="width: 175pt; mso-width-source: userset; mso-width-alt: 8521;"> <col width="76" style="width: 57pt; mso-width-source: userset; mso-width-alt: 2779;"> <col width="63" style="width: 47pt; mso-width-source: userset; mso-width-alt: 2304;"> <col width="63" style="width: 47pt; mso-width-source: userset; mso-width-alt: 2304;"> <col width="118" style="width: 89pt; mso-width-source: userset; mso-width-alt: 4315;"> <col width="77" style="width: 58pt; mso-width-source: userset; mso-width-alt: 2816;"> <col width="86" style="width: 65pt; mso-width-source: userset; mso-width-alt: 3145;"> <col width="61" style="width: 46pt; mso-width-source: userset; mso-width-alt: 2230;"> <col width="59" style="width: 44pt; mso-width-source: userset; mso-width-alt: 2157;"> <tbody>[TR]
[TD="class: xl85, width: 83, bgcolor: #F8CBAD"]Acct #[/TD]
[TD="class: xl102, width: 233, bgcolor: transparent"]722353[/TD]
[TD="class: xl87, width: 76, bgcolor: #C6E0B4"]Type[/TD]
[TD="class: xl109, width: 63, bgcolor: #C6E0B4"]C[/TD]
[TD="class: xl88, width: 63, bgcolor: #C6E0B4"] [/TD]
[TD="class: xl78, width: 118, bgcolor: #DBDBDB"]CS Prod[/TD]
[TD="class: xl93, width: 77, bgcolor: #DBDBDB"] [/TD]
[TD="class: xl83, width: 86, bgcolor: #00B0F0"] Total W# [/TD]
[TD="class: xl98, width: 61, bgcolor: #00B0F0"] [/TD]
[TD="class: xl69, width: 59, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl86, bgcolor: #F8CBAD"]CustName[/TD]
[TD="class: xl86, bgcolor: #F8CBAD"]SLAY'S FIREWORKS[/TD]
[TD="class: xl92, bgcolor: #C6E0B4"] SA [/TD]
[TD="class: xl109, bgcolor: #C6E0B4"]245[/TD]
[TD="class: xl88, bgcolor: #C6E0B4"] [/TD]
[TD="class: xl94, bgcolor: #DBDBDB"]CS Promo[/TD]
[TD="class: xl95, bgcolor: #DBDBDB"] [/TD]
[TD="class: xl96, bgcolor: #00B0F0"] # PALLETS [/TD]
[TD="class: xl99, bgcolor: #00B0F0"] [/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl85, bgcolor: #F8CBAD"]Date[/TD]
[TD="class: xl86, bgcolor: #F8CBAD"] [/TD]
[TD="class: xl88, bgcolor: #C6E0B4"]PL[/TD]
[TD="class: xl109, bgcolor: #C6E0B4"]L8[/TD]
[TD="class: xl88, bgcolor: #C6E0B4"] [/TD]
[TD="class: xl79, bgcolor: #DBDBDB"] Total Cost CS [/TD]
[TD="class: xl81, bgcolor: #DBDBDB"] [/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl97, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl89, bgcolor: #B4C6E7"]RBU[/TD]
[TD="class: xl90, bgcolor: #B4C6E7"] [/TD]
[TD="class: xl87, bgcolor: #C6E0B4"]Terms[/TD]
[TD="class: xl109, bgcolor: #C6E0B4"]WNX[/TD]
[TD="class: xl88, bgcolor: #C6E0B4"] [/TD]
[TD="class: xl80, bgcolor: #DBDBDB"] Total w/Prod Lia [/TD]
[TD="class: xl81, bgcolor: #DBDBDB"] [/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl97, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl89, bgcolor: #B4C6E7"]BP Ship[/TD]
[TD="class: xl91, bgcolor: #B4C6E7"] [/TD]
[TD="class: xl87, bgcolor: #C6E0B4"] [/TD]
[TD="class: xl87, bgcolor: #C6E0B4"] [/TD]
[TD="class: xl87, bgcolor: #C6E0B4"] [/TD]
[TD="class: xl77, bgcolor: #92D050"] [/TD]
[TD="class: xl82, bgcolor: #92D050"] [/TD]
[TD="class: xl72, bgcolor: transparent"] Act Ship BP [/TD]
[TD="class: xl100, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl84, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] "]Order Notes[/TD]
[TD="class: xl118, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , colspan: 6"] [/TD]
[TD="class: xl69, bgcolor: transparent"]Order #[/TD]
[TD="class: xl97, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: transparent"] [/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl101, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl103, width: 76, bgcolor: transparent"]Tot CS Ord[/TD]
[TD="class: xl111, bgcolor: transparent"]L8[/TD]
[TD="class: xl111, bgcolor: transparent"]UOM[/TD]
[TD="class: xl103, width: 118, bgcolor: transparent"]Tot $ Ord[/TD]
[TD="class: xl105, width: 77, bgcolor: transparent"]Tot CS COG EXT[/TD]
[TD="class: xl110, width: 86, bgcolor: transparent"] ORD GM% [/TD]
[TD="class: xl105, width: 61, bgcolor: transparent"]PY Tot CS[/TD]
[TD="class: xl114, width: 59, bgcolor: transparent"] PY Tot $ [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl104, bgcolor: transparent"]8[/TD]
[TD="class: xl106, bgcolor: transparent"] [/TD]
[TD="class: xl106, bgcolor: transparent"] [/TD]
[TD="class: xl117, bgcolor: transparent"]#N/A[/TD]
[TD="class: xl117, bgcolor: transparent"]$969.84[/TD]
[TD="class: xl116, bgcolor: transparent"]#N/A[/TD]
[TD="class: xl106, bgcolor: transparent"] [/TD]
[TD="class: xl115, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl73, width: 83, bgcolor: transparent"]Item #[/TD]
[TD="class: xl73, width: 233, bgcolor: transparent"]Item Description[/TD]
[TD="class: xl74, width: 76, bgcolor: transparent"]CS QTY ORD[/TD]
[TD="class: xl73, width: 63, bgcolor: transparent"]CS Cost[/TD]
[TD="class: xl73, width: 63, bgcolor: transparent"][/TD]
[TD="class: xl74, width: 118, bgcolor: transparent"]Tot EXT ORD $[/TD]
[TD="class: xl73, width: 77, bgcolor: transparent"]EXT CS COG[/TD]
[TD="class: xl73, width: 86, bgcolor: transparent"]Item GM%[/TD]
[TD="class: xl74, width: 61, bgcolor: transparent"]2017 CS PUR[/TD]
[TD="class: xl74, width: 59, bgcolor: transparent"]2017 $ PUR[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]101789[/TD]
[TD="class: xl67, bgcolor: transparent"]ABLAZE TRAY 'C'[/TD]
[TD="class: xl66, bgcolor: transparent"]1[/TD]
[TD="class: xl108, bgcolor: transparent"]78.48[/TD]
[TD="class: xl108, bgcolor: transparent"]CS[/TD]
[TD="class: xl112, bgcolor: transparent"]78.48[/TD]
[TD="class: xl112, bgcolor: transparent"]50.80[/TD]
[TD="class: xl113, bgcolor: transparent"]35.3%[/TD]
[TD="class: xl107, bgcolor: transparent"][/TD]
[TD="class: xl107, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]100899[/TD]
[TD="class: xl67, bgcolor: transparent"]ABSOLUTE POWER 'C'[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl108, bgcolor: transparent"]83.66[/TD]
[TD="class: xl108, bgcolor: transparent"]CS[/TD]
[TD="class: xl112, bgcolor: transparent"]0.00[/TD]
[TD="class: xl112, bgcolor: transparent"]0.00[/TD]
[TD="class: xl113, bgcolor: transparent"]39.7%[/TD]
[TD="class: xl107, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]101111A[/TD]
[TD="class: xl67, bgcolor: transparent"]BIG BANG 'C'[/TD]
[TD="class: xl66, bgcolor: transparent"]4[/TD]
[TD="class: xl108, bgcolor: transparent"]248.73[/TD]
[TD="class: xl108, bgcolor: transparent"]CS[/TD]
[TD="class: xl112, bgcolor: transparent"]994.91[/TD]
[TD="class: xl112, bgcolor: transparent"]697.15[/TD]
[TD="class: xl113, bgcolor: transparent"]29.9%[/TD]
[TD="class: xl107, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]101206[/TD]
[TD="class: xl67, bgcolor: transparent"]BIG BANG (CA VER)[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl108, bgcolor: transparent"]103.26[/TD]
[TD="class: xl108, bgcolor: transparent"]CS[/TD]
[TD="class: xl112, bgcolor: transparent"]0.00[/TD]
[TD="class: xl112, bgcolor: transparent"]0.00[/TD]
[TD="class: xl113, bgcolor: transparent"]39.0%[/TD]
[TD="class: xl107, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]100072[/TD]
[TD="class: xl67, bgcolor: transparent"]BIG BOMB POLYBAG 'C'[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl108, bgcolor: transparent"]70.30[/TD]
[TD="class: xl108, bgcolor: transparent"]CS[/TD]
[TD="class: xl112, bgcolor: transparent"]0.00[/TD]
[TD="class: xl112, bgcolor: transparent"]0.00[/TD]
[TD="class: xl113, bgcolor: transparent"]39.4%[/TD]
[TD="class: xl107, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]102175[/TD]
[TD="class: xl67, bgcolor: transparent"]BIG BOMB TRAY 'C'[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl108, bgcolor: transparent"]92.18[/TD]
[TD="class: xl108, bgcolor: transparent"]CS[/TD]
[TD="class: xl112, bgcolor: transparent"]0.00[/TD]
[TD="class: xl112, bgcolor: transparent"]0.00[/TD]
[TD="class: xl113, bgcolor: transparent"]38.4%[/TD]
[TD="class: xl107, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl108, bgcolor: transparent"][/TD]
[TD="class: xl108, bgcolor: transparent"][/TD]
[TD="class: xl112, bgcolor: transparent"][/TD]
[TD="class: xl112, bgcolor: transparent"][/TD]
[TD="class: xl113, bgcolor: transparent"][/TD]
[TD="class: xl107, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl108, bgcolor: transparent"][/TD]
[TD="class: xl108, bgcolor: transparent"][/TD]
[TD="class: xl112, bgcolor: transparent"][/TD]
[TD="class: xl112, bgcolor: transparent"][/TD]
[TD="class: xl113, bgcolor: transparent"][/TD]
[TD="class: xl107, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl108, bgcolor: transparent"][/TD]
[TD="class: xl108, bgcolor: transparent"][/TD]
[TD="class: xl112, bgcolor: transparent"][/TD]
[TD="class: xl112, bgcolor: transparent"][/TD]
[TD="class: xl113, bgcolor: transparent"][/TD]
[TD="class: xl107, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl108, bgcolor: transparent"][/TD]
[TD="class: xl108, bgcolor: transparent"][/TD]
[TD="class: xl112, bgcolor: transparent"][/TD]
[TD="class: xl112, bgcolor: transparent"][/TD]
[TD="class: xl113, bgcolor: transparent"][/TD]
[TD="class: xl107, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl108, bgcolor: transparent"][/TD]
[TD="class: xl108, bgcolor: transparent"][/TD]
[TD="class: xl112, bgcolor: transparent"][/TD]
[TD="class: xl112, bgcolor: transparent"][/TD]
[TD="class: xl113, bgcolor: transparent"][/TD]
[TD="class: xl107, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl108, bgcolor: transparent"][/TD]
[TD="class: xl108, bgcolor: transparent"][/TD]
[TD="class: xl112, bgcolor: transparent"][/TD]
[TD="class: xl112, bgcolor: transparent"][/TD]
[TD="class: xl113, bgcolor: transparent"][/TD]
[TD="class: xl107, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]