How to pivot a data set (or use formulas) and add columns based on criteria

**ammartino44** · 02-15-2018, 12:33 AM

Hallo. I have this data set with SKU, dates, purchase quantity, and a price per unit. Attached in first tab on left.

I want to be able to create an output with these rules but I don't know how to do it.

Rules and Output:
I want one row per sku (in the data set, there is one row per purchase date, per sku).

If there is one purchase date for a sku, then that row can just be presented as is.

If there are more than purchase dates for a sku AND the average price is the same, then I just need one row that sums quantity and keeps average price the same.

If there are more than one purchase date for a sku, AND the average prices are different, then I want those separated in THE COLUMNS. So it would be like this
SKU, Purchase Quantity, Price per unit, purchase quantity, price per unit, etc (the dates don't need to be specified but it does need to be in order of date.

**JeteMc** · 02-19-2018, 04:15 PM

It looks as if the table in columns I:S on the Base sheet is supposed to be an intermediary between the table in columns A:G and the table in columns A:E on sheet2. That being the case I didn't write any formulas for transferring data from columns A:G to columns I:S instead writing formulas, as well as rearranging the first table so that data from the first table transfers directly, with the aide of helper columns (which may be moved and/or hidden for aesthetic purposes).
The helper columns headed 2016 Sales and 2017 Sales are populated using the formula:

Formula:

Please Login or Register  to view this content.

The helper column headed Count is populated using: =COUNTIFS(A$4:A4,A4)
On sheet 2 the COGS columns are populated using the array entered formula*:

Formula:

Please Login or Register  to view this content.

*Array entered formulas are confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Let us know if you have any questions.

How to pivot a data set (or use formulas) and add columns based on criteria

LinkBack

Thread Tools

Rate This Thread

Display

How to pivot a data set (or use formulas) and add columns based on criteria

Re: How to pivot a data set (or use formulas) and add columns based on criteria

Thread Information

Users Browsing this Thread

Similar Threads

Adding manual columns next to pivot tables and pulling pivot table data for formulas

Hide pivot table columns based on date criteria

Macro to filter data and copy the data's from multiple columns based on the criteria

formulas for search based on multiple criteria in multiple columns

[SOLVED] sum data in pivot table based on date criteria

Code to hide columns based on date criteria and insert another column with formulas

Pivot multiple columns into rows based on criteria

Bookmarks

Bookmarks

Posting Permissions