max date basis required next column.

[Atul Kadam]

dear All,

We have a huge data in excel file , we need to find max date in different columns and on the basis of max date we also required next column code.

Please suggest a formula or vba code.

Date	Code	Date	Code	Date	Code	Max Date	Code on the basis of max date
01-Nov-16	461	05-Nov-16	1124	30-Nov-16	1008	30-Nov-16	1008
30-Nov-16	1135	07-Dec-16	1091	24-Dec-16	1089	24-Dec-16	1089
06-Dec-16	1124	06-Dec-16	1126	10-Oct-16	979	06-Dec-16	Error
15-Nov-16	1049	01-Nov-16	1048	14-Dec-16	1060	14-Dec-16	1060
17-Oct-16	1061	15-Sep-16	1062	15-Sep-16	1085	17-Oct-16	1061
01-Sep-16	1173	08-May-16	1172	04-Jul-16	1170	01-Sep-16	1173
09-Aug-16	1171	08-Mar-16	1203	01-Nov-16	1207	01-Nov-16	1207

<tbody>
</tbody>

 

[AliGW]

Why does line three resolve to an error and not 1126?

 

[AliGW]

Try this:

Excel 2016 (Windows) 32 bit
	A	B	C	D	E	F	G	H
1	Date	Code	Date	Code	Date	Code	Max Date	Code on the basis of max date
2	01-Nov-16	461	05-Nov-16	1124	30-Nov-16	1008	30-Nov-16	1008
3	30-Nov-16	1135	07-Dec-16	1091	24-Dec-16	1089	24-Dec-16	1089
4	06-Dec-16	1124	06-Dec-16	1126	10-Oct-16	979	06-Dec-16	1126
5	15-Nov-16	1049	01-Nov-16	1048	14-Dec-16	1060	14-Dec-16	1060
6	17-Oct-16	1061	15-Sep-16	1062	15-Sep-16	1085	17-Oct-16	1061
7	01-Sep-16	1173	08-May-16	1172	04-Jul-16	1170	01-Sep-16	1173
8	09-Aug-16	1171	08-Mar-16	1203	01-Nov-16	1207	01-Nov-16	1207
Sheet1

Cell Formulas
Range	Formula
G2	=MAX($A2:$F2)
H2	=LOOKUP(2,1/($A2:$F2=G2),$B2:$G2)

 

[Atul Kadam]

if date is same in two or three different cloumn then we have identify these cases and check with mannuly what is exact error

 

[AliGW]

In that case, try this:

Excel 2016 (Windows) 32 bit
	A	B	C	D	E	F	G	H
1	Date	Code	Date	Code	Date	Code	Max Date	Code on the basis of max date
2	01-Nov-16	461	05-Nov-16	1124	30-Nov-16	1008	30-Nov-16	1008
3	30-Nov-16	1135	07-Dec-16	1091	24-Dec-16	1089	24-Dec-16	1089
4	06-Dec-16	1124	06-Dec-16	1126	10-Oct-16	979	06-Dec-16	Error
5	15-Nov-16	1049	01-Nov-16	1048	14-Dec-16	1060	14-Dec-16	1060
6	17-Oct-16	1061	15-Sep-16	1062	15-Sep-16	1085	17-Oct-16	1061
7	01-Sep-16	1173	08-May-16	1172	04-Jul-16	1170	01-Sep-16	1173
8	09-Aug-16	1171	08-Mar-16	1203	01-Nov-16	1207	01-Nov-16	1207
Sheet1

Cell Formulas
Range	Formula
G2	=MAX($A2:$F2)
H2	=IF(COUNTIF($A2:$F2,G2)>1,"Error",LOOKUP(2,1/($A2:$F2=G2),$B2:$G2))

 

[Atul Kadam]

Great

Thank you very much

it’s working

 

[AliGW]

You are most welcome.