Get latest value for a column based on another column (date)

Hello Adam Cunnington,

 

This could be possible a couple different ways but I think it will take a couple functions to make it happen.

 

I suggest that you share an editable COPY of your sheet. Use your sample sheet to demonstrate what you'd like the sheet to do!  Here are some tips to help you get a great answer quickly... Product Experts TIPS FOR SHARING  

 

Stephen

Sure; here is an editable sheet. Feel free to create a new tab for enriching the raw data and/or a new tab for the final pivot.

Hello Adam,

 

Thank you for the sample sheet. I created a new sheet called Stephen Help and placed two formulae in cells A2 and C2. The first one in A2 will build you a unique list of your Clients and Components and Sort them by client name.

 

=ArrayFormula(SORT(UNIQUE({'Raw Data'!A2:A,'Raw Data'!C2:C}),1,1))

 

The second one will give you the most recent Version. It accomplishes this using a VLOOKUP() the first part is the search key and that is made from combining the client and component into one key using A2:A&B2:B. The next part is the range to search for the match in. That is made by sorting a literal array made from the data on your other sheet {'Raw Data'!A2:A&'Raw Data'!C2:C,'Raw Data'!B2:B,'Raw Data'!D2:D}. This data is sorted by the dates in column C or the 3rd column. The VLOOKUP then returns an index of column 3 from that data.

 

=ArrayFormula(VLOOKUP(A2:A&B2:B,SORT({'Raw Data'!A2:A&'Raw Data'!C2:C,'Raw Data'!B2:B,'Raw Data'!D2:D},2,0),3,0))

 

Hope this helps,

Stephen

Thanks for this, I've learnt a lot here.

However, this isn't quite what I'm looking for. If you refer to my "Desired output" picture or "ideal" tab, it shows a grid that plots the clients against the latest version of each component. This isn't the same as "show me the latest version for each client + component". In the Ideal tab, you will see that there is no "tickmark" in C5 and C6 because clients B and C did not have the latest version deployed.

Thanks,

Adam

Hello Adam,

 

Sorry I misunderstood. I have changed my sheet in your sample to something that might possibly work? I will be on the sheet for a few minutes to chat as well but here is what I have done. I put this formula in A1.

 

=QUERY('Raw Data'!A1:D,"select A,count(A) where A is not null group by A pivot C,D",1)

 

This uses a QUERY() to look at 'Raw Data'!A1:D and display column A and a count of column A where A is not empty. It then pivots columns C and D and uses a single header row. This might combine the component and version in a way that is slightly different than what you have.

 

Next I used custom number formatting to change all numbers greater than 0 to an x by formatting the area where the count is to [>0]"x"

 

Hope this helps,

Stephen

Grateful for the response but this still doesn't match ideal. It shows every combination of component and version. I am just interested in whether each client has the most recent version of each component.

Hello Adam,

 

Sorry, I was way over thinking this but at the same time simplifying the formula. I adjusted my sheet to match yours exactly now. :) This takes 3 formulae now.

 

The first one is a simple UNIQUE() to get the unique client names in A4.

=UNIQUE('Raw Data'!A2:A)

 

The second one gets the most recent of the unique Component and Version of each in B2.

=ArrayFormula(TRANSPOSE(IFERROR(VLOOKUP(UNIQUE('Raw Data'!C2:C),SORT({'Raw Data'!C2:D,'Raw Data'!B2:B},3,0),{1,2},0))))

 

Finally the third one looks up to see if there is a match and then returns the date just to have a number to use with the custom number format in B4.

=ArrayFormula(IFERROR(VLOOKUP(A4:A&B2:2&B3:3,{'Raw Data'!A2:A&'Raw Data'!C2:C&'Raw Data'!D2:D,'Raw Data'!B2:B},2,0)))

 

These will all expand as new data is entered.

 

I think I finally got this one right. :)

 

Stephen

Adam Cunnington,

 

You're welcome, I'm glad I was able to help!

Thank you for the feedback.

 

Stephen

 

Sorry to re-open but we've changed our need and wondering if you can help? I've updated the ideal tab with what we're looking for. Instead of showing whether or not each client has the most recent version, we now just want to show the most recent version of each component that each client has.

Hello Adam,

 

I have updated the table on Stephen Help to get what you're asking for. The formula is in H4 and looks like this.

 

=ArrayFormula(IFERROR(VLOOKUP(G4:G&H3:3,SORT({'Raw Data'!A2:A&'Raw Data'!C2:C,'Raw Data'!D2:D,'Raw Data'!B2:B},3,0),2,0)))

 

This first uses a literal array {'Raw Data'!A2:A&'Raw Data'!C2:C,'Raw Data'!D2:D,'Raw Data'!B2:B} which combines the Client and component into a single column using the &. Then adds the column of the version and the date column. That is sorted using SORT() by the date column to put the most recent at the top. Then it uses a VLOOKUP() to find the match of the client and component again combined as the search key in the VLOOKUP() and returns the version. VLOOKUP() will only return the first instance of a match so once it works down the sorted list and finds a match it stops looking at the older data.

 

Hope this helps,

Stephen

Adam Cunnington,

 

You're welcome, I'm glad I was able to help again!

 

Stephen