Identify Cells/ Rows based on Criteria and evaluated by Timestamp?

[iskivt30]

Hello,

I have a table where the items listed in col A:A, may be repeated .. but there are other criteria in columns B:D, and a timestamp for each row in col E:E. I have generated this sheet with VBA userform where the user enters selection from listboxes and applies status via combo boxes. When user clicks > OK vba code grabs values in list box (that user has selected) and selections from the combo boxes and pastes into excel.

Where i have 2 orange highlighted rows in the data tab (representing Home Page) I would like to grab one that is timestamped the latest (this is also the one noted as mark as Approved).

I have combed through forums for days now, and tried different combinations of formulas, such as using: min/max, countif, vlookups based on IF statements.

I cannot figure out how to identify the following:

IF a Feature is listed in column A:A more than once AND has the same values in column B:B & C:C, then take the one with the latest timestamp and copy to a new tab.

Thanks a million in advance!
Heat map 6-25.xlsm

[Miroslav R.]

Hi there,
last occurence in list = latest record, when You are building list via VBA and users do not insert new lines to already created list.
You did not mention, where do You want to display the result, so I assume that it is col F on sheet 'Features'.

What about the combinations that can occure in col B&C on 'Data' sheet?

You want to pass only results for Feature name & 'Prototype' & 'Desktop'?

If so, then put this formula in cell F2 on sheet 'Features' and drag down:

Please Login or Register  to view this content.

Note that this is array formula and needs to be confirmed with Ctrl+Shift+Enter instead of Enter

Does it help?

[iskivt30]

Hi,

First off, thank you so much for your help on this problem!

To answer your questions:
1. Yes, the last occurrence on the 'data' sheet is the last one entered by the user. This occurs from the click event on the OK button when the user builds the list via VBA.

2. Where do i want the results from the 'data' sheet to go? (great question! i should have clarified this before) The 'Features' sheet originally was planned to be used to show status, as they are drafted and approved. On the ‘Features’ sheet, column C:C should be titled "Prototype", col D:D should be titled "Design (desktop), E:E should be titled "Design (mobile)", and F:F should be titled "Design (tablet)". I have attached an updated workbook to reflect this.
The status for all types of deliverables (on the ‘Features’ sheet), for a Feature would be driven by conditional formatting, which would be based on the data in 'Data' sheet.
I was thinking a formula that identifies the correct row to grab info from for formatting by identifying it w/ either a 1 or a 0 (lets say)… that’s where I am stuck!

I have provided an updated workbook with the correctly titled columns I mentioned above. I have also included your formula in column F.

In the attached workbook – as an example: for the Feature: Generic in column A:A on 'data' sheet (highlighted blue), the one in Row 9 is the latest according to the time stamp. Col B and C match, this is a requirement - column D:D is less relevant. For the Home Page scenario: the Home Page prototype, for the Desktop, went from Drafted, to Approved, and back to Drafted again. this could occur if a new requirement is received from the client for the Home Page, after it was approved. This is a key scenario in this project.

So in this case, i would want to grab the latest instance of Home Page, where B and C are the same BUT identify the one with the latest time stamp in this case.

I surely hope this is helps, please let me know if you have questions and again, thank you!
Heat map 6-25 Updated.xlsm

[Miroslav R.]

Man,
lets clarify You requirements.
From updated file, I can see:
-sheets 'Features' contain col C:F with headers:
Prototype
Design (desktop)
Design (Mobile)
Design (Tablet)

Sheet 'Data' contain col B with possible status:
Prototype
Design
and col C with possible status:
Desktop
Tablet Breakpoint
Mobile Design

Now tell me which combination od col B:C in 'Data' sheet should be passed to each one of columns C:F on 'Features' since it is not clear for me at all.

Thanks