Matching one instance to Another Field to Find all matches

[greg2423]

I have a file in which the data looks like below. For example, the  FL34-16264B52-CD goes out on ECN0867229 in the CD event out field and comes back In as FL34-16264B52-CE on ECN0867229. If you look at both part lines they both have the same usage  in the CD SFI Assembly field within this ECN. I'm trying to detect within any ECN (this example I'm using ECN0867229). I want to compare my  CD SFI Assembly out fields and in fields within an ECN  and detect change.  If anyone can help me or at least point me in the right direction it would be appreciated. 

Thank you, 

Count(CD_SFI_ASSEMBLY)

NO_PART_PREFIXNO_PART_BASENO_PART_SUFFIXCount(CD_SFI_ASSEMBLY)CD_EVENT_INCD_EVENT_OUTNO_ECNCD_SFI_ASSEMBLY

			96
FL34	16264B52	CE	4	ECN0867229		ECN0867229	TFCABBHA
FL34	16264B52	DE	4	ECN0860307	ECN0867229	ECN0867229	TFCABTTV
FL34	16264B52	DF	4	ECN0867229		ECN0867229	TFCABTTV
FL34	16264B52	CD	4	ECN0860307	ECN0867229	ECN0867229	TFCABBHA
FL34	16264B53	CE	4	ECN0867229		ECN0867229	TFCABBHA
FL34	16264B53	DE	4	ECN0860307	ECN0867229	ECN0867229	TFCABTTV
FL34	16264B53	DF	4	ECN0867229		ECN0867229	TFCABTTV
FL34	16264B53	CD	4	ECN0860307	ECN0867229	ECN0867229	TFCABBHA
FL34	15219A64	CE	4	ECN0860307	ECN0867229	ECN0867229	TFCABAHH
FL34	15219A64	DE	4	ECN0860307	ECN0867229	ECN0867229	TFCABTTR
FL34	15219A64	CF	4	ECN0867229	S4PJCC	ECN0867229	TFCABAHH
FL34	15219A64	DF	4	ECN0867229	S4PJCC	ECN0867229	TFCABTTR
FL34	15219A65	CE	4	ECN0860307	ECN0867229	ECN0867229	TFCABAHH
FL34	15219A65	CF	4	ECN0867229	S4PJCC	ECN0867229	TFCABAHH
FL34	15219A65	DF	4	ECN0860307	ECN0867229	ECN0867229	TFCABTTR
FL34	15219A65	DG	4	ECN0867229	S4PJCC	ECN0867229	TFCABTTR
HL3V	16264B52	BG	4	ECN0860307	ECN0867229	ECN0867229	TFCABTTW
HL3V	16264B52	BH	4	ECN0867229		ECN0867229	TFCABTTW
HL3V	16264B53	BG	4	ECN0860307	ECN0867229	ECN0867229	TFCABTTW
HL3V	16264B53	BH	4	ECN0867229		ECN0867229	TFCABTTW
HL3V	15219A64	BH	4	ECN0860307	ECN0867229	ECN0867229	TFCABTTT
HL3V	15219A64	BJ	4	ECN0867229	S4PJCC	ECN0867229	TFCABTTT
HL3V	15219A65	BH	4	ECN0860307	ECN0867229	ECN0867229	TFCABTTT
HL3V	15219A65	BJ	4	ECN0867229	S4PJCC	ECN0867229	TFCABTTT

[Joe_Mako]

What would you expect for an output from this sample data?

[greg2423]

Hey Joe,

Thanks for the response and the help.. So what I am trying to do is identify changes based on the SFI between the out and the In parts on ECN0867229 for this example.  So if  you look at the tables below I want to line up my out SFIs A to Z  and my in SFIs A to Z. I want a formula that will detect if there is a change between the two. I used a count if in the past in excel. I would check in one cell within the ECN IN SFIs and look at the array of out SFIs to see if anything matched. I then would total each SFI for the IN and OUT and compare each total SFI count to understand if anything has changed. Let me know if anything doesn't make sense. I was thinking I would need to use the summarize field and the Multi Field or Multi-row formula somehow.

