Some advanced Index Match (or something else?)

[Alhelor]

Hello folks,


please take a look at this production table. I need the formulas for A16 and D16:

Question: What is the earliest date at which a computer is consumed to create a car within the same project?


A Computer is consumed to produce a Car when they are both part of the same project.


What you can see in the table:


The earliest date at which a Computer is consumed is 01.01.1970. However, it’s project E which doesn’t produce Cars. Projects A and B are independent of each other. Projects C, D and F are the only ones where a Computer is consumed to produce a Car. In project F the relevant date cell is left blank and should be disregarded. In project D, the Computer is consumed earlier than in project C. Thus, D16 should return 18.11.1999 and A16 should return D.


Please help me with the formulas.


Thank you!

P.S.: The fact that in the dates some items are produced before their ingredients are consumed should be disregarded. The production date is not part of this task.

 

[Alhelor]

Re: Some advanced Index Match (or something else?) - Please help!

Hey Eric,

Thanks for the reply!

The D18 formula still contains an error. I underlined that part: =SMALL(IF($A$2:$A$13=TRANSPOSE($A$2:$A$13);IF($B$2:$B$13=C18;IF($C$2:$C$13="C";IF(TRANSPOSE($B$2:$B$13)=B18;IF(TRANSPOSE($C$2:$C$13)="P";IF($D$2:$D$13$D$2:$D$13);IF(ISNUMBER($D$2:$D$13);IF(ISNUMBER(TRANSPOSE($D$2:$D$13));$D$2:$D$13))))))));1)

I feel ashamed I haven't taken the time yet to understand that formula myself and fix it.

The A18 formula works when I put in the correct date in D18 manually.

 

[Eric W]

Re: Some advanced Index Match (or something else?) - Please help!

Arrgh! I've been caught more times by that feature of the board software more times in the last week than the last year! I know I need to add a space after a < , but after a while I tend to forget.

Try:

=SMALL(IF($A$2:$A$13=TRANSPOSE($A$2:$A$13);IF($B$2:$B$13=C18;IF($C$2:$C$13="C";IF(TRANSPOSE($B$2:$B$13)=B18;IF(TRANSPOSE($C$2:$C$13)="P";IF($D$2:$D$13< TRANSPOSE($D$2:$D$13);IF(ISNUMBER($D$2:$D$13);IF(ISNUMBER(TRANSPOSE($D$2:$D$13));$D$2:$D$13))))))));1)

 

[inactiveuserps07]

Re: Some advanced Index Match (or something else?) - Please help!

@Alhelor, I'm not sure if you tried Eric W.'s suggested solution or if it worked, but here is another:

1. Format your results as a table. The name is unimportant; we'll just want to be able to use structured references in the formulas. Make sure your headers for this newly formed table are exactly as you presented in the original post: Project | Produced | Consumed | Earliest Consumption Date. You can change them later if you want.

2. Once the table is formatted, enter the following formulas:

In the table cell directly beneath Earliest Consumption Date (Entered with CSE):

=MIN(IF(B$2:B$13=[@Consumed],IF(C$2:C$13="C",IF(D$2:D$13>1000,IF(ISNUMBER(MATCH(A$2:A$13&[@Produced]&"P",A$2:A$13&B$2:B$13&C$2:C$13,0)),D$2:D$13,"NO MATCH")))))

In the table cell directly beneath Project(Entered with CSE):

=IFERROR(INDEX(A$2:A$13,MATCH([@Consumed]&"C"&[@[Earliest Consumption Date]],B$2:B$13&C$2:C$13&D$2:D$13,0)),"NO MATCH")

 

[inactiveuserps07]

Re: Some advanced Index Match (or something else?) - Please help!

@Alhelor, actually, the MIN() formula will always give at least a zero, meaning all conditions are FALSE. So as written above, it won't ever throw a "NO MATCH." I got tricky and found a way to use IFERROR() by forcing a situation where a zero (i.e., all FALSE) will trip the "NO MATCH":

=IFERROR((SQRT(MIN(IF(B$2:B$13=[@Consumed],IF(C$2:C$13="C",IF(D$2:D$13>1000,IF(ISNUMBER(MATCH(A$2:A$13&[@Produced]&"P",A$2:A$13&B$2:B$13&C$2:C$13,0)),D$2:D$13)))))-1)^2)+1,"NO MATCH")

 

