CODEWITHSUNDEEP
arraysexcellookupdata-processing
Maks Gordeev
Maks Gordeev

Reputation: 351

Returning value from data array based on several conditions

I have a data table with 3 columns (the look is simplified for clarity, but arrangements and data formats are preserved, 1st column A is Date formatted with my regional settings, but it is NOT text):

Date        Module  Value
27.01.2013  xxxxx   90
27.01.2013  ttttt   100
29.01.2013  aaaaa   110
31.01.2013  aaaaa   50
31.01.2013  mmmmm   200
31.01.2013  ttttt   80
03.02.2013  ttttt   140
04.02.2013  aaaaa   120
07.02.2013  mmmmm   150
07.02.2013  ttttt   90
07.02.2013  aaaaa   190
08.02.2013  aaaaa   210
08.02.2013  aaaaa   90
08.02.2013  aaaaa   170
08.02.2013  ttttt   90
08.02.2013  ttttt   110
08.02.2013  mmmmm   130
09.02.2013  mmmmm   90
09.02.2013  aaaaa   200

The list may be quite long and updates regularly. What I need to achieve is this: on the same sheet I have the following calculated cells placed starting column E (i.e. there's a blank column D between data and calculated area):

MAX last week   Date        Value
aaaaa           09.02.2013  200
mmmmm           07.02.2013  150
ttttt           03.02.2013  140
xxxxx           <empty>     No change

Basically I need to find out from the data array in A:C the following:

  1. For the list of values in E (which correspond to module name in B) Date and corresponding value which is maximal among values for the same module, but for the last week starting today should be returned.
  2. If there are no records for this module during the last week - Date should be empty, and "No change" string should be for Value (as for xxxxx from the sample above, relating to the initial array and assuming today is Feb, 9).

As for the #2, I ended up with the following:

As for #1 - I'm stuck, and that's where I need guidance or advice. I tried different stuff with MATCH, VLOOKUP and similar, but not even close to success: I either have errors or not relevant results.

If that matters - dates are always sorted that more recent are in the end of the list, i.e. new records are added to the bottom. For the same Module may be several records during the same day. In case sample or explanation is not clear - please respond in comments and I'll add more details. Thanks!

Upvotes: 1

Views: 3419

Answers (1)

barry houdini
barry houdini

Reputation: 46331

Try an array formula like this in G2 for value

=IF(COUNTIFS(A$2:A$100,">"&TODAY()-7,B$2:B$100,E2),MAX(IF(A$2:A$100>TODAY()-7,IF(B$2:B$100=E2,C$2:C$100))),"No change")

confirmed with CTRL+SHIFT+ENTER and copied down

and then for date in F2

=IF(G2="No change","",MIN(IF(A$2:A$100>TODAY()-7,IF(B$2:B$100=E2,IF(C$2:C$100=G2,A$2:A$100)))))

If there's more than one date in the last 7 days for that module where max value occurred that will give you the earliest (change MIN to MAX for the latest)

see here for working sample

Upvotes: 3

Related Questions