Hoping someone can help....as couldn't find anything relevant in the forum.

In sheet 1, I have coulmns of data that includes below. In sheet 2 i need
to return the highest value that is in column B if column A is 'Kerry
Beniston1'. In this instance it would be 22Kerry Beniston.
If it could just return the value of 22, that would be even better, but if
necessary, can live with the whole text.

Please can anyone help. Many thanks


Column A Column B
KERRY BENISTON0 0KERRY BENISTON
KERRY BENISTON0 0KERRY BENISTON
KERRY BENISTON0 0KERRY BENISTON
KERRY BENISTON1 16KERRY BENISTON
KERRY BENISTON1 17KERRY BENISTON
KERRY BENISTON1 22KERRY BENISTON
KERRY BENISTONFALSE 1KERRY BENISTON

Hi Holyman,

I have a sugestion for you, not sure if it's the best way but...

use the this function to extract just the digits of the text string,

************************************************
Public Function DigitsOnly(sStr As String) As Variant
Dim oRegExp As Object

Set oRegExp = CreateObject("VBScript.RegExp")

With oRegExp
.IgnoreCase = True
.Global = True
oRegExp.Pattern = "\D"

DigitsOnly = oRegExp.Replace(sStr, vbNullString)
End With
End Function
***********************************************
create a column between A and B with the function =abs(digitsonly(c2)) copy
it down

on the sheet2 use a vlookup as
=vlookup(max(sheet1!b2:b100),sheet1!b3:c100,2,0)

hth
regards from Brazil
Marcelo





"holyman" escreveu:

Hoping someone can help....as couldn't find anything relevant in the forum.

In sheet 1, I have coulmns of data that includes below. In sheet 2 i need
to return the highest value that is in column B if column A is 'Kerry
Beniston1'. In this instance it would be 22Kerry Beniston.
If it could just return the value of 22, that would be even better, but if
necessary, can live with the whole text.

Please can anyone help. Many thanks


Column A Column B
KERRY BENISTON0 0KERRY BENISTON
KERRY BENISTON0 0KERRY BENISTON
KERRY BENISTON0 0KERRY BENISTON
KERRY BENISTON1 16KERRY BENISTON
KERRY BENISTON1 17KERRY BENISTON
KERRY BENISTON1 22KERRY BENISTON
KERRY BENISTONFALSE 1KERRY BENISTON

It needs to be a formula, and the formulas below does not work for the data.
Has anyone got any other suggestions?

"Marcelo" wrote:

Hi Holyman,

I have a sugestion for you, not sure if it's the best way but...

use the this function to extract just the digits of the text string,

************************************************
Public Function DigitsOnly(sStr As String) As Variant
Dim oRegExp As Object

Set oRegExp = CreateObject("VBScript.RegExp")

With oRegExp
.IgnoreCase = True
.Global = True
oRegExp.Pattern = "\D"

DigitsOnly = oRegExp.Replace(sStr, vbNullString)
End With
End Function
***********************************************
create a column between A and B with the function =abs(digitsonly(c2)) copy
it down

on the sheet2 use a vlookup as
=vlookup(max(sheet1!b2:b100),sheet1!b3:c100,2,0)

hth
regards from Brazil
Marcelo





"holyman" escreveu:

Hoping someone can help....as couldn't find anything relevant in the forum.

In sheet 1, I have coulmns of data that includes below. In sheet 2 i need
to return the highest value that is in column B if column A is 'Kerry
Beniston1'. In this instance it would be 22Kerry Beniston.
If it could just return the value of 22, that would be even better, but if
necessary, can live with the whole text.

Please can anyone help. Many thanks


Column A Column B
KERRY BENISTON0 0KERRY BENISTON
KERRY BENISTON0 0KERRY BENISTON
KERRY BENISTON0 0KERRY BENISTON
KERRY BENISTON1 16KERRY BENISTON
KERRY BENISTON1 17KERRY BENISTON
KERRY BENISTON1 22KERRY BENISTON
KERRY BENISTONFALSE 1KERRY BENISTON

A pivot table would allow you to filter for all kerrybeniston1's. The
fields in your second column would be in the pivot table rows, eg
11kerrybeniston, 17kerrybeniston etc etc.

use the large worksheet function to find the biggest field in the list,
it might be obvious anyway if you sort the data "descending" in the
pivot table.


--
robert111
------------------------------------------------------------------------
robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996
View this thread: http://www.excelforum.com/showthread...hreadid=562628

A pivot table would allow you to filter for all kerrybeniston1's. The
fields in your second column would be in the pivot table rows, eg
11kerrybeniston, 17kerrybeniston etc etc.

use the large worksheet function to find the biggest field in the list,
it might be obvious anyway if you sort the data "descending" in the
pivot table.


--
robert111
------------------------------------------------------------------------
robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996
View this thread: http://www.excelforum.com/showthread...hreadid=562628

Robert,

thanks for that..........I would do that, but I'm needing a formula result
as the result is then dependant on other formaulas that I have within the
spreadsheet. A pivot table can't help in this instance.

Is there a formula out there?

"robert111" wrote:


A pivot table would allow you to filter for all kerrybeniston1's. The
fields in your second column would be in the pivot table rows, eg
11kerrybeniston, 17kerrybeniston etc etc.

use the large worksheet function to find the biggest field in the list,
it might be obvious anyway if you sort the data "descending" in the
pivot table.


--
robert111
------------------------------------------------------------------------
robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996
View this thread: http://www.excelforum.com/showthread...hreadid=562628

a bit convoluted but this works, see attachment. you will need to merge
all the intermediate fomulas if you cannot have dummy columns


+-------------------------------------------------------------------+
|Filename: answer1.zip |
|Download: http://www.excelforum.com/attachment.php?postid=5063 |
+-------------------------------------------------------------------+

--
robert111
------------------------------------------------------------------------
robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996
View this thread: http://www.excelforum.com/showthread...hreadid=562628