Mimic a Pivot Table, Group by date and SUM

jdahl

I'm trying to figure out how to essentially mimic a pivot table using functions. And no we won't be getting the pivot table app hence needing this solution.

I want to group items from a sheet by matching dates in a column, and SUM the contents of a few other columns based on those matching dates. The SUMIFS is part of it I think but I can not figure out the group by date part. It's straight forward in a pivot table.

I tried to do this in a report but unfortunately the graphs on the dashboard don't allow you to plot based on the sums in the report, otherwise that is a nice way to also do this. I'll attach some images of what I'm working with.

The pivot table version just shows what I'm after if you did this in excel with a pivot table.

The deliver table just shows a snippet from the sheet I want to create this from. I want to group by the ship column, and then for all matching dates sum the values for the demand, planned, and shipped columns.

So for the 5/5/2021 I would sum 2 rows and have 6, 6, 6 etc.

I also just added an image of the report just to show I can get close to what I want, if only there was the ability to graph using the sums of the different columns

report example.png

deliver table.png

pivot table version.png

Paul Newcome

The reason I ask is because the "table sheet" where we house the pivot table will need to be "prefilled" with enough rows to accommodate as many as you will need. So if you think you won't have more than 50, then I suggest prefilling to 75 just to be on the safe side. That kind of thing. I will point out where the prefilling comes into play.

On the source sheet we are going to insert 4 columns.

1. Auto-Number column called "Auto" with no special formatting.
2. Text/Number column called "Row" with the following column formula:
   1. =MATCH(Auto@row, Auto:Auto, 0)
3. Text/Number column called "DateNumber" with the following column formula:
   1. =VALUE(YEAR(Ship@row) + "" + IF(MONTH(Ship@row) < 10, "0") + MONTH(Ship@row) + "" + IF(DAY(Ship@row) < 10, "0") + DAY(Ship@row))
4. Text/Number column called "Rank" with the following column formula:
   1. =IF(COUNTIFS(DateNumber:DateNumber, DateNumber@row, Row:Row, @cell <= Row@row) = 1, RANKEQ(DateNumber@row, DateNumber:DateNumber, 1))

Now we go to your pivot sheet and prefill as many rows as you need starting at 1 on row 1 and going on down the line. Call this column "Number"

1

2

3

4

5

6

7

so on and so forth until you have more than enough rows populated to cover the number of unique dates.

Then we put this column formula in your date type column:

=INDEX({Source Sheet Ship Column}, MATCH(Number@row, {Source Sheet Rank Column}, 0))

Now that you have your dates populated, you can use a SUMIFS

=SUMIFS({Source Sheet Range To Sum}, {Source Sheet Ship Column}, [Date Column]@row)

Paul Newcome

Ok. We are closer. Go back to the pivot table sheet and insert a text/number column called "Row" and enter this formula:

=SMALL({Data Sheet Rank Column}, Number@row)

Then update the formula in the date column to look for this value instead of the value in the Number column.

=INDEX({Source Sheet Ship Column}, Row@row)

Paul Newcome

How many unique dates do you anticipate having in the sheet?

jdahl

https://community.smartsheet.com/discussion/comment/297231#Comment_297231

It will be variable, but so far there are about 34. I'd of course want this to adjust as new ones are added, even if I have to do some manual adjusting for that.

Paul Newcome

The reason I ask is because the "table sheet" where we house the pivot table will need to be "prefilled" with enough rows to accommodate as many as you will need. So if you think you won't have more than 50, then I suggest prefilling to 75 just to be on the safe side. That kind of thing. I will point out where the prefilling comes into play.

On the source sheet we are going to insert 4 columns.

1. Auto-Number column called "Auto" with no special formatting.
2. Text/Number column called "Row" with the following column formula:
   1. =MATCH(Auto@row, Auto:Auto, 0)
3. Text/Number column called "DateNumber" with the following column formula:
   1. =VALUE(YEAR(Ship@row) + "" + IF(MONTH(Ship@row) < 10, "0") + MONTH(Ship@row) + "" + IF(DAY(Ship@row) < 10, "0") + DAY(Ship@row))
4. Text/Number column called "Rank" with the following column formula:
   1. =IF(COUNTIFS(DateNumber:DateNumber, DateNumber@row, Row:Row, @cell <= Row@row) = 1, RANKEQ(DateNumber@row, DateNumber:DateNumber, 1))

Now we go to your pivot sheet and prefill as many rows as you need starting at 1 on row 1 and going on down the line. Call this column "Number"