[Eric W]

Re: Some advanced Index Match (or something else?) - Please help!

@Erik: I haven't tried your formula, but I don't see in it where you handle the requirement that the Consumed date precede the Produced date. Also, it's probably worth noting that your second formula can possibly, in very rare situations, return the wrong Project. You'd need to have a project with the right consumed item, a C next to that, a matching date, and no produced item in that group, and precede the actual matching project.

Your SQRT(x-1)^2+1 structure is clever (and it has a place in some situations), but I'd recommend just changing the MIN to SMALL( ,1). Unlike MIN, SMALL will generate an error if there are no matches. I think that would be a bit less confusing to someone trying to decode your formula.

 

[inactiveuserps07]

Re: Some advanced Index Match (or something else?) - Please help!

@Eric W., the OP stated specifically, "P.S. The fact that in the dates some items are produced before their ingredients are consumed should be disregarded. The production date is not part of this task."

Good call on the SMALL( ,1). Thus:

=IFERROR(SMALL(IF(B$2:B$13=[@Consumed],IF(C$2:C$13="C",IF(D$2:D$13>1000,IF(ISNUMBER(MATCH(A$2:A$13&[@Produced]&"P",A$2:A$13&B$2:B$13&C$2:C$13,0)),D$2:D$13)))),1),"NO MATCH")

 

[Fazza]

Re: Some advanced Index Match (or something else?) - Please help!

It seems to me the task is unnecessarily complicated by the data structure. If the data were set up better, the task is simple.

Running this query (just to help get a result, not address the fundamental data structure) - and I renamed some fields for simplicity - helped the answers drop out easily

SELECT A.Project, A.Resource, B.Resource, B.DateOccrdFROM Inputs A, Inputs B
WHERE A.[C OR P] = 'P' AND B.[C OR P] = 'C' AND A.Project = B.Project
ORDER BY 1, 2

returned this dataset,

[TABLE="width: 72"]
<tbody>[TR]
[TD="width: 72"]Project|Resource|Resource2|DateOccrd[/TD]
[/TR]
[TR]
[TD]C|Cake|Computer|1-Aug-19[/TD]
[/TR]
[TR]
[TD]C|Car|Computer|1-Aug-19[/TD]
[/TR]
[TR]
[TD]D|Car|Cookies|19-Feb-11[/TD]
[/TR]
[TR]
[TD]D|Car|Computer|18-Nov-99[/TD]
[/TR]
[TR]
[TD]E|Bike|Computer|1-Jan-70[/TD]
[/TR]
[TR]
[TD]F|Car|Computer|[/TD]
[/TR]
</tbody>[/TABLE]

The query was via MS Query, such as ALT-D-D-N but you can run the query however you do in your Excel version.

You'll probably find doing anything much with the current data structure will be difficult: whereas if the data is well structured things should be simple - basic formulas or pivot tables or whatever.

cheers

 

[Eric W]

Re: Some advanced Index Match (or something else?) - Please help!

@Eric W., the OP stated specifically, "P.S. The fact that in the dates some items are produced before their ingredients are consumed should be disregarded. The production date is not part of this task."

Hmm, missed that. Anhelor, this means you can remove the underlined part from my formula in post #12, and its corresponding close parenthesis.

Also, some other good suggestions here worth looking at.

 

[Alhelor]

Re: Some advanced Index Match (or something else?) - Please help!

Hmm, missed that. Anhelor, this means you can remove the underlined part from my formula in post #12, and its corresponding close parenthesis.

Also, some other good suggestions here worth looking at.

Thank you for your responses. While it does work in the example, in the real file, which is built exactly the same way, just much more data (and partly missing data), it returns #NUM! when there's clearly a solution. When I enter it without CSE, it returns 00.01.1900. I already checked the formula and formatting multiple times, but I don't get what could be the source of this error. I cannot reproduce it in the example, either.

Any idea/clues?

 

[Alhelor]

Re: Some advanced Index Match (or something else?) - Please help!

The only difference I see is that in the real file the columns aren't directly next to each other. There are also columns in between that are irrelevant for this calculation. Does it somehow affect the TRANSPOSE function within the IF?