finding a text in multiple fields and then get the max of a date field field based on that text for Google Sheets.

[sdas34]

Hi, I have a dataset where it has a Date field and there are 4 or more than 4 'Resource Name' columns. The name could be repetitive in each column but it will not be repetitive in each row. Now, I have a list of unique resource name and I need to find out the max Date for a particular resource. I have attached a sample data and the outcome I am looking for. Any help will be appreciated. Thanks in advance.

Dataset:

Date	Resource 1	Resource 2	Resource 3	Resource 4
1 Jan 2022	John
25 Mar 2022	Kane			Maria
15 Jun 2022	Harry	John
18 Jul 2022	Emily
23 Oct 2022	Arin		Kane
29 Dec 2022	Maria			Harry

Output:

Resources	Max Date
John	15 Jun 2022
Kane	23 Oct 2022
Harry	29 Dec 2022
Emily	18 Jul 2022
Arin	23 Oct 2022
Maria	29 Dec 2022

 

[alansidman]

With Power Query, unpivot your data and then group by the name for the maximum date

Book5
	A	B	C	D	E
1	Date	Resource 1	Resource 2	Resource 3	Resource 4
2	1-Jan-22	John
3	25-Mar-22	Kane			Maria
4	15-Jun-22	Harry	John
5	18-Jul-22	Emily
6	23-Oct-22	Arin		Kane
7	29-Dec-22	Maria			Harry
8
9	Value	Max Date
10	John	6/15/2022
11	Kane	10/23/2022
12	Maria	12/29/2022
13	Harry	12/29/2022
14	Emily	7/18/2022
15	Arin	10/23/2022
Sheet1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Date"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Value"}, {{"Max Date", each List.Max([Date]), type nullable date}})
in
    #"Grouped Rows"

 

[sdas34]

With Power Query, unpivot your data and then group by the name for the maximum date

Book5
	A	B	C	D	E
1	Date	Resource 1	Resource 2	Resource 3	Resource 4
2	1-Jan-22	John
3	25-Mar-22	Kane			Maria
4	15-Jun-22	Harry	John
5	18-Jul-22	Emily
6	23-Oct-22	Arin		Kane
7	29-Dec-22	Maria			Harry
8
9	Value	Max Date
10	John	6/15/2022
11	Kane	10/23/2022
12	Maria	12/29/2022
13	Harry	12/29/2022
14	Emily	7/18/2022
15	Arin	10/23/2022
Sheet1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Date"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Value"}, {{"Max Date", each List.Max([Date]), type nullable date}})
in
    #"Grouped Rows"

Hi, thanks for your reply. Actually I need to do this in Google sheet. And I guess Google sheet doesn't support power pivot. Hence, I need to use a formula

 

[Fluff]

When asking questions about Sheets you need to make that clear in your post/title & all such questions should be posted to the General Discussion & Other Applications section of the board.
I have done that for you this time.

 

[sdas34]

Thanks for reposting.

 

[alansidman]

I don't use google sheets, so this is the best I can do for you

Unpivot In Google Sheets With Formulas. Turn Wide Data Into Tall Data.

Unpivot in Google Sheets with formulas. Transform wide data into tall data, which is better for data analysis. For formula connoisseurs!

www.benlcollins.com

 

[sdas34]

Thanks for the suggestions. It is solved now. I have used Arrayformula with Max and if condition to solve it. E.g: Arrayformula(Max(if(sheet1!a:d=b2, sheet1!c:c))). Here sheet1!a:d is range of lookup list, b2 is lookup value, sheet1!c:c is range of date. It's returning the result.