VBA Code to find max value from horizontal text from list having numbers

[jackt05]

Dear All,

I have the following names in a row like below


[TABLE="align: center"]
<tbody>[TR]
[TD="width: 63, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]Sam[/TD]
[TD="width: 63, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]John[/TD]
[TD="width: 63, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]Jack[/TD]
[TD="width: 77, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]George Paul[/TD]
[TD="width: 71, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]Mathew Joy[/TD]
[/TR]
</tbody>[/TABLE]



and I have a table on another sheet where I have the following data,

<!-- Please do not remove this header -->


[TABLE="align: center"]
<tbody>[TR]
[TD="width: 81, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]Sam[/TD]
[TD="width: 63, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]45[/TD]
[/TR]
[TR]
[TD="width: 81, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]John[/TD]
[TD="width: 63, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]60[/TD]
[/TR]
[TR]
[TD="width: 81, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]Jack[/TD]
[TD="width: 63, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]30[/TD]
[/TR]
[TR]
[TD="width: 81, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]George Paul[/TD]
[TD="width: 63, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]85[/TD]
[/TR]
[TR]
[TD="width: 81, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]Mathew Joy[/TD]
[TD="width: 63, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]32[/TD]
[/TR]
</tbody>[/TABLE]


As a result in the next column cell I need the name of the highest aged person and in next column cell his age.

as in this case

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]George Paul[/TD]
[TD]85[/TD]
[/TR]
</tbody>[/TABLE]




There is around 2000 rows everyday and Presently I am doing it manually.

If I get a VBA code it would save me a lot of work.

Thanks in Advance

Jack

 

[Peter_SSs]

I have shared the sheet to a colleague but he has office 2007. and aggregate function is not working in that office is there any other solution.

Sure. These are array formulas so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down.

Excel Workbook
	A	B	C	D	E	F	G	H	I
1								Result	Result
2	Name 6	Name 2	Name 5	Name 8	Name 7			Name 7	62
3	Name 9	Name 10	Name 15	Name 17	Name 1			Name 10	71
4	Name 4	Name 11	Name 16	Name 13	Name 3			Name 3	69
Sheet3

 

[jackt05]

Got It,

First did not properly enter.

But got the trick of confirming.

Thanks a lot

 

[Peter_SSs]

Dear Peter,

It is just returning the original list from top to bottom. not the names and "0" for Age.

I have used it as array.

what can be the error in entering.

I'm not sure. You can see that it is working for me. Here is my KM sheet with values entered in column B as Text, which is apparently what you had earlier.

Excel Workbook
	A	B
1	Name	Age
2	Name 1	32
3	Name 2	58
4	Name 3	69
5	Name 4	45
6	Name 5	20
7	Name 6	19
8	Name 7	62
9	Name 8	50
10	Name 9	40
11	Name 10	71
12	Name 11	33
13	Name 12	30
14	Name 13	28
15	Name 14	41
16	Name 15	18
17	Name 16	55
18	Name 17	17
19
KM

Has your data format changed?

In sheet KM, what does this formula, placed in an empty cell, return =ISNUMBER(B2)

 

[jackt05]

Sorry Peter,

I was not actually entering the formula properly,

Your formula is absolutely correct.

Thanks very much for your support.

Jack

 

[Peter_SSs]

Cheers. Glad you got it sorted.