Adaptation of formula without helping columns

[motilulla]

Hello,</SPAN></SPAN>

The formula I am using for that in need to have helper columns gives result in column "K" as shown below</SPAN></SPAN>

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1
2
3				n1	n2	Join		n1	n2	Join	Last Row Number
4				3	1	3 | 1		1	1	1 | 1	23
5				8	5	8 | 5		1	2	1 | 2	29
6				3	1	3 | 1		1	3	1 | 3	15
7				5	2	5 | 2		1	4	1 | 4	27
8				2	2	2 | 2		1	5	1 | 5
9				6	8	6 | 8		1	6	1 | 6
10				3	4	3 | 4		1	7	1 | 7
11				2	6	2 | 6		1	8	1 | 8
12				1	4	1 | 4		1	9	1 | 9
13				2	3	2 | 3		2	1	2 | 1	30
14				3	6	3 | 6		2	2	2 | 2	8
15				1	3	1 | 3		2	3	2 | 3	13
16				1	1	1 | 1		2	4	2 | 4	32
17				3	2	3 | 2		2	5	2 | 5
18				1	1	1 | 1		2	6	2 | 6	34
19				1	4	1 | 4		2	7	2 | 7
20				5	3	5 | 3		2	8	2 | 8
21				1	1	1 | 1		2	9	2 | 9
22				3	4	3 | 4		3	1	3 | 1	35
23				1	1	1 | 1		3	2	3 | 2	17
24				3	1	3 | 1		3	3	3 | 3
25				4	8	4 | 8		3	4	3 | 4	22
26				1	4	1 | 4		3	5	3 | 5
27				1	4	1 | 4		3	6	3 | 6	14
28				1	2	1 | 2		3	7	3 | 7
29				1	2	1 | 2		3	8	3 | 8
30				2	1	2 | 1		3	9	3 | 9
31				9	1	9 | 1		4	1	4 | 1
32				2	4	2 | 4		4	2	4 | 2
33				5	1	5 | 1		4	3	4 | 3
34				2	6	2 | 6		4	4	4 | 4
35				3	1	3 | 1		4	5	4 | 5
36				6	1	6 | 1		4	6	4 | 6
37
38
39
Sheet7

Cell Formulas
Range	Formula
F4	=D4&" | "&E4
J4	=H4&" | "&I4
K4	{=IF(MAX((J4=$F$4:$F$4956)*(ROW($F$4:$F$4956)))=0,"",MAX((J4=$F$4:$F$4956)*(ROW($F$4:$F$4956))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

I am looking does is it possible to have a new formula, which can work with out helper columns and give result in column "I" as shown below or VBA any solution</SPAN></SPAN>

Book1
	A	B	C	D	E	F	G	H	I	J
1
2
3				n1	n2		n1	n2	Last Row Number
4				3	1		1	1	23
5				8	5		1	2	29
6				3	1		1	3	15
7				5	2		1	4	27
8				2	2		1	5
9				6	8		1	6
10				3	4		1	7
11				2	6		1	8
12				1	4		1	9
13				2	3		2	1	30
14				3	6		2	2	8
15				1	3		2	3	13
16				1	1		2	4	32
17				3	2		2	5
18				1	1		2	6	34
19				1	4		2	7
20				5	3		2	8
21				1	1		2	9
22				3	4		3	1	35
23				1	1		3	2	17
24				3	1		3	3
25				4	8		3	4	22
26				1	4		3	5
27				1	4		3	6	14
28				1	2		3	7
29				1	2		3	8
30				2	1		3	9
31				9	1		4	1
32				2	4		4	2
33				5	1		4	3
34				2	6		4	4
35				3	1		4	5
36				6	1		4	6
37
38
Sheet8

Thank you all</SPAN></SPAN>

Excel 2000</SPAN></SPAN>
Regards,</SPAN>
Moti</SPAN></SPAN>

 

[KolGuyXcel]

Array formula (Ctrl+Shift+Enter)

=IF(MAX((G4=$D$4:$D$4956)*(H4=$E$4:$E$4956)*(ROW(F$4:$F$4956)))=0,"",MAX((G4=$D$4:$D$4956)*(H4=$E$4:$E$4956)*(ROW(F$4:$F$4956))))

 

[MickG]

Another option:-
Based on the second Table.

[COLOR="Navy"]Sub[/COLOR] MG18Nov37
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Txt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("D4"), Range("D" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    Txt = Dn.Value & "-" & Dn.Offset(, 1).Value
    [COLOR="Navy"]If[/COLOR] Not .exists(Txt) [COLOR="Navy"]Then[/COLOR]
        .Add Txt, Dn.Row
    [COLOR="Navy"]Else[/COLOR]
        .Item(Txt) = Dn.Row
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng.Offset(, 3)
    Txt = Dn.Value & "-" & Dn.Offset(, 1).Value
        [COLOR="Navy"]If[/COLOR] .exists(Txt) [COLOR="Navy"]Then[/COLOR] Dn.Offset(, 2) = .Item(Txt)
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[motilulla]

Array formula (Ctrl+Shift+Enter)

=IF(MAX((G4=$D$4:$D$4956)*(H4=$E$4:$E$4956)*(ROW(F$4:$F$4956)))=0,"",MAX((G4=$D$4:$D$4956)*(H4=$E$4:$E$4956)*(ROW(F$4:$F$4956))))

Thank you KolGuyXcel, for building a formula to work without helper columns, it is resulting fine!!</SPAN></SPAN>

Have a great weekend and good luck.</SPAN></SPAN>

Kind regards,</SPAN>
Moti</SPAN></SPAN>

 

[motilulla]

Another option:-
Based on the second Table.

[COLOR=navy]Sub[/COLOR] MG18Nov37
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range, n [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] Txt [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]
[COLOR=navy]Set[/COLOR] Rng = Range(Range("D4"), Range("D" & Rows.Count).End(xlUp))
[COLOR=navy]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
    Txt = Dn.Value & "-" & Dn.Offset(, 1).Value
    [COLOR=navy]If[/COLOR] Not .exists(Txt) [COLOR=navy]Then[/COLOR]
        .Add Txt, Dn.Row
    [COLOR=navy]Else[/COLOR]
        .Item(Txt) = Dn.Row
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR]
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng.Offset(, 3)
    Txt = Dn.Value & "-" & Dn.Offset(, 1).Value
        [COLOR=navy]If[/COLOR] .exists(Txt) [COLOR=navy]Then[/COLOR] Dn.Offset(, 2) = .Item(Txt)
[COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]End[/COLOR] [COLOR=navy]With[/COLOR]
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

Thank you Mick, I liked the VBA option very much, it is more practical while using because it does not bother to adjust the ranges and live the values only. Worked like a charm </SPAN></SPAN>

Have a great weekend and good luck.</SPAN></SPAN>

Kind regards,</SPAN></SPAN>
Moti</SPAN></SPAN>

 

[MickG]

You're welcome