1

2

3

4

5

6

7

so on and so forth until you have more than enough rows populated to cover the number of unique dates.

Then we put this column formula in your date type column:

=INDEX({Source Sheet Ship Column}, MATCH(Number@row, {Source Sheet Rank Column}, 0))

Now that you have your dates populated, you can use a SUMIFS

=SUMIFS({Source Sheet Range To Sum}, {Source Sheet Ship Column}, [Date Column]@row)

jdahl

Thanks a lot for all the help on this. Couple questions/issues.

1) What is the point of the first 2 new columns, couldn't this just be done with the auto column alone since all that seems to be doing is copying the numbers from the Auto to the Row?

2) In the 4th "Rank" column the formula isn't working, is the @cell missing something? It's greyed out like it isn't pointing to the right spot. It's giving me an #invalid data type error.

***After writing this I started playing around and the issues seems to be with the first range/criterion in the COUNTIFS, since I'm not really sure what it's trying to do I can't diagnose very well.

Just a general question on all of this. I assume you went this route because when we do the index match on the pivot sheet you have to use formulas in text/number type columns and the match is going to be finding a date if you did this more like what you wrote in this thread?

https://community.smartsheet.com/discussion/57396/generate-list-of-unique-values-index-distinct-vlookup/p1

jdahl

https://community.smartsheet.com/discussion/comment/297244#Comment_297244

Paul Newcome

The second column is in place so that row 1 will always be 1, row 2 will always be 2, so on and so forth even if new rows are added or deleted out of order and will also remain that way if the sheet is sorted. If all new rows are added to the bottom of the sheet, and you never delete any rows or sort the sheet, then yes. The auto-number column would work on its own.

The reason I chose this method is because the RANKQ formula didn't work when evaluating dates. On the Pivot Sheet, then INDEX/MATCH should be in a date type column pulling from the date column. It should not be in a text/number column.

When I posted my above solution, I had created a test sheet with those column names and made sure it was working first. I then copy/pasted each of the formulas directly from the working solution to here. As long as you have it set up exactly as described above, it should be working for you.

jdahl

https://community.smartsheet.com/discussion/comment/297711#Comment_297711

dates not showing.png

show formula.png

I copied and pasted your formula so it should be correct. Here are some screen shots of what I'm getting.

I understand the invalid data type on the DateNumber column since I have no date there.

I tried braking down the formula to isolate the issue but I couldn't find a reason it wasn't working.

jdahl

Well I'll try to respond again, it got deleted some how. Anyhow, I copied and pasted your functions as you have them here so I'm not sure why it's not working. I tried to break down the last one into smaller parts to see if I could isolate what was wrong but haven't found the issue yet.

Attached are some screen shots so you can see what I have and possibly what the issue is.

dates not showing.png

show formula.png

One shows the formula I have entered, the other shows the columns.

Thanks again for the help on this.

jdahl

On second look it seems to possibly be with this

COUNTIFS(DateNumber:DateNumber, DateNumber@row)

I isolated that function and it doesn't seem to find matches. Could the format of the datenumber column be wrong?

Paul Newcome

It is because you have blanks in the Ship column. Are you able to fill those in, or do they need to be left blank?

jdahl

They need to remain blank until I have a date to fill them in with. Can't be pre-filled though.

Could this be fixed by adding some IF check to the DateNumber row that looks for blanks and if blank adds a value that doesn't mess up the Rank function?

jdahl

Ok, So I believe I fixed it by changing the DateNumber columns function to this

=IF(ISBLANK(Ship@row), "", VALUE(YEAR(Ship@row) + "" + IF(MONTH(Ship@row) < 10, "0") + MONTH(Ship@row) + "" + IF(DAY(Ship@row) < 10, "0") + DAY(Ship@row)))

jdahl

https://community.smartsheet.com/discussion/comment/297244#Comment_297244

So I have the Ranks, and I have them importing into the new pivot page. The issue now is that the ranks don't fill in numerically, numbers are skipped. So then when I fill in the dates on the pivot sheet there are #No Matches. See attachments

pivot 1.png

pivot 2.png

rank 1.png

Can these be in order so there are not holes with missing dates?

Paul Newcome

Try this in the Rank column

=IF(COUNTIFS(DateNumber:DateNumber, DateNumber@row, Row:Row, @cell <= Row@row) = 1, Row@row)

jdahl

https://community.smartsheet.com/discussion/comment/297786#Comment_297786

Sorry that didn't fix it, just shifted the numbers up a couple but still gaps