• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Combining dates of different columns into one pivot table field

Kamesh63

New Member
Hi:
I have a rows containing names of medicines. I have three different columns of dates, with respective different columns containing quantity relative to the date as below.
A B C D E F G​
Name of medicine Qty1 Date1 Qty2 Date2 Qty3 Date3

I want to generate a pivot report consolidating all 3 dates (C, E, F) in a single column, which then displays name of medicine and quantity1 or quantity2 or quantity3 relative to that date. I have used slicers, but they are all different dates. Can I somehow consolidate all three date columns into a single column - either through slicer or pivot table field? Thanks for the help.
 
I would like columns E, G, and I consolidated into one column in the pivot report and display for every row from B37 to B543 respective values of D, F, and H accordingly. For instance, the pivot report first column should display expiry dates from all 3 columns. Thanks!
 

Attachments

  • for chorg.xlsx
    37.6 KB · Views: 2
Not fully clear on what you mean by "should display expiry dates from all 3 columns" but I suppose what you want eventually is to know how many units by customer and batch are expiring on each date.

So in a Pivot you may have all the dates listed in the first column. When you expand one date, you will get to the all the customer who have units expiring on that date along with the number of units per batch.

Is that right?
 
Thanks Vivek. I shall be receiving this data from 9 different customers. Each customer may have have different batches (anywhere from 1 to 3) and each having a variable expiring dates. So I need to consolidate all expiry dates from these 9 customers and then show expiring quantities from most recent to most remote date. My problem is variable dates and expiry dates. As you can see I made provision for 3 batches of same medicine.
Customer1 may have 3 different batches and customer2 may have only 1 batch and so on. I need to pull up all the data at a single place and therefore I was looking at how to show different expiry dates of 3 columns into a single pivot table column. Hope I am clear.
 
Yes. I think key for you is to be able to get a final table setup with columns
Medicine, Batch, Expiry Date and then you can do whatever you want with it.

Now to do the combining, I feel the best option is to use Power Query (it's very powerful and easy... in case you did not know about it).

Check out the links below
Merge 3 Tables Into One in PowerPivot or Excel Table
How to Unpivot Data with Power Query in Excel


These should help you get the output in a simple table format, post which you should be able to get the desired output.

If you are running into issues, do upload a sample file with some data and I can give it a try myself.
 
Back
Top