Extract Multiple Records with 3 Criteria

[ario]

Hi,

I'd like to create a report that displays the values according to 3 criteria. I have 3 drop down boxes that one can select a customer's name, brand, and date (format of Jan 2013 Week Ending 1/26/2013). My drop downs are in a separate sheet under a5, b5, and c5 respectively. I will then need to find the various sales facts ($sales a2:a34279, volume sales b2:b34279, etc.) from my data sheet. I'm not certain which formulas to use, should I use aggregate or use the old formulas of only index, match, and, row, etc. I'm also not sure if I must split my date drop down values to a month and year only or if the present date value will work, i.e. Jan 2013 Week Ending 1/26/2013. Any advice? Thanks!

 

[Robert Mika]

Hi,

I'd like to create a report that displays the values according to 3 criteria. I have 3 drop down boxes that one can select a customer's name, brand, and date (format of Jan 2013 Week Ending 1/26/2013). My drop downs are in a separate sheet under a5, b5, and c5 respectively. I will then need to find the various sales facts ($sales a2:a34279, volume sales b2:b34279, etc.) from my data sheet. I'm not certain which formulas to use, should I use aggregate or use the old formulas of only index, match, and, row, etc. I'm also not sure if I must split my date drop down values to a month and year only or if the present date value will work, i.e. Jan 2013 Week Ending 1/26/2013. Any advice? Thanks!

Hello ad welcome.
Please post your data.See below how to do it.

 

[ario]

In one view I would like to display records/columns 6-9 for the same market, brand, and date. In another view I would like to only display the markets and products for the selected brand and date. I am only interested in displaying the top 20 records for each view. Thanks!

I have excel 2010.

see an example of the data

