Unable get result NOT(ISBLANK()), nested IFs

[JPARKHURST]

Job Set
Jobs
- Finished Good Job
- Manufactured Parts Jobs

I have a table which shows me purchased parts shortages. I want to concatenate shortages for similar Job Sets. Now,my table auto-produces the Job Set for Jobs that are for the finished goods. I manually input Job Sets for non-finished goods Jobs.

What I am wanting to do is concatenate two fields based upon a comparison of the Jobset and NonFG Jobset fields.

Now, i don't have a problem getting my results with the working formula below, but I need to add the following checks:

• Check if k1=k2;
• check k1="-" and prevent two matching "-"'s;
• check L1 to see if it is blank,
• Compare K1 TO K2; K1 TO L2; L1 TO K2; L1 TO L2 for a match

This was my old formula i used before I had to worry about Non FG Job Sets. It shows what the purchased part number is, and when it is expected to arrive.
=IF(B1=B2, CONCATENATE(J1," | ",C2, " ",TEXT(F2,"MMM-DD")),CONCATENATE(C2," ",TEXT(F2,"MMM-DD")))

I tried this formula, but it would notwork correctly
VERSION 1.3 TEST
=IF(K1=K2,CONCATENATE(J1," | ",C2, " ",TEXT(F2,"MMM-DD")),IF(NOT(ISBLANK(L2)),IF(K1=L2,CONCATENATE(J1," | ",C2, " ",TEXT(F2,"MMM-DD")),IF(AND(NOT(ISBNLANK(L1)),NOT(ISBLANK(L2))),IF(L1=L2, CONCATENATE(J1," | ",C2, " ",TEXT(F2,"MMM-DD")),CONCATENATE(C2," ",TEXT(F2,"MMM-DD"))))))

Here is my nonWorking Very Basic Formula. It gives me L1=L2 when there is a blank field in L1, despite what I thought was a NOt(ISBLANK()) condition to prevent that. If I take out NOT, then I just get a False reading.
=IF(AND(K1="-",K2="-"),"",IF(K1=K2, "K1=K2", IF(K1=L2,"K1=l2",IF((NOT(ISBLANK(L1))),IF(L1=L2,"L1=L2","CONCATENATE NORMAL"),""))))

Can anybody see in particular why the last basic formula is giving me a L1=L2 when the cell is blank? Is there a better way than using nested IF's?

Thanks for any help in advance.

Jon

 

[JPARKHURST]

Two additions:

1. I also need to still capture the Jobs if they match, as some of my jobs do not have containing jobsets.

Also, here is my table structure, with the basic script in:

DATE PURCHASE UPDATED	JOB	SHORTAGE PART #	DESCRIPTION	PO #	PO DUE DATE	STATUS	BUYER	NOTES	Shortages Compiled	Jobset Search	NonFG Jobset
03/20/17	95368	1002861	Caster	2717	05/05/17	PUR UPDATE	W		1002861 May-05	2495
03/20/17	95377	1008220	Trim	3022	05/05/17	PUR UPDATE	W		L1=L2	2497
03/20/17	99653	1008182	Slide	2336	04/19/17	PUR UPDATE	W		L1=L2	2589
03/02/17	104516	multiple, see note	Legs and cross brace	2940	05/01/17	PUR UPDATE	F	1010058, 1010059, 10010060	CONCATENATE NORMAL	-	2700
03/20/17	106008	1010056	Wood Rod	2758	04/10/17	PUR UPDATE	W	Can cover 350 before PO arrives	CONCATENATE NORMAL	2756
02/13/17	106059	1010431	Trim	2677	03/20/17	PUR UPDATE	F	YOU ARE 4 SHORT	L1=L2	2757
03/20/17	106999	1008182	Slide	2336	04/19/17	PUR UPDATE	W		L1=L2	2783
02/10/17	107211	1003206	Bag Assy	2779	05/17/17	PUR UPDATE	F	Supplier unable to get parts before this	L1=L2	2790
02/10/17	107282	1003295	Bag Assy	2378	03/27/17	PUR UPDATE	F		L1=L2	2793
03/20/17	107294	1003493	Particle Board	2758	04/03/17	PUR UPDATE	W	Date on PO was moved out on 3/15. At that point the job was on 4/10 and the move was okay. Job was moved up to 3/21 on 3/16	L1=L2	2794
03/20/17	107330	12918A44-SS	Lock	2484	04/14/17	PUR UPDATE	W		L1=L2	2796
03/21/17	108189	1008047	Ball Stud	2212	3/23 OR 3/24	PUR UPDATE	W		CONCATENATE NORMAL	-	2818
03/22/17	109397	1012987	Magnet	2760	04/28/17	PUR UPDATE	W	Production will be taking the current on hand out of these magnets. Loss of 260	CONCATENATE NORMAL	2835
03/20/17	110900	1008182	Slide	2336	04/19/17	PUR UPDATE	W		L1=L2	2861
02/21/17	110983	1009151	TRIM	2876	04/04/17	PUR UPDATE	F		L1=L2	2862
03/20/17	112393	1014613	Trim	2329	04/21/17	PUR UPDATE	W		L1=L2	2889
03/20/17	112393	1014614	Trim	2259	04/21/17	PUR UPDATE	W		K1=K2	2889
03/20/17	113089	1005111	Slide	1935	03/28/17	PUR UPDATE	W		L1=L2	2915
03/20/17	113106	1005111	Slide	1935	03/28/17	PUR UPDATE	W		L1=L2	2916
03/20/17	113123	1005111	Slide	1935	03/28/17	PUR UPDATE	W		L1=L2	2917
03/20/17	113140	1005111	Slide	1935	03/28/17	PUR UPDATE	W		L1=L2	2918
03/20/17	113174	1005111	Slide	2745	04/14/17	PUR UPDATE	W		L1=L2	2920
03/20/17	113191	1005111	Slide	2745	04/14/17	PUR UPDATE	W		L1=L2	2921
03/20/17	113208	1005111	Slide	2745	04/14/17	PUR UPDATE	W		L1=L2	2922
03/20/17	113660	1006787	Liner	2976	05/12/17	PUR UPDATE	W		L1=L2	2940
03/20/17	113866	1006781	Liner	2976	05/12/17	PUR UPDATE	W		L1=L2	2943
03/20/17	113931	W13640	Slide			PUR UPDATE	W	Can this job be decreased by 1? Then there will be enough slides.	L1=L2	2945
03/20/17	114172	1006787	Liner	2976	05/12/17	PUR UPDATE	W		L1=L2	2949
03/20/17	116157	9317A11	Gas Spring	2824	04/28/17	PUR UPDATE	W		L1=L2	2977
03/20/17	116891	1010103	Parawood	2517	04/17/17	PUR UPDATE	W		L1=L2	2998
03/02/17	116895	multiple, see note	Legs and cross brace	2940	05/01/17	PUR UPDATE	F	you can run 550 of this job, 1010058, 1010059, 10010060	K1=l2	-	2998

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

Table Structure with original, working formula in - look at Jobsets Search / Non FG Jobset 2998, you can see the Shortages Compiled do not incorporate each other. sEE js 2889 for proper Concatenation.

DATE PURCHASE UPDATED	JOB	SHORTAGE PART #	DESCRIPTION	PO #	PO DUE DATE	STATUS	BUYER	NOTES	Shortages Compiled	Jobset Search	NonFG Jobset
03/20/17	95368	1002861	Caster	2717	05/05/17	PUR UPDATE	W		1002861 May-05	2495
03/20/17	95377	1008220	Trim	3022	05/05/17	PUR UPDATE	W		1008220 May-05	2497
03/20/17	99653	1008182	Slide	2336	04/19/17	PUR UPDATE	W		1008182 Apr-19	2589
03/02/17	104516	multiple, see note	Legs and cross brace	2940	05/01/17	PUR UPDATE	F	1010058, 1010059, 10010060	multiple, see note May-01	-	2700
03/20/17	106008	1010056	Wood Rod	2758	04/10/17	PUR UPDATE	W	Can cover 350 before PO arrives	1010056 Apr-10	2756
02/13/17	106059	1010431	Trim	2677	03/20/17	PUR UPDATE	F	YOU ARE 4 SHORT	1010431 Mar-20	2757
03/20/17	106999	1008182	Slide	2336	04/19/17	PUR UPDATE	W		1008182 Apr-19	2783
02/10/17	107211	1003206	Bag Assy	2779	05/17/17	PUR UPDATE	F	Supplier unable to get parts before this	1003206 May-17	2790
02/10/17	107282	1003295	Bag Assy	2378	03/27/17	PUR UPDATE	F		1003295 Mar-27	2793
03/20/17	107294	1003493	Particle Board	2758	04/03/17	PUR UPDATE	W	Date on PO was moved out on 3/15. At that point the job was on 4/10 and the move was okay. Job was moved up to 3/21 on 3/16	1003493 Apr-03	2794
03/20/17	107330	12918A44-SS	Lock	2484	04/14/17	PUR UPDATE	W		12918A44-SS Apr-14	2796
03/21/17	108189	1008047	Ball Stud	2212	3/23 OR 3/24	PUR UPDATE	W		1008047 3/23 OR 3/24	-	2818
03/22/17	109397	1012987	Magnet	2760	04/28/17	PUR UPDATE	W	Production will be taking the current on hand out of these magnets. Loss of 260	1012987 Apr-28	2835
03/20/17	110900	1008182	Slide	2336	04/19/17	PUR UPDATE	W		1008182 Apr-19	2861
02/21/17	110983	1009151	TRIM	2876	04/04/17	PUR UPDATE	F		1009151 Apr-04	2862
03/20/17	112393	1014613	Trim	2329	04/21/17	PUR UPDATE	W		1014613 Apr-21	2889
03/20/17	112393	1014614	Trim	2259	04/21/17	PUR UPDATE	W		1014613 Apr-21 | 1014614 Apr-21	2889
03/20/17	113089	1005111	Slide	1935	03/28/17	PUR UPDATE	W		1005111 Mar-28	2915
03/20/17	113106	1005111	Slide	1935	03/28/17	PUR UPDATE	W		1005111 Mar-28	2916
03/20/17	113123	1005111	Slide	1935	03/28/17	PUR UPDATE	W		1005111 Mar-28	2917
03/20/17	113140	1005111	Slide	1935	03/28/17	PUR UPDATE	W		1005111 Mar-28	2918
03/20/17	113174	1005111	Slide	2745	04/14/17	PUR UPDATE	W		1005111 Apr-14	2920
03/20/17	113191	1005111	Slide	2745	04/14/17	PUR UPDATE	W		1005111 Apr-14	2921
03/20/17	113208	1005111	Slide	2745	04/14/17	PUR UPDATE	W		1005111 Apr-14	2922
03/20/17	113660	1006787	Liner	2976	05/12/17	PUR UPDATE	W		1006787 May-12	2940
03/20/17	113866	1006781	Liner	2976	05/12/17	PUR UPDATE	W		1006781 May-12	2943
03/20/17	113931	W13640	Slide			PUR UPDATE	W	Can this job be decreased by 1? Then there will be enough slides.	W13640 Jan-00	2945
03/20/17	114172	1006787	Liner	2976	05/12/17	PUR UPDATE	W		1006787 May-12	2949
03/20/17	116157	9317A11	Gas Spring	2824	04/28/17	PUR UPDATE	W		9317A11 Apr-28	2977
03/20/17	116891	1010103	Parawood	2517	04/17/17	PUR UPDATE	W		1010103 Apr-17	2998
03/02/17	116895	multiple, see note	Legs and cross brace	2940	05/01/17	PUR UPDATE	F	you can run 550 of this job, 1010058, 1010059, 10010060	multiple, see note May-01	-	2998

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[FDibbins]

Im trying to picture what your data/layout looks like (and not succeeding too well lol)

1st let me say that if a cell contains anything - even a formula returning "", it is not blank

Based on your NOT working formula....
=IF(countif(K1:K2,"-")=2,"",IF(K1=K2, "K1=K2", IF(K1=L2,"K1=l2",IF(L1<>"",IF(L1=L2,"L1=L2","CONCATENATE NORMAL"),""))))

To simplify your "working" part...
=IF(B1=B2, J1&" | ","")&C2&" "&TEXT(F2,"MMM-DD")

 

[JPARKHURST]

Awesome, I like the way you went with that. Let me play around.

Thank you very much, and I much appreciate some more experienced advice.

Jon