SOLVED

Copy Data to Other Sheets' Columns Based on Criteria

Brass Contributor

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, Boat, etc.  The Orders sheet will have all the order data for an entire year.

 

The Month sheets have the same columns as the Orders sheet excluding the Dept and Cost columns. Instead of one column for Dept there are 10 columns, one for each of the options. These columns will be populated with the cost of each option.  This makes a total of 13 columns on the Month sheets.  Vendor, Brand and Date columns are A1:A3 and the Dept options columns are A4:13.

 

I would like to populate the Month sheets based on the Date column from the Orders sheet.  For example, all the orders between Jan 1 and Jan 31 would appear on the Jan sheet, all the orders from Feb 1 to Feb 28 on the Feb sheet and so on for the remaining Month sheets.  Depending on the option selected in the Dept column of the Orders sheet, I would like the associated cost to populate the corresponding options column on the Month sheet. For example, if on the Orders sheet an RV was entered at a cost of $35,000, that cost would be transferred to the Month sheet to the appropriate option column.  In this case the RV column. 

 

To sum up, each row in the Month sheets would have the Vendor, Brand, Date and Cost in the appropriate option column.  This data would be auto populated from the Orders sheet.

 

Is there a formula(s) that makes this possible?

 

Thanks in advance!

11 Replies

Hi Rob,

 

Usually what people do to recommending this or that formula they played with it in the workbook. Even if they know for sure how it works. If you provide short sample file not to generate your data model from scratch it'll be much more chances someone answers.

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 

 

 

 

best response confirmed by Rob Nunley (Brass Contributor)
Solution

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.image.png
    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

Hi Rob,

 

In addition to PivotTable solution which Argelo suggested here are couple more. Which one to use depends on many factors, from Excel versions and do you use predefined forms or not; to personal preferences.

 

Anyway, first one using array formulas (Ctrl+Shift+Enter) to add. Let assume your data is located as

orders

Orders.JPG

month

Month.JPG

The pattern for the formula which extract data from master list based on criteria is described here http://www.exceltactics.com/make-filtered-list-sub-arrays-excel-using-small/4/ and in many other places.

In our case in monthly sheet in A2 let add

=IFERROR(
   INDEX(Orders!$A$2:$C$660,
      SMALL(
         IF((MONTH(Orders!$A$2:$A$660)=$H$1)*(Orders!$A$2:$A$660>1),
            ROW(Orders!$A$2:$A$660) - ROW(Orders!$A$2)+1
         ),ROW(1:1)
      ),1
   ),""
)

We take 660-rows range in Orders to have some gap. If fortunatelly you have more it is to be increased. Formula return first date in orders for defined in cell $H$1 month. Dates is first column in the range, in bold above in formula.

Copy this formula in B2 and change 1 on 3 to receive data from third column "Make".

To receive the cost by type for this record in C2 add

=IFERROR(
   INDEX(Orders!$F$2:$F$660,
      MATCH(1,
         (Orders!$A$2:$A$660=$A2)*
         (Orders!$C$2:$C$660=$B2)*
         (Orders!$B$2:$B$660=C$1),
      0)
   ),
0)

which finds the cost for the order for given Date and Make (A2 and B2), and Type for this column (C1). 

Copy this cell to the right till last column type (F2).

Select cells from C2 to F2 and apply cusom format (Ctrl+1) to them

[$$-en-US]#,##0.00;;;@

which 'hides' zeroes in the cells.

Now select all cells in row 2 and drag them down till out of your ordes in the month, better more. Finally select sheet tab and copy it for another months. Only H1 is enough to change for each given month.

 

In formulas above size of orders list could be defined dynamically, but bit easier if you use for orders Excel Table (that's only one of pros). Let name that table as Orders, when in monthly tabs alternatives to above formulas will be

=IFERROR(
   INDEX(Orders[[Order Date]:[Make]],
      SMALL(
         IF((MONTH(Orders[Order Date])=$H$1)*(Orders[Order Date]>1),
            ROW(Orders[Order Date]) - ROW(Orders[[#Headers],[Order Date]])
         ),ROW(1:1)
      ),1
   ),""
)

and

=IFERROR(
   INDEX(Orders[Cost],
      MATCH(1,
         (Orders[Order Date]=$A2)*
         (Orders[Make]=$B2)*
         (Orders[Type]=C$1),
      0)
   ),
0)

And finally the option with using Power Query (aka Get & Transform in Excel 2016). First, we load and pivot for types Orders table. Could be done in few clicks in user interface, here is code generated

let
    Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
    ChangeType = Table.TransformColumnTypes(Source,{
      {"Order Date", type date},
      {"Type", type text},
      {"Make", type text},
      {"Model", type text},
      {"Year", Int64.Type},
      {"Cost", type number}
    }),
    RemoveUnused = Table.RemoveColumns(ChangeType,{"Model", "Year"}),
    ReordereColumns = Table.ReorderColumns(RemoveUnused,
      {"Order Date", "Make", "Type", "Cost"}
   ),
    PivotType = Table.Pivot(ReordereColumns,
      List.Distinct(ReordereColumns[Type]), "Type", "Cost")
in
    PivotType

Load the query as connection. After that make the reference on that query fro each month adding filter for the monh

let
    Source = Orders,
    FilterMonth = Table.SelectRows(Source, each Date.Month([Order Date]) = 9)
in
    FilterMonth

and load each of them into Excel sheet

 

Fileas are attached

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?

Hi Rob,

Yes there is. 

  1. Click the dropdown ont he model field, and click field settings.
    image.png
  2. under the Layout & Print tab click show items with no data. Click okay and generate the Report Filter Pages again.
    image.png

Thank you for the help.  Works like a champ!

Hi

Inline with the same, i have a data as attached in file:

 

I need to extract data for attributes column against each classs having only cross mark shown.

 

can anyone please suggest the ways to doing it?. Thanks in advance.

Sir, I Have Same type of excel, I also try to Copy Data to Other Sheets' Columns Based on Criteria.

 

Please see my attached sample excel

 

@Sergei Baklan Thank you Sergei, i used your code and to my surprise it worked wonder... I just changed it to my requirement and it worked... Thanks for your effort. I appreciate it!!

@vandanay , you are welcome, glad it helped

1 best response

Accepted Solutions
best response confirmed by Rob Nunley (Brass Contributor)
Solution

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.image.png
    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

View solution in original post