Look if values exist seperately and together, then write Yes or No in cell

[MasterBash]

Look if values exist seperately and together, then write Yes or No in cell

Hello, Table exemple on Sheet1 : 1000 ... 2317 1000 2317

www.vbaexpress.com

Hello,

Table exemple on Sheet1 :

1000	...	2317	1000 2317
1001	...	1187	1001 1187
1002	...	5996, 9666	1002 5996, 9666
1003	...	4862	1003 4862

Currently, I have this formula :

=IF(VLOOKUP(A2&" "&C2;'Sheet1'!D:D;1;FALSE)>0;"Yes";"No")

So it lookups value A2 and C2 in my current sheet, and if it is equal to column D on Sheet1, then Yes.

2 problems in the last 2 rows :
Third row : When there are 2 numbers in column C of sheet1. So 1002 5996, 9666 returns false even if both are available. Sometimes those numbers can be seperated by a comma, sometimes by a dash or other times, simply a space. Sometimes it is also like this : 1002 9666, 5996. It needs to work with 1 or more numbers, as long as those numbers are exact, regardless of the seperators.
Fourth row : By mistake, sometimes user enter extra spaces before/after the numberm resulting in a "No".

How can I modify my formula to make sure that those 2 cases are equal to Yes ?
Unfortunately, VBA is not an option.
Thank you !

 

[DanteAmor]

Try:

=IF(COUNTIF(D:D;TRIM(A4)&" "&TRIM(C4));"Yes";"No")

 

[MasterBash]

I just tested it out and unfortunately, I do not get the desired results. I think I am trying to do too much with the formula. I will try to keep it simple and I think this will fix 50% of the problems we are having:

1002 5996
1002 9666

on Sheet2, but 1002 5996, 9666 on sheet1.
or it can also be reversed like 1002 9666, 5996.
Is there a formula to make those say Yes, that would be nice instead of No with my current formula, regardless of the seperator ?

Thank you.

 

[myall_blues]

See if this helps. It should be better for your row 4 and might fix a few more of your row 3 - it can't fix all of those because you have varying characters between the numbers and/or some numbers missing.

=IF(COUNTIFS(D:D,"=" &TRIM(A2 & " " & C2)&"*"),"Yes","No")

 

[DanteAmor]

I'm not understanding how you have the data on sheet1 and how on sheet2.
You could put about 10 examples with problems of the data on sheet1 and the data on sheet2.
Use the XL2BB tool.

 

[MasterBash]

Here is an example of what I am trying to do.... The column "Expected result" is what it should be.

numbertest.xlsx
	A	B	C	D
1	Order	Attribution	Number	Helper
2	10001000		77889	10001000 77889
3	10001001		1533, 1577	10001001 1533, 1577
4	10001002		1944	10001002 1944
5	10001003		2007-2138	10001003 2007-2138
6	10001004			10001004
7	20001000		5228, 6339	20001000 5228, 6339
8	20001001		5228, 6339	20001001 5228, 6339
9	20001002		4444, 4588, 4688	20001002 4444, 4588, 4688
10	20001003		4444, 4588, 4688	20001003 4444, 4588, 4688
11	20001004		6666	20001004 6666
12	20001005		9998-9999	20001005 9998-9999
13	30001000		5700	30001000 5700
14	30001003		5900	30001003 5900
Sheet1

Cell Formulas
Range	Formula
D2:D14	D2	=A2&" "&C2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A14	Cell Value	duplicates	text	NO

Sheet1 has a helper column as a formula.

numbertest.xlsx
	A	B	C	D	E	F
1	Order	Order available ?	Number	Number available ?	Exact ?	Expected result
2	10001000	Yes	77889	Yes	Yes	Everything is yes, as it should be
3	10001001	Yes	1533	#N/A	Yes	Everything should be yes
4	10001001	Yes	1577	#N/A	Yes	Everything should be yes
5	10001002	Yes	1944	Yes	Yes	Everything is yes, as it should be
6	10001003	Yes	2007	#N/A	Yes	Everything should be yes
7	10001003	Yes	2138	#N/A	Yes	Everything should be yes
8	30001000	Yes	5600	#N/A	#N/A	That is correct, wrong load# on Sheet1
9	30001002	#N/A	5900	Yes	#N/A	That is correct, order does not exist on Sheet1
Sheet2

Cell Formulas
Range	Formula
D2:D9,B2:B9	D2	=IF(VLOOKUP(C2,Sheet1!C:C,1,FALSE)>0,"Yes","No")
E2:E9	E2	=IF(VLOOKUP(A2&"*"&C2&"*",Sheet1!D:D,1,FALSE)>0,"Yes","No")

numbertest.xlsx
	A	B	C	D	E	F
1	Order	Order available ?	Number	Number available ?	Exact ?	Expected result
2	20001000-20001001	#N/A	5008	#N/A	#N/A	Everything should be yes
3	20001000-20001001	#N/A	6339	#N/A	#N/A	Everything should be yes
4	20001002-20001003	#N/A	4444	#N/A	#N/A	Everything should be yes
5	20001002-20001003	#N/A	4588	#N/A	#N/A	Everything should be yes
6	20001002-20001003	#N/A	4688	#N/A	#N/A	Everything should be yes
7	20001004	Yes	6666	Yes	Yes	Everything is yes, as it should be
8	20001005	Yes	9998	#N/A	#N/A	Everything should be yes
9	20001005	Yes	9999	#N/A	#N/A	Everything should be yes
Sheet3

