Compare strings without numbers and characters

[probexcel]

I have a column in excel that has some data like that:

• Alexa 200 Fernandes
• Alexa 210 Fernandes
• Alexa 210 Fernandes Melo
• Google Helpful 235
• Google Helpful 231
• Hello World 234 (5x9)
• Hello World 235 (5x9

What I need to is to check if the text is the same, and if so, to check the highest number. The two first columns are the ones I have in my original Spreadsheet, and the very last one is my desired output. The third one is not really necessary for me, it's only for demonstration purpose. For example:

Original Text	Numbers	Text that needs to be compared	Output
Alexa 200 Fernandes	200	Alexa Fernandes	Sell
Alexa 210 Fernandes	210	Alexa Fernandes	Hold
Alexa 210 Fernandes Melo	210	Alexa Fernandes Melo
Google Helpful 235	235	Google Helpful	Hold
Google Helpful 231	231	Google Helpful	Sell
Hello World 234 (5x9)	234	Hello World x	Sell
Hello World 235 (5x9	235	Hello World x	Hold

Basically, what I need in the last column is, IF the extracted letters (no numbers or characters) from row 2 column 1 is equals to the extracted text from any other cell in the column 1, then sell the smaller number in column 2 and hold the higher number in column 2. IF that doesn't happen then just leave it blank.

IF there's 3 different rows that match or more, I only want to sell the smaller number and hold all of the others.

I've spent 2 days trying to figure out a way to do that but still couldn't, so any help and advice would be welcome.

 

[lrobbo314]

How about this using Power Query.

PQ
	A	B	C	D	E	F	G
1	Original Text	Numbers		Original Text	Numbers	RN	Output
2	Alexa 200 Fernandes	200		Alexa 200 Fernandes	200	Alexa Fernandes	Sell
3	Alexa 210 Fernandes	210		Alexa 210 Fernandes	210	Alexa Fernandes	Hold
4	Alexa 210 Fernandes Melo	210		Alexa 210 Fernandes Melo	210	Alexa Fernandes Melo
5	Google Helpful 235	235		Google Helpful 235	235	Google Helpful	Hold
6	Google Helpful 231	231		Google Helpful 231	231	Google Helpful	Sell
7	Hello World 234 (5x9)	234		Hello World 234 (5x9)	234	Hello World x	Sell
8	Hello World 235 (5x9)	235		Hello World 235 (5x9)	235	Hello World x	Hold
Data

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    RemoveNum = Table.AddColumn(Source, "RN", each Text.Replace(Text.Select([Original Text],{"A".."Z","a".."z"," "}),"  ", " ")),
    Group = Table.Group(RemoveNum, {"RN"}, {{"Data", each _, type table [Original Text=text, Numbers=number, RN=text]}}),
    TC = Table.TransformColumns(Group,{{"Data", (tbl)=> Table.AddColumn(tbl,"Output", each 
        if Table.RowCount(tbl)=1 then null else
        if [Numbers] = List.Min(tbl[Numbers]) then "Sell" else "Hold"
    )}}),
    Expand = Table.ExpandTableColumn(TC, "Data", {"Original Text", "Numbers", "Output"}, {"Original Text", "Numbers", "Output"}),
    Reorder = Table.ReorderColumns(Expand,{"Original Text", "Numbers", "RN", "Output"})
in
    Reorder

 

[lrobbo314]

Here is another way using excel functions. It uses a defined lambda function 'RemoveCharList', and the formula for that is.

=LAMBDA(text,chars,IF(chars="",text,REMOVECHARLIST(SUBSTITUTE(text,LEFT(chars),""),RIGHT(chars,LEN(chars)-1))))

FX
	A	B	C	D
14	Original Text	Numbers	No Numbers	Output
15	Alexa 200 Fernandes	200	Alexa Fernandes	Sell
16	Alexa 210 Fernandes	210	Alexa Fernandes	Hold
17	Alexa 210 Fernandes Melo	210	Alexa Fernandes Melo
18	Google Helpful 235	235	Google Helpful	Hold
19	Google Helpful 231	231	Google Helpful	Sell
20	Hello World 234 (5x9)	234	Hello World (x)	Sell
21	Hello World 235 (5x9)	235	Hello World (x)	Hold
Data

Cell Formulas
Range	Formula
C15:C21	C15	=TRIM(REMOVECHARLIST(A15,"0123456789"))
D15:D21	D15	=LET(f,FILTER($B$15:$B$21,$C$15:$C$21=C15),IF(COUNTA(f)=1,"",IF(B15=MIN(f),"Sell","Hold")))

 

[JamesCanale]

Couldn't get at xl2bb for this. put the formulas down below.

Alexa 200 Fernandes	200​	Alexa Fernandes	Sell
Alexa 210 Fernandes	210​	Alexa Fernandes	Hold
Alexa 210 Fernandes Melo	210​	Alexa Fernandes Melo
Google Helpful 235	235​	Google Helpful	Hold
Google Helpful 231	231​	Google Helpful	Sell
Hello World 234 (5x9)	234​	Hello World x	Sell
Hello World 235 (5x9	235​	Hello World x	Hold
	=LET(a," "&A2&" ",b,TEXTSPLIT(a," "),c,SUM(IF(ISNUMBER(VALUE(b)),VALUE(b),"")),c)	=LET(a,CODE(MID(A2,SEQUENCE(LEN(A2)),1)),b,FILTER(a,(a>64)*(a<91)+(a>96)*(a<123)+(a=32)),TEXTJOIN("",TRUE,CHAR(b)))	=IF(ROWS(FILTER($B$2:$B$8,$C$2:$C$8=C2))>1,IF(MIN(FILTER($B$2:$B$8,$C$2:$C$8=C2))=B2,"Sell","Hold"),"")