Markets Products BRAND [UPC] Period [$] [$ % Chg] [Base $] [Base $ % Chg] Total xAOC YVS IF YOU PLS MTLS VG MXCN GRND 12 OZ 006082280002 Meat Alternative Category 74854 Jan 13 W/E 01/26/13 748393794 -100 -100 Total Food YVS IF YOU PLS MTLS VG MXCN GRND 12 OZ 068692432726 Boc 74855 Dec 12 W/E 12/29/12 748393795 -100 -100 Walmart Total US TA YVS IF YOU PLS ORGN WNR WRP 13.4 OZ 006082200070 Star 74856 Nov 12 W/E 11/24/12 748393796 -100 -100 Kroger Corp Total TA YVS IF YOU PLS ORGN WNR WRP 13.4 OZ 068692432699 Lighter 74857 Oct 12 W/E 10/27/12 748393797 -100 -100 Walmart SC Total US TA YVS IF YOU PLS 12 OZ 006082200041 Gard 74858 Sep 12 W/E 09/29/12 748393798 -100 -100 Target Total TA YVS IF YOU PLS 12 OZ 006082200301 Veggieburger 74859 Aug 12 W/E 08/25/12 748393799 -100 -100 Safeway Corp TA YVS IF YOU PLS BBQ RC BN BRGR 5.3 OZ 068692460693 Dr. Prag 74860 Jul 12 W/E 07/28/12 748393800 -100 -100 Ahold USA Corp Total TA YVS IF YOU PLS CJN MTLS CHCK 5.5 OZ 006082200119 Quorny 74861 Jun 12 W/E 06/30/12 748393801 -100 -100 Publix Total TA YVS IF YOU PLS MTLS BLGN SLCD 5.5 OZ 006082200006 Tofurky 74862 May 12 W/E 05/26/12 748393802 -100 28.504 -97.964 SUPERVALU Total Corp incl ALB TA YVS IF YOU PLS MTLS BLGN SLCD 5.5 OZ 068692432693 Yves 74863 Apr 12 W/E 04/28/12 748393803 -92.675 105.386 -93.056 ShopRite Total TA YVS IF YOU PLS MTLS CNDN BCN SLCD 6 OZ 006082200115 Meat Alternative Category 74864 Mar 12 W/E 03/31/12 748393804 -72.911 722.913 -66.976 Meijer Total TA YVS IF YOU PLS MTLS CNDN BCN SLCD 6 OZ 068692432704 Boc 74865 Feb 12 W/E 02/25/12 748393805 -26.771 1007.115 -28.004 Total xAOC YVS IF YOU PLS MTLS DL SLCD 5.5 OZ 006082200120 Star 74866 Latest 12 - W/E 01/26/13 748393806 -100 -100 Total Food YVS IF YOU PLS MTLS HAM SLCD 5.5 OZ 006082200307 Lighter 74867 Latest 13 - W/E 01/26/13 748393807 -100 -100 Walmart Total US TA YVS IF YOU PLS MTLS HAM SLCD 5.5 OZ 068692432712 Gard 74868 Latest 24 - W/E 01/26/13 748393808 -100 -100 Kroger Corp Total TA YVS IF YOU PLS MTLS PPRN SLCD 4.2 OZ 006082200116 Veggieburger 74869 YTD W/E 01/26/13 748393809 -100 -100 Walmart SC Total US TA YVS IF YOU PLS MTLS PPRN SLCD 4.2 OZ 068692432705 Dr. Prag 74870 Jan 13 W/E 01/26/13 748393810 -100 -100 Target Total TA YVS IF YOU PLS MTLS SLM SLCD 5.5 OZ 006082200111 Quorny 74871 Dec 12 W/E 12/29/12 748393811 -81.667 3.98 -35.285 Safeway Corp TA YVS IF YOU PLS MTLS SLM SLCD 5.5 OZ 068692432703 Tofurky 74872 Nov 12 W/E 11/24/12 748393812 3.03 -9.009 Ahold USA Corp Total TA YVS IF YOU PLS MTLS TRKY SLCD 5.5 OZ 006082200306 Yves 74873 Oct 12 W/E 10/27/12 748393813 -100 -100 Publix Total TA YVS IF YOU PLS MTLS TRKY SLCD 5.5 OZ 068692432711 Meat Alternative Category 74874 Sep 12 W/E 09/29/12 748393814 -83.927 1.49 -90.82 SUPERVALU Total Corp incl ALB TA YVS IF YOU PLS ORGN HOT DOG WRP 11 OZ 006082200311 Boc 74875 Aug 12 W/E 08/25/12 748393815 -91.23 1.99 -87.508 ShopRite Total TA YVS IF YOU PLS PRCK HOT DOG WRP 11 OZ 068692432716 Star 74876 Jul 12 W/E 07/28/12 748393816 -100 -100 Meijer Total TA YVS IF YOU PLS PRCK HOT DOG WRP 9.7 OZ 006082200008 Lighter 74877 Jun 12 W/E 06/30/12 748393817 -100 -100 Total xAOC YVS IF YOU PLS PRCK HOT DOG WRP 9.7 OZ 068692432695 Gard 74878 May 12 W/E 05/26/12 748393818 -100 -100 Total Food YVS IF YOU PLS PRCK VG SSG PT RFRG 8 OZ 006082200312 Veggieburger 74879 Apr 12 W/E 04/28/12 748393819 -100 -100 Walmart Total US TA YVS IF YOU PLS PRCK VG SSG PT RFRG 8 OZ 068692432717 Dr. Prag 74880 Mar 12 W/E 03/31/12 748393820 -67.875 13.73 -55.422 Kroger Corp Total TA YVS IF YOU PLS VG MTLS BRTW LNK 13.4 OZ 006082200025 Quorny 74881 Feb 12 W/E 02/25/12 748393821 -12.298 18.87 -27.673 Walmart SC Total US TA YVS IF YOU PLS VG MTLS BRTW LNK 13.4 OZ 068692441405 Tofurky 74882 Latest 12 - W/E 01/26/13 748393822 -88.055 5.02 -68.208 Target Total TA YVS IF YOU PLS VG MTLS ITLN BRTW 13.4 OZ 006082200027 Yves 74883 Latest 13 - W/E 01/26/13 748393823 -83.281 7.01 -59.433 Safeway Corp TA YVS IF YOU PLS VG MTLS ITLN BRTW 13.4 OZ 068692441406 Meat Alternative Category 74884 Latest 24 - W/E 01/26/13 748393824 -83.788 10.49 -76.05 Ahold USA Corp Total TA GRDN 10 OZ 084223400073 Boc 74885 YTD W/E 01/26/13 748393825 -100 -100 Publix Total TA GRDN 10.5 OZ 084223400030 Star 74886 Jan 13 W/E 01/26/13 748393826 -100 -100 SUPERVALU Total Corp incl ALB TA GRDN 12.3 OZ 084223400096 Lighter 74887 Dec 12 W/E 12/29/12 748393827 -100 -100 ShopRite Total TA GRDN BFLS BRGR ENTR BAG 12 OZ 084223400082 Gard 74888 Nov 12 W/E 11/24/12 748393828 -100 -100 Meijer Total TA GRDN CHPT BLCK BN VG BRGR PTY 50.8 OZ 084223400119 Veggieburger 74889 Oct 12 W/E 10/27/12 748393829 -100 -100 Total xAOC GRDN CHPT LM CRSP MT FR FNGR BAG 9 OZ 084223400080 Dr. Prag 74890 Sep 12 W/E 09/29/12 748393830 -100 -100 Total Food GRDN HMST BFLS BRGR BAG IN BOX 12 OZ 084223400103 Quorny 74891 Aug 12 W/E 08/25/12 748393831 -100 2.02 -98.793 Walmart Total US TA GRDN MNDR ORNG CHCK ENTR BAG 10.5 OZ 084223400094 Tofurky 74892 Jul 12 W/E 07/28/12 748393832 -88.889 17.56 -78.733 Kroger Corp Total TA GRDN MT FR BBQ PLD SHRD BAG IN BOX 9 OZ 084223400047 Yves 74893 Jun 12 W/E 06/30/12 748393833 -41.654 118.23 -23.51 Walmart SC Total US TA GRDN MT FR BBQ SKWR ENTR BAG IN BOX 8 OZ 084223400038 Meat Alternative Category 74894 May 12 W/E 05/26/12 748393834 35.216 132.998 -9.807 Target Total TA GRDN MT FR BRGN TR BFLS TPS VGS 10.5 OZ 084223400056 Boc 74895 Apr 12 W/E 04/28/12 748393835 -43.397 180.071 15.378 Safeway Corp TA GRDN MT FR CHCK FLT ENTR BAG IN BOX 7 OZ 084223400036 Star 74896 Mar 12 W/E 03/31/12 748393836 79.723 262.571 41.877 Ahold USA Corp Total TA GRDN MT FR CHCK STRP RFRG ENTR BOX 7 OZ 084223400034 Lighter 74897 Feb 12 W/E 02/25/12 748393837 3.914 216.675 7.965 Publix Total TA GRDN MT FR CLSC STYL BFL WNG BAG 9 OZ 084223400049 Gard 74898 Latest 12 - W/E 01/26/13 748393838 -100 -100 SUPERVALU Total Corp incl ALB TA GRDN MT FR HM STYL BFLS TIP BAG 9 OZ 084223400051 Veggieburger 74899 Latest 13 - W/E 01/26/13 748393839 -100 -100 ShopRite Total TA GRDN MT FR LGHT SSND CHCK N SCLP 30 OZ 084223400067 Dr. Prag 74900 Latest 24 - W/E 01/26/13 748393840 -100 -100 Meijer Total TA GRDN MT FR LGHT SSND CHCK SCLP BAG 10 OZ 084223400048 Quorny 74901 YTD W/E 01/26/13 748393841 -100 -100 <tbody> </tbody>

<tbody>
</tbody>

 

[ario]

In one view I would like to display records/columns 6-9 for the same market, brand, and date. In another view I would like to only display the markets and products for the selected brand and date. I am only interested in displaying the top 20 records for each view. Thanks!

I have excel 2010 and I'm unable to have the formulat below work. Any idea how to get to work?

=IF(ROWS(A$13:A13)>Control!$G$2,"",INDEX(Data!$I$2:$I$34279,AGGREGATE(15,6,(ROW(Data!$G$2:$G$34279)-ROW(Data!$G$3)+1/((Data!$G$2:$G$34279=Control!$E$5)*(Data!$H$2:$H$34279=Control!$A$70)*(Data!$K$2:$K$34279=Control!$A$5)),ROWS(A$13:A13)))))

 

[shawnhet]

Have you tried a pivot table? This should let you let your organise your reports however you like and autofilter/sort the results as needed.

Excel 2010: Create Pivot Table & Chart
Excel 2010 Tutorial: Pivot Tables

Cheers,