Hello All,
I have a set of dynamic data that is somewhat inconsistent. My objective is to pull text from the set of data based on a specific unchanging criteria. However, at times -as I've already indicated because of the dynamic data- not all of the criteria is able to be fulfilled and it ends up displaying an error. I will try to display a small sample size that best represents the data, along with my array formula that I am trying to use to pull the data. if someone could suggest a solution that would be excellent.
=INDEX('EXPORTED FAR'!A:N,MATCH(1,('EXPORTED FAR'!A:A=$B32)*('EXPORTED FAR'!B:B="N10")*('EXPORTED FAR'!D:D=$B$2)*('EXPORTED FAR'!E:E=$B$3),0),14)&"_"&INDEX('EXPORTED FAR'!A:N,MATCH(1,('EXPORTED FAR'!A:A=$B32)*('EXPORTED FAR'!B:B="D10")*('EXPORTED FAR'!D:D=$B$2)*('EXPORTED FAR'!E:E=$B$3),0),14)
THE CRITERIA: THE DATE, 4 DIGIT NUMBER, 3 DIGIT CODE, 3 DIGIT CODE
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]EXTRA CRITERIA[/TD]
[TD]3416[/TD]
[TD]S42[/TD]
[/TR]
[TR]
[TD]DATE[/TD]
[TD]COMMENTS (RESULTS FROM FORMULA with data below)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="class: xl65, width: 77, align: right"]4/26/2019[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 396"]
<tbody>[TR]
[TD="class: xl65, width: 396"]w/s , mck , g/s _c/s mk to rm inst g/s prep d/b 8' advancement[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="class: xl65, width: 77, align: right"]4/27/2019[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 396"]
<tbody>[TR]
[TD="class: xl65, width: 396"]w/s , mck , g/s _c/s comp g/s mk pad prep d/b 8' advancement[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="class: xl65, width: 77, align: right"]4/28/2019[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]#value[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 626"]
<colgroup><col><col span="3"><col></colgroup><tbody>[TR]
[TD]DATE[/TD]
[TD]3 DIGIT CODE [/TD]
[TD] 4 DIGIT CODE[/TD]
[TD] 3 DIGIT CODE[/TD]
[TD]COMMENTS[/TD]
[/TR]
[TR]
[TD="align: right"]4/26/2019 [/TD]
[TD] N10[/TD]
[TD="align: right"]3416[/TD]
[TD] S42[/TD]
[TD]w/s , mck , g/s [/TD]
[/TR]
[TR]
[TD="align: right"]4/26/2019[/TD]
[TD] D10[/TD]
[TD="align: right"]3416[/TD]
[TD] S42[/TD]
[TD]c/s mk to rm inst g/s prep d/b 8' advancement[/TD]
[/TR]
[TR]
[TD="align: right"]4/27/2019[/TD]
[TD] D10[/TD]
[TD="align: right"]3416 [/TD]
[TD]S42[/TD]
[TD]c/s comp g/s mk pad prep d/b 8' advancement[/TD]
[/TR]
[TR]
[TD="align: right"]4/27/2019 [/TD]
[TD] N10[/TD]
[TD="align: right"]3416[/TD]
[TD] S42[/TD]
[TD]w/s , mck , g/s [/TD]
[/TR]
[TR]
[TD="align: right"]4/28/2019[/TD]
[TD] D10[/TD]
[TD="align: right"]3416[/TD]
[TD] S42[/TD]
[TD]w/s , mck , g/s [/TD]
[/TR]
[TR]
[TD="align: right"]4/28/2019[/TD]
[TD] D10[/TD]
[TD="align: right"]3416 [/TD]
[TD]S42[/TD]
[TD]c/s comp g/s mk pad prep d/b 8' advancement[/TD]
[/TR]
</tbody>[/TABLE]
sorry for the format^
You will notice in the last two rows the two entries for the date 4/28/2019 both have D10 as the second 3 digit code, this is not an error. However, this is what causes the error in my array formula. The array formula is looking for an entry with D10 and an entry with N10--> for clarity this code refers to a dayshift and a nightshift. Sometimes in some working areas the only work a dayshift/nightshift whatever the case may be. So I'm not always able to fulfill the criteria and then its puts out an error. Or sometimes like also displayed with the data table above i have two comments for the same criteria and i would like both comments displayed. If more clarity is required please let me know.
I have a set of dynamic data that is somewhat inconsistent. My objective is to pull text from the set of data based on a specific unchanging criteria. However, at times -as I've already indicated because of the dynamic data- not all of the criteria is able to be fulfilled and it ends up displaying an error. I will try to display a small sample size that best represents the data, along with my array formula that I am trying to use to pull the data. if someone could suggest a solution that would be excellent.
=INDEX('EXPORTED FAR'!A:N,MATCH(1,('EXPORTED FAR'!A:A=$B32)*('EXPORTED FAR'!B:B="N10")*('EXPORTED FAR'!D:D=$B$2)*('EXPORTED FAR'!E:E=$B$3),0),14)&"_"&INDEX('EXPORTED FAR'!A:N,MATCH(1,('EXPORTED FAR'!A:A=$B32)*('EXPORTED FAR'!B:B="D10")*('EXPORTED FAR'!D:D=$B$2)*('EXPORTED FAR'!E:E=$B$3),0),14)
THE CRITERIA: THE DATE, 4 DIGIT NUMBER, 3 DIGIT CODE, 3 DIGIT CODE
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]EXTRA CRITERIA[/TD]
[TD]3416[/TD]
[TD]S42[/TD]
[/TR]
[TR]
[TD]DATE[/TD]
[TD]COMMENTS (RESULTS FROM FORMULA with data below)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="class: xl65, width: 77, align: right"]4/26/2019[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 396"]
<tbody>[TR]
[TD="class: xl65, width: 396"]w/s , mck , g/s _c/s mk to rm inst g/s prep d/b 8' advancement[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="class: xl65, width: 77, align: right"]4/27/2019[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 396"]
<tbody>[TR]
[TD="class: xl65, width: 396"]w/s , mck , g/s _c/s comp g/s mk pad prep d/b 8' advancement[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="class: xl65, width: 77, align: right"]4/28/2019[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]#value[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 626"]
<colgroup><col><col span="3"><col></colgroup><tbody>[TR]
[TD]DATE[/TD]
[TD]3 DIGIT CODE [/TD]
[TD] 4 DIGIT CODE[/TD]
[TD] 3 DIGIT CODE[/TD]
[TD]COMMENTS[/TD]
[/TR]
[TR]
[TD="align: right"]4/26/2019 [/TD]
[TD] N10[/TD]
[TD="align: right"]3416[/TD]
[TD] S42[/TD]
[TD]w/s , mck , g/s [/TD]
[/TR]
[TR]
[TD="align: right"]4/26/2019[/TD]
[TD] D10[/TD]
[TD="align: right"]3416[/TD]
[TD] S42[/TD]
[TD]c/s mk to rm inst g/s prep d/b 8' advancement[/TD]
[/TR]
[TR]
[TD="align: right"]4/27/2019[/TD]
[TD] D10[/TD]
[TD="align: right"]3416 [/TD]
[TD]S42[/TD]
[TD]c/s comp g/s mk pad prep d/b 8' advancement[/TD]
[/TR]
[TR]
[TD="align: right"]4/27/2019 [/TD]
[TD] N10[/TD]
[TD="align: right"]3416[/TD]
[TD] S42[/TD]
[TD]w/s , mck , g/s [/TD]
[/TR]
[TR]
[TD="align: right"]4/28/2019[/TD]
[TD] D10[/TD]
[TD="align: right"]3416[/TD]
[TD] S42[/TD]
[TD]w/s , mck , g/s [/TD]
[/TR]
[TR]
[TD="align: right"]4/28/2019[/TD]
[TD] D10[/TD]
[TD="align: right"]3416 [/TD]
[TD]S42[/TD]
[TD]c/s comp g/s mk pad prep d/b 8' advancement[/TD]
[/TR]
</tbody>[/TABLE]
sorry for the format^
You will notice in the last two rows the two entries for the date 4/28/2019 both have D10 as the second 3 digit code, this is not an error. However, this is what causes the error in my array formula. The array formula is looking for an entry with D10 and an entry with N10--> for clarity this code refers to a dayshift and a nightshift. Sometimes in some working areas the only work a dayshift/nightshift whatever the case may be. So I'm not always able to fulfill the criteria and then its puts out an error. Or sometimes like also displayed with the data table above i have two comments for the same criteria and i would like both comments displayed. If more clarity is required please let me know.