Replace formula in this code

[RAJESH1960]

Hello guys, I need your help to replace the formula in the code with the new formula.

With wsDestination.Range(DestinationRemarksColumn & "2")
        .FormulaArray = "=IFERROR(IF(ROW(B2)<=SMALL(IF((ABS(" & DSCol & "2-$" & DSCol & "$2:$" & DSCol & _
                "$20000)<=1)*(C2=$C$2:$C$20000)*(B2=$B$2:$B$20000),ROW($A$2:$A$20000),""""),xxxxxx)" & _
                ",""Matched"",NA()),NA())"                                                              '   Formula to insert into 'Remarks' column
        .Replace "xxxxxx", FormulaReplacementString1, xlPart                                            '   Insert additional string into formula
    End With

New formula

=IF(MATCH(1,((ABS(G2-$G$2:$G$23200)<=1))*(C2=$C$2:$C$23200)*(F2=$F$2:$F$23200)*(B2<>$B$2:$B$23200),0),"Matched","")

Old formula

=IF(MATCH(1,((ABS(G2-$G$2:$G$23200)<=1))*(C2=$C$2:$C$23200)*(B2<>$B$2:$B$23200),0),"Matched","")

 

[RAJESH1960]

In the sheet I tested, I am getting 26 matches out of 40 using the new formula with control + shift + enter.

mikerickson's formula.xlsx
	A	B	C	D	E	F	G	H	I	J
2	1	PORTAL	07XXXTB0431R1XD		0000CR2019000893	22-05-2019	900.00			Matched
3	108	TALLY	07XXXTB0431R1XD			22-05-2019	901.00			Matched
4	2	PORTAL	07XXXTB0431R1XD		0000CR2019001162	05-06-2019	900.00			Matched
5	3	PORTAL	07XXXTB0431R1XD		0000CR2019001163	05-06-2019	900.00			Matched
6	4	PORTAL	07XXXTB0431R1XD		0000CR2019001164	05-06-2019	900.00			Matched
7	109	TALLY	07XXXTB0431R1XD			05-06-2019	899.00			Matched
8	110	TALLY	07XXXTB0431R1XD		0000CR2019001163	05-06-2019	900.00			Matched
9	111	TALLY	07XXXTB0431R1XD			05-06-2019	901.00			Matched
10	5	PORTAL	07XXXTB0431R1XD		0000CR2019001809	12-07-2019	900.00			Matched
11	112	TALLY	07XXXTB0431R1XD			12-07-2019	900.00			Matched
12	6	PORTAL	07XXXTB0431R1XD		0000CR2019002131	26-07-2019	900.00			Matched
13	113	TALLY	07XXXTB0431R1XD			26-07-2019	901.00			Matched
14	7	PORTAL	07XXXTB0431R1XD		0000CR2019002276	03-08-2019	900.00			Matched
15	114	TALLY	07XXXTB0431R1XD			03-08-2019	899.50			Matched
16	8	PORTAL	07XXXTB0431R1XD		0000CR2019003076	19-09-2019	900.00			Matched
17	115	TALLY	07XXXTB0431R1XD			19-09-2019	900.50			Matched
18	9	PORTAL	07XXXTB0431R1XD		0000CR2019003126	23-09-2019	900.00			Matched
19	116	TALLY	07XXXTB0431R1XD		3126	23-09-2019	900.00			Matched
20	10	PORTAL	07XXXTB0431R1XD		0000CR2019005946	28-02-2020	900.00			Matched
21	117	TALLY	07XXXTB0431R1XD			28-02-2020	900.00			Matched
22	11	PORTAL	19XXECS5031Q1XV		GST-065/19-20	06-02-2020	1881.00			Matched
23	118	TALLY	19XXECS5031Q1XV		65	06-02-2020	1881.50			Matched
24	12	PORTAL	19XXECS5031Q1XV		GST-052/19-20	25-12-2019	2520.00			#N/A
25	13	PORTAL	19XXECS5031Q1XV		GST-053/19-20	04-01-2020	2520.00			Matched
26	119	TALLY	19XXECS5031Q1XV		53	04-01-2020	2520.00			Matched
27	14	PORTAL	19XXECS5031Q1XV		GST-057/19-20	13-01-2020	2520.00			Matched
28	120	TALLY	19XXECS5031Q1XV		57	13-01-2020	2519.50			Matched
29	121	TALLY	27XXOFD3597R1XC		446	01-01-2020	7520.90			#N/A
30	122	TALLY	27XXOFD3597R1XC		447	01-01-2020	7521.00			#N/A
31	123	TALLY	27XXOFD3597R1XC		448	01-01-2020	7522.00			#N/A
32	124	TALLY	27XXOFD3597R1XC		444	01-01-2020	7521.45			#N/A
33	125	TALLY	27XXOFD3597R1XC		445	01-01-2020	7521.45			#N/A
34	126	TALLY	27XXOFD3597R1XC		443	01-01-2020	7521.50			#N/A
35	15	PORTAL	27XXOFD3597R1XC		DEI-00438/19-20	29-12-2019	7521.58			#N/A
36	16	PORTAL	27XXOFD3597R1XC		DEI-00443/19-20	30-12-2019	7521.58			#N/A
37	17	PORTAL	27XXOFD3597R1XC		444	30-12-2019	7521.58			#N/A
38	18	PORTAL	27XXOFD3597R1XC		DEI-00445/19-20	30-12-2019	7521.58			#N/A
39	19	PORTAL	27XXOFD3597R1XC		DEI-00446/19-20	31-12-2019	7521.58			#N/A
40	20	PORTAL	27XXOFD3597R1XC		DEI-00447/19-20	31-12-2019	7521.58			#N/A
41	21	PORTAL	27XXOFD3597R1XC		448	31-12-2019	7521.58			#N/A
Matched

Cell Formulas
Range	Formula
J2:J41	J2	=IF(MATCH(1,((ABS(G2-$G$2:$G$23200)<=1))*(C2=$C$2:$C$23200)*(F2=$F$2:$F$23200)*(B2<>$B$2:$B$23200),0),"Matched","")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[johnnyL]

So again I will ask you, Do you want to use the Excel acceptable version of a formula from post #1, or are you wanting to try and fail using the non acceptable formula that you proposed that has the 'NA' stuff at the end?

 

[RAJESH1960]

I am not getting it. It is the same formula I used in the sheet that is in post #1. Only thing when you try to copy the formula and paste it in the thread, maybe it looks different i.e., {} are missing

 

[johnnyL]

You posted a formula in post #19 here. I asked if that was what you wanted to use. You responded with 'Yes'. That formula is not the same as the 'New' formula in post #1.

 

[johnnyL]

So show me what vba code you will try to use that reflects the code from post #1 instead of the formula you tried to use in post #19.

 

[RAJESH1960]

    With wsDestination.Range(DestinationRemarksColumn & "2")
        .FormulaArray = "=IFERROR(IF(ROW(B2)<=SMALL(IF((ABS(" & DSCol & "2-$" & DSCol & "$2:$" & DSCol & _
                $23200)<=1))*(C2=$C$2:$C$23200)*(F2=$F$2:$F$23200)*(B2<>$B$2:$B$23200),ROW($A$2:$A$23200),""""),xxxxxx)" & _
                ",""Matched"",NA()),NA())"                                                              '   Formula to insert into 'Remarks' column
    End With

