Outputting Values to New Column Based on ID and IF condition

[bdav1216]

See the attached samples:

When Column A (ID) has the same value -> Evalulate IF Column D (Type) = "FFFT" AND Column I (Delivery) = "ABC w/ I"

When these values exist for the same Column A (ID), output the values of Column J (Relation) Into Column K (Expected Value).

- If there are blank values in Column J, output '-'.
- If there are more than one instance where Colume D (Type) = "FFFT" AND Column I (Delivery) = "ABC w/ I", output ALL values separated with a "/"
- If there are no instances where Colume D (Type) = "FFFT" AND Column I (Delivery) = "ABC w/ I", output 'No Scenarios'

[Limor_OP]

Hi

in k2 and down:

Please Login or Register  to view this content.

The formula will cover only 3 instances that are separated by "/"

[bdav1216]

Hello,

For a few ID's (11454 and 1407), I am receiving a #NUM! error.

Can you take a look at these? I can re-upload my sample if that helps.

[Limor_OP]

Doesnt happen in my file....
Please upload the file where you are using the formula

[bdav1216]

I attached my sample with those messages.

[Limor_OP]

Formula should be in K2

[bdav1216]

I had updated the sample (attached) in Column L with the formula you had provided.
On my end:

ID: 11454 has 4 rows
The first two are #NUM! and the last rows are '-'.

ID: 1407 has 4 rows and all are equal to #NUM!

I am using Microsoft Office Professional Plus 2016.

[Limor_OP]

Sorry, didnt tell you that it's a n array formula meaning:

Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

[Limor_OP]

or you can just replace the SUM with SUMPRODUCT;

=IF(SUM(($A$2:$A$46=A2)*($D$2:$D$46&$I$2:$I$46<>"FFFTABC w/ I"))>=COUNTIF($A$2:$A$46,A2),"No Scenarios",IF(J2="","-",INDEX($J$1:$J$46,AGGREGATE(15,6,(ROW($A$2:$A$46)/(($A$2:$A$46=A2)*($D$2:$D$46="FFFT")*($I$2:$I$46="ABC W/ I"))),1))&IFERROR("/"&INDEX($J$1:$J$46,AGGREGATE(15,6,(ROW($I$2:$I$46)/(($A$2:$A$46=A2)*($D$2:$D$46="FFFT")*($I$2:$I$46="ABC W/ I"))),2)),"")&IFERROR("/"&INDEX($J$1:$J$46,AGGREGATE(15,6,(ROW($I$2:$I$46)/(($A$2:$A$46=A2)*($D$2:$D$46="FFFT")*($I$2:$I$46="ABC W/ I"))),3)),"")))

[bdav1216]

Thanks! Any way to do this without an array?

[Limor_OP]

with sumproduct as suggested in the last post

[bdav1216]

OK, i'm trying using SUMPRODUCT, but still getting that #NUM! error still in those same columns.

Any idea on how to address that?

[Limor_OP]

Please attach again your file with the errors, I will look at that

[Bo_Ry]

Please try at K2

=IFNA(LOOKUP(2,1/($A$2:$A$46=A2)/($D$2:$D$46="FFFT")/($I$2:$I$46="ABC w/ I"),$J$2:$J$46)&MID(REPT("/-",COUNTIFS($A$2:$A$46,A2,$D$2:$D$46,"FFFT",$I$2:$I$46,"ABC w/ I",$J$2:$J$46,"")),2,20),"No Secenarios")

[Limor_OP]

Bo_Ry - Cells 18-27 and 44-46 do not comply with what he asks...he wants all scenarios met to be displayed in the cell...

[Bo_Ry]

@belinda Thanks, without textjoin function need multiple ampersands.
K2

Please Login or Register  to view this content.

[bdav1216]

That last formula is outputting what I need!

Two questions to see if this is possible.

1)
In my data, there are a few scenario's where the output just keeps repeating beacuase of the data.

CCCR/CCCR/CCCR/CCCR/CCCR/TPAR/TPAR/TPAR/TPAR/TPAR/TPAR/TPAR/TPAR/TPAR/TPAR

Can that be capped to show only the first instance of each scenario? Something like CCCR/TPAR instead of every instance?

2) My real data has an example like "Mickey Mouse" but that "/" creates "Mickey/Mouse Any way to address that so any data with spaces do not have that slash, just between actual differences in the values?

I can upload a few additional examples if needed.

Thank you !

[Bo_Ry]

Please try

Please Login or Register  to view this content.

[bdav1216]

Thank you so much, this looks very good.

One last update.

As I'm reviewing the data, can we cap those situations to where there is no data in Column J?

The ID 1130 is a great example where we currently output -/-/-/-/-
For that scenario, where there are more than 1 row that qualifies, but all data is blank in Column J, can we do a '-' instead?

This would also be great to apply to an example where it currently outputs: -/-/-/-/-/-/CCCR to just do -/CCCR (basically allow for only instance of '-' to be output if there are multiple rows that qualify.

[Bo_Ry]

Please provide a file with more representative data and the expected result.

[bdav1216]

I added some additional examples beginning in line 47 with the expected result.

These would be these examples where I would like to remove duplicate '-' values but keep the rest of the formula as-is.

Column M is the current formula, Column N is the expected value.

[Bo_Ry]

Please try

=IF(COUNTIFS($A$2:$A$130,A2,$D$2:$D$130,"FFFT",$I$2:$I$130,"ABC w/ I"),
SUBSTITUTE(SUBSTITUTE(TRIM(
IF(COUNTIFS($A$2:$A$130,A2,$D$2:$D$130,"FFFT",$I$2:$I$130,"ABC w/ I",$J$2:$J$130,""),"-",)
&IF(COUNTIFS($A$2:$A$130,A2,$D$2:$D$130,"FFFT",$I$2:$I$130,"ABC w/ I",$J$2:$J$130,"CCCR")," CCCR",)
&IF(COUNTIFS($A$2:$A$130,A2,$D$2:$D$130,"FFFT",$I$2:$I$130,"ABC w/ I",$J$2:$J$130,"TPAR")," TPAR",)
&IF(COUNTIFS($A$2:$A$130,A2,$D$2:$D$130,"FFFT",$I$2:$I$130,"ABC w/ I",$J$2:$J$130,"DPAC")," DPAC",)
&IF(COUNTIFS($A$2:$A$130,A2,$D$2:$D$130,"FFFT",$I$2:$I$130,"ABC w/ I",$J$2:$J$130,"VVVR")," VVVR",)
&IF(COUNTIFS($A$2:$A$130,A2,$D$2:$D$130,"FFFT",$I$2:$I$130,"ABC w/ I",$J$2:$J$130,"Mickey Mouse")," Mickey_Mouse",))," ","/"),"_"," "),"No Secenarios")