Urgent: want to put paste lastest date values in all from one column logic

[vikas9385]

Condition: If Credit Note value & Party Name are same then latest date of invoice & it's no should come in desired invoice no & date coloumn


[TABLE="width: 732"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]NAME[/TD]
[TD]Inv No[/TD]
[TD]Inv Date[/TD]
[TD]CEDIT NOTE[/TD]
[TD]Desired Inv No[/TD]
[TD]Desired Date[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]217975312[/TD]
[TD]03-Jan-17[/TD]
[TD]833743881[/TD]
[TD]217975313[/TD]
[TD]03-02-2017[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]217975313[/TD]
[TD]03-Feb-17[/TD]
[TD]833743881[/TD]
[TD]217975313[/TD]
[TD]03-02-2017[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]217975311[/TD]
[TD]03-Mar-17[/TD]
[TD]833743882[/TD]
[TD]217975311[/TD]
[TD]05-03-2017[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]217975311[/TD]
[TD]03-Mar-17[/TD]
[TD]833743882[/TD]
[TD]217975311[/TD]
[TD]05-03-2017[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]217975311[/TD]
[TD]05-Mar-17[/TD]
[TD]833743882[/TD]
[TD]217975311[/TD]
[TD]05-03-2017[/TD]
[/TR]
</tbody>[/TABLE]


Thanks in advance

 

[VBA Geek]

Excel 2010
	A	B	C	D	E	F
1	NAME	Inv No	Inv Date	CEDIT NOTE	Desired Inv No	Desired Date
2	A	217975312	3-Jan-17	833743881	3-Feb-17	217975313
3	A	217975313	3-Feb-17	833743881	3-Feb-17	217975313
4	B	217975311	3-Mar-17	833743882	5-Mar-17	217975311
5	B	217975311	3-Mar-17	833743882	5-Mar-17	217975311
6	B	217975311	5-Mar-17	833743882	5-Mar-17	217975311
Sheet1

Cell Formulas
Range	Formula
E2	{=MAX(IF(D2=$D$2:$D$6,IF($A$2:$A$6=A2,$C$2:$C$6)))}
E3	{=MAX(IF(D3=$D$2:$D$6,IF($A$2:$A$6=A3,$C$2:$C$6)))}
E4	{=MAX(IF(D4=$D$2:$D$6,IF($A$2:$A$6=A4,$C$2:$C$6)))}
E5	{=MAX(IF(D5=$D$2:$D$6,IF($A$2:$A$6=A5,$C$2:$C$6)))}
E6	{=MAX(IF(D6=$D$2:$D$6,IF($A$2:$A$6=A6,$C$2:$C$6)))}
F2	{=INDEX($B$2:$B$6,MAX(IF(D2=$D$2:$D$6,IF($A$2:$A$6=A2,IF(E2=$C$2:$C$6,ROW($A$2:$A$6)-ROW($A$2)+1)))))}
F3	{=INDEX($B$2:$B$6,MAX(IF(D3=$D$2:$D$6,IF($A$2:$A$6=A3,IF(E3=$C$2:$C$6,ROW($A$2:$A$6)-ROW($A$2)+1)))))}
F4	{=INDEX($B$2:$B$6,MAX(IF(D4=$D$2:$D$6,IF($A$2:$A$6=A4,IF(E4=$C$2:$C$6,ROW($A$2:$A$6)-ROW($A$2)+1)))))}
F5	{=INDEX($B$2:$B$6,MAX(IF(D5=$D$2:$D$6,IF($A$2:$A$6=A5,IF(E5=$C$2:$C$6,ROW($A$2:$A$6)-ROW($A$2)+1)))))}
F6	{=INDEX($B$2:$B$6,MAX(IF(D6=$D$2:$D$6,IF($A$2:$A$6=A6,IF(E6=$C$2:$C$6,ROW($A$2:$A$6)-ROW($A$2)+1)))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[vikas9385]

Thanks a lot sir

I've already achieved the desired result by following two functions but when I'm applying this in my vba code then it is not giving me the desired result. Actually, it is giving incorrect results in array function.

{=LARGE((I2=$I$2:$I$3874)*(C2=$C$2:$C$3874)*($F$2:$F$3874),1)}

=VLOOKUP(G2&I2,D:E,2,0)

Excel 2010

	A	B	C	D	E	F
A
A
B
B
B

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]NAME[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]Inv No[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]Inv Date[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]CEDIT NOTE[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]Desired Inv No[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]Desired Date[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]217975312[/TD]
[TD="align: right"]3-Jan-17[/TD]
[TD="align: right"]833743881[/TD]
[TD="align: right"]3-Feb-17[/TD]
[TD="align: right"]217975313[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]217975313[/TD]
[TD="align: right"]3-Feb-17[/TD]
[TD="align: right"]833743881[/TD]
[TD="align: right"]3-Feb-17[/TD]
[TD="align: right"]217975313[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]217975311[/TD]
[TD="align: right"]3-Mar-17[/TD]
[TD="align: right"]833743882[/TD]
[TD="align: right"]5-Mar-17[/TD]
[TD="align: right"]217975311[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]217975311[/TD]
[TD="align: right"]3-Mar-17[/TD]
[TD="align: right"]833743882[/TD]
[TD="align: right"]5-Mar-17[/TD]
[TD="align: right"]217975311[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]217975311[/TD]
[TD="align: right"]5-Mar-17[/TD]
[TD="align: right"]833743882[/TD]
[TD="align: right"]5-Mar-17[/TD]
[TD="align: right"]217975311[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]{=MAX(IF(D2=$D$2:$D$6,IF($A$2:$A$6=A2,$C$2:$C$6)))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]{=INDEX($B$2:$B$6,MAX(IF(D2=$D$2:$D$6,IF($A$2:$A$6=A2,IF(E2=$C$2:$C$6,ROW($A$2:$A$6)-ROW($A$2)+1)))))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E3[/TH]
[TD="align: left"]{=MAX(IF(D3=$D$2:$D$6,IF($A$2:$A$6=A3,$C$2:$C$6)))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F3[/TH]
[TD="align: left"]{=INDEX($B$2:$B$6,MAX(IF(D3=$D$2:$D$6,IF($A$2:$A$6=A3,IF(E3=$C$2:$C$6,ROW($A$2:$A$6)-ROW($A$2)+1)))))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E4[/TH]
[TD="align: left"]{=MAX(IF(D4=$D$2:$D$6,IF($A$2:$A$6=A4,$C$2:$C$6)))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F4[/TH]
[TD="align: left"]{=INDEX($B$2:$B$6,MAX(IF(D4=$D$2:$D$6,IF($A$2:$A$6=A4,IF(E4=$C$2:$C$6,ROW($A$2:$A$6)-ROW($A$2)+1)))))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E5[/TH]
[TD="align: left"]{=MAX(IF(D5=$D$2:$D$6,IF($A$2:$A$6=A5,$C$2:$C$6)))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F5[/TH]
[TD="align: left"]{=INDEX($B$2:$B$6,MAX(IF(D5=$D$2:$D$6,IF($A$2:$A$6=A5,IF(E5=$C$2:$C$6,ROW($A$2:$A$6)-ROW($A$2)+1)))))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E6[/TH]
[TD="align: left"]{=MAX(IF(D6=$D$2:$D$6,IF($A$2:$A$6=A6,$C$2:$C$6)))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F6[/TH]
[TD="align: left"]{=INDEX($B$2:$B$6,MAX(IF(D6=$D$2:$D$6,IF($A$2:$A$6=A6,IF(E6=$C$2:$C$6,ROW($A$2:$A$6)-ROW($A$2)+1)))))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]