In the above code, when I added only *(F2=$F$2:$F$23200) in your code it was running but showing all mismatches and when I replaced the whole formula with this I got a syntax error.
With wsDestination.Range(DestinationRemarksColumn & "2")
.FormulaArray = "=IF(MATCH(1,((ABS(G2-$G$2:$G$23200)<=1))*(C2=$C$2:$C$23200)*(F2=$F$2:$F$23200)*(B2<>$B$2:$B$23200),0),"Matched","",NA()),NA())" ' Formula to insert into 'Remarks' column
End With

 

[RAJESH1960]

Should I remove this NA()),NA())"

 

[johnnyL]

Were going backwards again.

Read Post #20. I said we were getting somewhere. That was referring to your post #19. I have said the formula in post #19 is not right. I also said that the 'New' formula from post #1 is acceptable by excel. That was a clue to you to replace the formula code from post #19 that was very close, with the 'New' formula from Post #1.

So fix what you had in post #19 with the code from the 'New' formula from post #1 and post here what the code will look like, that is very similar to post #19, but without that 'NA' stuff at the end.

Do that correctly, and you are one step away from the proper vba code to try.

 

[RAJESH1960]

With wsDestination.Range(DestinationRemarksColumn & "2")
        .FormulaArray = "=IF(MATCH(1,((ABS(G2-$G$2:$G$23200)<=1))*(C2=$C$2:$C$23200)*(F2=$F$2:$F$23200)*(B2<>$B$2:$B$23200),0),"Matched",""                                                            '   Formula to insert into 'Remarks' column
    End With

 

[RAJESH1960]

I am getting a compile error