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

Sam

John

Jack

George Paul

Mathew Joy

<tbody>
</tbody>

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

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

Sam	45
John	60
Jack	30
George Paul	85
Mathew Joy	32

<tbody>
</tbody>

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

George Paul

85

<tbody>
</tbody>

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]

What result(s) & layout would you want if John also had 85?

What do the names in a horizontal row have to do with it? Aren't you just asking who had the highest value in the table on the other sheet?

Which sheet do the results go on?

 

[jackt05]

Dear Peter,
Thanks for replying

First no same name will have same age as I have made the data that way.
I get the data where the persons in groups of 4 or 5 travel and they enter their names in a cell.
I use the text to column to get the names in separate columns
Then assign a leader of the group according to the highest aged person in the next column.
the result should be in the sheet where the rows are.

Like below

<!-- Please do not remove this header -->
<!-- Table easily created from Excel with ASAP Utilities (http://www.asap-utilities.com) -->
<table border="1" bordercolor="#C0C0C0" bordercolordark="#FFFFFF" cellspacing="0" align="center" frame="border">
<tr>
<td bgcolor="#FFFFFF" width="63" height="21" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
<td bgcolor="#FFFFFF" width="63" height="21" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
<td bgcolor="#FFFFFF" width="63" height="21" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
<td bgcolor="#FFFFFF" width="77" height="21" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
<td bgcolor="#FFFFFF" width="75" height="21" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td>
<td bgcolor="#FFFFFF" width="78" height="21" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Result</font></td>
<td bgcolor="#FFFFFF" width="63" height="21" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Result</font></td>
</tr>
<tr>
<td bgcolor="#FFFFFF" width="63" height="21" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Sam</font></td>
<td bgcolor="#FFFFFF" width="63" height="21" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">John</font></td>
<td bgcolor="#FFFFFF" width="63" height="21" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Jack</font></td>
<td bgcolor="#FFFFFF" width="77" height="21" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">George Paul</font></td>
<td bgcolor="#FFFFFF" width="75" height="21" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Mathew Joy</font></td>
<td bgcolor="#FFFFFF" width="78" height="21" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">George Paul</font></td>
<td bgcolor="#FFFFFF" width="63" height="21" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">85</font></td>
</tr>
</table>

Thanks

 

[Peter_SSs]

Thanks for the clarification. Next question is: Do you really need VBA? Could you just use formulas like this (with sheet names & ranges adjusted) copied down?

Excel Workbook
	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
List

Excel Workbook
	E	F
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
Table

 

[jackt05]

Dear Peter,

Something is not working it is giving error #NUM !

My table is in Sheet Named KM range A2 to B804

and rows in Sheet3 B2:G2 result needed in G2 & H2

Please suggest changes in formula.

What is 15,6 in the formula and
14,6 in other formula

Jack

 

[jackt05]

Update,

and rows in Sheet3 B2:G2 result needed in G2 & H2

Result in H2 and I2

 

[Peter_SSs]

Something is not working it is giving error #NUM !

Sounds like the numbers in KM column B are Text not actual Numbers. Hopefully new formulas below will cope with that.

and rows in Sheet3 B2:G2 result needed in G2 & H2

Bit hard to have the data and the results in the same cell so you may have to adjust the formulas a little.
Edit: Ah, hadn't seen your last post but hopefully still you can adjust if needed.

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

What is 15,6 in the formula and
14,6 in other formula

Have a look at the AGGREGATE Help

 

[jackt05]

Thanks Peter,

It is now working perfectly as desired.
Thanks your support

and thank you for this forum it really is a boon for people for me with less knowledge in excel.

Marked as Completed.

 

[Peter_SSs]

You're very welcome. Thanks for the follow-up.

 

[jackt05]

Dear Peter,

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.

Jack