Please help

[holyman]

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

[Marcelo]

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
>

[holyman]

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
> >

[robert111]

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]

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.

[holyman]

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
>
>

[robert111]

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