SOLVED

Finding the first & last entry for each category in a large dataset

Copper Contributor

Hello,

 

I am working on a project where I have hundreds of tables with tens of thousands of entries. In each table, I have categories for multiple items and entry dates for each.

 

For example, let's say I have a table where I have the entry dates for 4 types of fruit: apple, orange, papaya and kiwi. I can have thousands of Apple entries, starting all the way back in 2004, and ending in 2019, while for oranges, the entries can start in 2001 and end in 2021. I have organized the data using Pivot tables for it to show me the entries for each fruit category in chronological order. However, since my dataset has tens of thousands of entries, and I must do this for a lot of tables, I end up scrolling for hours to manually find the first and last entry for each item category, in each table.

 

I have created a spreadsheet where there is a small example of what I must do. I have been trying to create a formula that looks for each Apple/Papaya/Kiwi/Orange entry and returns the First (Min) and Last (Max) entries for each, with no success so far. (https://docs.google.com/spreadsheets/d/1NLPNfvuRA33a_9eJz3Naicu6bDPxlK8Z/edit?usp=sharing&ouid=10110...)

 

Thank you in advance!

4 Replies
best response confirmed by AlexC616 (Copper Contributor)
Solution

@AlexC616 

Insert a pivot table.

"Fruit" in rows area.

"Date" twice in values area. Change the aggregation to Min and to Max.

 

@Detlef Lewin

Oh my God it works. I didn't know you can put "Date" twice in values area. What a relief. Thank you so much!
I have one more question. Could you please have a look over this secondary type of table I am dealing with? I have to get to the same result, but the data is arranged differently.

https://docs.google.com/spreadsheets/d/1RiuZsF2WGZPKJVzyKTwMSedssWsVcvFq/edit?usp=sharing&ouid=10110...

@AlexC616 

That is a crosstabular table. You have to unpivot it. I suggest unpivot with Power Query.

You also have to change the values in column B into real dates.

 

Something like this:

let
    Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Date"}, "Attribut", "Wert"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Wert] = "kg")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Wert"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Attribut", "Fruit"}})
in
    #"Renamed Columns"

Then load the data into a pivot table on the worksheet.

 

1 best response

Accepted Solutions
best response confirmed by AlexC616 (Copper Contributor)
Solution

@AlexC616 

Insert a pivot table.

"Fruit" in rows area.

"Date" twice in values area. Change the aggregation to Min and to Max.

 

View solution in original post