Prefix	Base	Suffix	96	ECN IN	ECN out	ECN #	SFI
FL34	16264B52	CE	4	ECN0867229		ECN0867229	TFCABBHA
FL34	16264B52	DE	4	ECN0860307	ECN0867229	ECN0867229	TFCABTTV
FL34	16264B52	DF	4	ECN0867229		ECN0867229	TFCABTTV
FL34	16264B52	CD	4	ECN0860307	ECN0867229	ECN0867229	TFCABBHA
FL34	16264B53	CE	4	ECN0867229		ECN0867229	TFCABBHA
FL34	16264B53	DE	4	ECN0860307	ECN0867229	ECN0867229	TFCABTTV
FL34	16264B53	DF	4	ECN0867229		ECN0867229	TFCABTTV
FL34	16264B53	CD	4	ECN0860307	ECN0867229	ECN0867229	TFCABBHA
FL34	15219A64	CE	4	ECN0860307	ECN0867229	ECN0867229	TFCABAHH
FL34	15219A64	DE	4	ECN0860307	ECN0867229	ECN0867229	TFCABTTR
FL34	15219A64	CF	4	ECN0867229	S4PJCC	ECN0867229	TFCABAHH
FL34	15219A64	DF	4	ECN0867229	S4PJCC	ECN0867229	TFCABTTR
FL34	15219A65	CE	4	ECN0860307	ECN0867229	ECN0867229	TFCABAHH
FL34	15219A65	CF	4	ECN0867229	S4PJCC	ECN0867229	TFCABAHH
FL34	15219A65	DF	4	ECN0860307	ECN0867229	ECN0867229	TFCABTTR
FL34	15219A65	DG	4	ECN0867229	S4PJCC	ECN0867229	TFCABTTR
HL3V	16264B52	BG	4	ECN0860307	ECN0867229	ECN0867229	TFCABTTW
HL3V	16264B52	BH	4	ECN0867229		ECN0867229	TFCABTTW
HL3V	16264B53	BG	4	ECN0860307	ECN0867229	ECN0867229	TFCABTTW
HL3V	16264B53	BH	4	ECN0867229		ECN0867229	TFCABTTW
HL3V	15219A64	BH	4	ECN0860307	ECN0867229	ECN0867229	TFCABTTT
HL3V	15219A64	BJ	4	ECN0867229	S4PJCC	ECN0867229	TFCABTTT
HL3V	15219A65	BH	4	ECN0860307	ECN0867229	ECN0867229	TFCABTTT
HL3V	15219A65	BJ	4	ECN0867229	S4PJCC	ECN0867229	TFCABTTT

OUT ECN

Prefix	Base	Suffix	ECN IN	ECN OUT	ECN #	SFI
FL34	15219A64	CE		ECN0867229	ECN0867229	TFCABAHH
FL34	15219A64	DE		ECN0867229	ECN0867229	TFCABTTR
HL3V	15219A64	BH		ECN0867229	ECN0867229	TFCABTTT
FL34	15219A65	CE		ECN0867229	ECN0867229	TFCABAHH
FL34	15219A65	DF		ECN0867229	ECN0867229	TFCABTTR
HL3V	15219A65	BH		ECN0867229	ECN0867229	TFCABTTT

IN ECN

Prefix	Base	Suffix	ECN IN	ECN OUT	ECN #	SFI
FL34	15219A64	CF	ECN0867229		ECN0867229	TFCABAHH
FL34	15219A64	DF	ECN0867229		ECN0867229	TFCABTTR
HL3V	15219A64	BG	ECN0867229		ECN0867229	TFCABTTT
FL34	15219A65	CF	ECN0867229		ECN0867229	TFCABAHH
FL34	15219A65	DF	ECN0867229		ECN0867229	TFCABTTR
HL3V	15219A65	BG	ECN0867229		ECN0867229	TFCABTTT

[Joe_Mako · Accepted solution]

Attached is a workflow to finding these issues:

[greg2423]

Joe,

Thanks for the help!!