Cell Formulas
Range	Formula
D2:D9,B2:B9	D2	=IF(VLOOKUP(C2,Sheet1!C:C,1,FALSE)>0,"Yes","No")
E2:E9	E2	=IF(VLOOKUP(A2&" "&C2,Sheet1!D:D,1,FALSE)>0,"Yes","No")

Sheet2 and Sheet3 can have different formulas, as long as it works.
B looks up if Order# exists on Sheet1, Yes if it is.
D looks up if Number exists on Sheet1, Yes if it is.
E looks up if column A and C matches with the ones on Sheet1 (Helper column D), yes if it is.

Problems exist when there are 2 orders on the same number or when there are 2 numbers for the same order.

For the extra spaces, - and comma, I can just use the find/replace tool if necessary. The problem above is mainly what I am trying to fix.

Thank you.

 

[DanteAmor]

Problems exist when there are 2 orders on the same number or when there are 2 numbers for the same order.

There are several combinations.
Do you want the solution with macro?

 

[MasterBash]

There are several combinations.
Do you want the solution with macro?

Unfortunately, this will be viewed online only, where VBA macros don't work.

 

[Cubist]

Try this. I didn't use the Helper column.

Book1
	A	B	C	D
1	Order	Attribution	Number	Helper
2	10001000		77889	10001000 77889
3	10001001		1533, 1577	10001001 1533, 1577
4	10001002		1944	10001002 1944
5	10001003		2007-2138	10001003 2007-2138
6	10001004			10001004
7	20001000		5228, 6339	20001000 5228, 6339
8	20001001		5228, 6339	20001001 5228, 6339
9	20001002		4444, 4588, 4688	20001002 4444, 4588, 4688
10	20001003		4444, 4588, 4688	20001003 4444, 4588, 4688
11	20001004		6666	20001004 6666
12	20001005		9998-9999	20001005 9998-9999
13	30001000		5700	30001000 5700
14	30001003		5900	30001003 5900
Sheet1

Cell Formulas
Range	Formula
D2:D14	D2	=A2&" "&C2

Book1
	A	B	C	D	E
1	Order	Order available ?	Number	Number available ?	Exact ?
2	10001000	Yes	77889	Yes	Yes
3	10001001	Yes	1533	Yes	Yes
4	10001001	Yes	1577	Yes	Yes
5	10001002	Yes	1944	Yes	Yes
6	10001003	Yes	2007	Yes	Yes
7	10001003	Yes	2138	Yes	Yes
8	30001000	Yes	5600	No	No
9	30001002	No	5900	Yes	No
Sheet2

Cell Formulas
Range	Formula
D2:D9	D2	=IF(SUM(--ISNUMBER(SEARCH(C2&", ",SUBSTITUTE(Sheet1!$C$2:$C$14,"-",", ")&", ")))>0,"Yes","No")
E2:E9	E2	=IF(B2&D2="YesYes","Yes","No")
B2:B9	B2	=IF(COUNTIF(Sheet1!A:A,A2),"Yes","No")

Book1
	A	B	C	D	E
1	Order	Order available ?	Number	Number available ?	Exact ?
2	20001000-20001001	Yes	5008	No	No
3	20001000-20001001	Yes	6339	Yes	Yes
4	20001002-20001003	Yes	4444	Yes	Yes
5	20001002-20001003	Yes	4588	Yes	Yes
6	20001002-20001003	Yes	4688	Yes	Yes
7	20001004	Yes	6666	Yes	Yes
8	20001005	Yes	9998	Yes	Yes
9	20001005	Yes	9999	Yes	Yes
Sheet3

Cell Formulas
Range	Formula
D2:D9	D2	=IF(SUM(--ISNUMBER(SEARCH(C2&", ",SUBSTITUTE(Sheet1!$C$2:$C$14,"-",", ")&", ")))>0,"Yes","No")
E2:E9	E2	=IF(B2&D2="YesYes","Yes","No")
B2:B9	B2	=IF(SUM(COUNTIF(Sheet1!A:A,TEXTSPLIT(Sheet3!A2,"-")))>0,"Yes","No")

 

[DanteAmor]

Unfortunately, this will be viewed online only, where VBA macros don't work.

OK. I will look for another option with the formulas.

Hi @Cubist , that way the order and number can exist but not necessarily on the same row and the search should verify that they are on the same row.
And I also imagine that the data from sheets 2 and 3 can be on the same sheet, something like this, @MasterBash or am I wrong?

varios 23jul2024.xlsm
	A	B
1	Order	Number
2	10001000	77889
3	10001001	1533
4	10001001	1577
5	10001002	1944
6	10001003	2007
7	10001003	2138
8	30001000	5600
9	30001002	5900
10	20001000-20001001	5008
11	20001000-20001001	6339
12	20001002-20001003	4444
13	20001002-20001003	4588
14	20001002-20001003	4688
15	20001004	6666
16	20001005	9998
17	20001005	9999
Sheet2