Stacked column chart creation - data reconfiguration

[AmyEH]

How do I rearrange this data in the most efficient way to create a stacked column chart from it?  The dates will be along the x-axis.  The phytoplankton concentration (cells/mL) will be on the y-axis.  The columns in the chart will show the different concentrations of each phyto group, so the phyto groups will be stacked in each column.  Multiple phytoplankton groups were collected on each date.  I need to transpose the group names as column headers.  I know how to transpose, but how do I transpose the group names while keeping the concentrations values associated with them and with the correct date?  Thanks for helping. #stackedcolumnchart #tranpose #rearrange

I did try making a Pivot Table using this data, which puts the data in the correct configuration for creating a stacked column chart.  However, the pivot table groups the date by month, tallying the data for each month in one row, and I don't know how to expand it to show each value separately.

[#2 · Accepted solution]

@AmyEH Perhaps like in the attached file? Not sure though what you mean by "transpose group names as column headers".

[#3]

@AmyEH 

Excel 365 will adjust the row and column headings dynamically.  Older versions and you might as well filter and copy them manually.

The Excel 365 formulas are

Row heading (Date )
= UNIQUE(Data[Date])
Column heading (groups)
= TRANSPOSE(UNIQUE(Data[GPNAME]))
Body (concentrations)
= SUMIFS(Data[cellsPerML2], Data[Date], date, Data[GPNAME], groups)

[#4]

@Riny_van_Eekelen Yes, this is exactly what I mean.  How do I do this?  I am using Excel 2016.

I had made a Pivot Table, so it looks like I was on the right track there, as that's what you did.  When I made my table, all the dates were grouped by month.  For each row (each month) the table had summed the number of values and showed that sum for each column (each group name).  How do I tell the table to display each unique date instead of grouping them by month?  If each unique date is displayed, then each value will be displayed too, instead of a sum.  That is the end result needs to look like.  Essentially, how do I make my Pivot Table look like yours so I can create that stacked column chart from it? 

[#5]

@Riny_van_Eeklen

I figured it out.  I discovered how to change the action from counts to a sum of the values.  I also figured out how to ungroup the dates so they show as unique dates instead of grouped by month.  Thanks for showing me it was possible, motivating me to keep working on solutions to these two problems.

Appreciate the help!

[#6]

@Peter Bartholomew I don't think I have access to Excel 365, so this is only so helpful.  Thanks for offering advice!

[#7]

@AmyEH Had been away from my computer all day, so couldn't respond earlier. Glad you figured it out. 

[#8]

@AmyEH 

I see your point!  It is possible to replace 

= UNIQUE(Data[Date])

by

= SMALL(
    IF(
      MATCH(Data[Date],Data[Date],0)=ROW(Data[Date])-1,
      Data[Date]
    ), 
  k )

but such a formula is such a dog by comparison that I decided it was time to dump my old Excel licence!  Now virtually every formula I write is a dynamic array formula.  It did mean moving to a subscription license though. 

Anyway, I am pleased you got the Pivot Table / Chart to come to order!