Forum Discussion

Rob Nunley's avatar
Rob Nunley
Brass Contributor
Sep 22, 2017
Solved

Copy Data to Other Sheets' Columns Based on Criteria

I have one Orders sheet and 12 Month sheets (Jan, Feb, Mar, etc).  The Orders sheet has five columns: Dept, Vendor, Brand, Cost and Date.  The Dept column has ten different options ie Truck, Car, RV,...
Formulas & Functions
Need Help
  • Argelo Royce Bautista's avatar
    Argelo Royce Bautista
    Sep 24, 2017

    Hello Rob Nunley,

    Your problem can be easily solved by utilizing excel's Pivot Tables. I hope you are somehow well versed with excel's ribbon menu. here are the general steps:

    1. Create an Excel table with your order data.
    2. Create a month column using this formula: "=TEXT([@[Order Date]],"MMM")"
    3. Use the table as a source data for your pivot table.
    4. Drag order date and make fields to the rows area.
    5. Drag model to the coluns area.
    6. Drag month to the filters area.
    7. Drag Cost to the Values area.
    8. Format the pivot table to your liking.
      1. Click the Sum of Cost and choose value field settings.
      2. Click Number Format and choose Currency then click OK, and then OK again.
      3. Under the design tab Click Subtotals and choose do not show subtotals.
      4. Click Report Layout and choose show in tabular forms.
      5. Click report layout and choose repeat all items labels.
    9. Generate the Sheets that you need.
      1. Under the analyze tab, click the little arrow beside the Options.
      2. Click show report filter pages.
      3. Click Month and then click OK.
    10. You're Done! if ever that you entered more data on your orders table, just click Refresh All under the Data tab and everything will be updated.

    I have attached the file which includes the sheets for Sep and Oct. In the case of another month (Let's Say Nov) you can do step 9 again, just delete the duplicate sheets (Sep (2) and Oct (2)).


    I hope you can try out the steps and get familiar with Excel's capabilities. If you ever need more details on the step, I can update this post and add more screenshots.

     

    Good luck on you task,

    Argelo Royce Bautista

Rob Nunley's avatar
Rob Nunley
Brass Contributor
Sep 23, 2017

Thanks for the reply.  Here is a sample workbook with the desired output.  The month sheets are link to the Orders sheet and auopopulates the corosponding columns.  The column headers on the Orders sheet are different from those on the month sheets. 

 

Orders     
Order DateTypeMakeModelYearCost
      
9/12/2017RVChampionTeir 12004$35,000.00
9/12/2017CarToyotaCamry2007$18,000.00
9/12/2017RVWinnebagoTeir 22015$43,000.00
9/21/2017TruckFordF1502004$21,000.00
10/13/2017BoatWatercraftSki2009$12,000.00
10/16/2017RVWinnebagoTeir 22006$45,000.00
10/16/2017CarToyotaCorolla2000$8,500.00
10/17/2017TruckToyotaTundra2001$12,345.00
10/18/2017TruckFordF2502016$16,988.00

 

Sep     
Order DateMakeRVCarTruckBoat
      
9/12/2017Champion$35,000.00   
9/12/2017Toyota $18,000.00  
9/12/2017Winnebago$43,000.00   
9/21/2017Ford  $21,000.00 

 

Oct     
Order DateMakeRVCarTruckBoat
      
10/13/2017Watercraft   $12,000.00
10/16/2017Winnebago$45,000.00   
10/16/2017Toyota $8,500.00  
10/17/2017Toyota  $12,345.00 
10/18/2017Ford  $16,988.00 

 

 

 

Argelo Royce Bautista's avatar
Argelo Royce Bautista
MCT
Sep 24, 2017

Hello Rob Nunley,

Your problem can be easily solved by utilizing excel's Pivot Tables. I hope you are somehow well versed with excel's ribbon menu. here are the general steps:

  1. Create an Excel table with your order data.
  2. Create a month column using this formula: "=TEXT([@[Order Date]],"MMM")"
  3. Use the table as a source data for your pivot table.
  4. Drag order date and make fields to the rows area.
  5. Drag model to the coluns area.
  6. Drag month to the filters area.
  7. Drag Cost to the Values area.
  8. Format the pivot table to your liking.
    1. Click the Sum of Cost and choose value field settings.
    2. Click Number Format and choose Currency then click OK, and then OK again.
    3. Under the design tab Click Subtotals and choose do not show subtotals.
    4. Click Report Layout and choose show in tabular forms.
    5. Click report layout and choose repeat all items labels.
  9. Generate the Sheets that you need.
    1. Under the analyze tab, click the little arrow beside the Options.
    2. Click show report filter pages.
    3. Click Month and then click OK.
  10. You're Done! if ever that you entered more data on your orders table, just click Refresh All under the Data tab and everything will be updated.

I have attached the file which includes the sheets for Sep and Oct. In the case of another month (Let's Say Nov) you can do step 9 again, just delete the duplicate sheets (Sep (2) and Oct (2)).


I hope you can try out the steps and get familiar with Excel's capabilities. If you ever need more details on the step, I can update this post and add more screenshots.

 

Good luck on you task,

Argelo Royce Bautista

  • Rob Nunley's avatar
    Rob Nunley
    Brass Contributor
    Sep 25, 2017

    Thanks Argelo! Some months I will not have an RV in Orders sheet.  Is there a way to include a column for RV for those month on the pivot table?

    • Argelo Royce Bautista's avatar
      Argelo Royce Bautista
      MCT
      Sep 26, 2017

      Hi Rob,

      Yes there is. 

      1. Click the dropdown ont he model field, and click field settings.
      2. under the Layout & Print tab click show items with no data. Click okay and generate the Report Filter Pages again.
      • Rob Nunley's avatar
        Rob Nunley
        Brass Contributor
        Sep 27, 2017

        Thank you for the help.  Works like a champ!

Resources