Convert multiple letters to ASCII and back to letters

[skittlz]

Hi Everyone,

I have a spreadsheet that manages revisions of different documents. I'm currently using this formula to convert letters to ASCII value:

=IFERROR(SUMPRODUCT(CODE(UPPER(MID(G2,ROW(INDIRECT("1:"&LEN(G2))),1)))),"")

I only want Capital letters, and this formula works. For A (or a), this formula gives me 65. For Z or z, i get 90. for AA, I get 130.

Problem: How do i convert 130 back to AA? My current formula is:

=IFERROR(CHAR(MAXIFS(Formulas!I:I,Formulas!A:A,'Training Matrix'!A7)),"")

For AA, I get a comma, for AB I get the "f" symbol, for AC i get 2 comas. Ideally, I need both formulas to work till ZZ.

Please help!

 

[Cubist]

Problem: How do i convert 130 back to AA? My current formula is:

I don't believe it's possible as the sum is not unique. For example, BB -> 66+66 =132, but it could've been A+C (65+67).

Also, you can avoid the volatile INDIRECT with newer Excel version.

=IFERROR(SUMPRODUCT(CODE(UPPER(MID(G2,SEQUENCE(LEN(G2)),1)))),"")

 

[skittlz]

I don't believe it's possible as the sum is not unique. For example, BB -> 66+66 =132, but it could've been A+C (65+67).

Also, you can avoid the volatile INDIRECT with newer Excel version.

=IFERROR(SUMPRODUCT(CODE(UPPER(MID(G2,SEQUENCE(LEN(G2)),1)))),"")

Crap - I forgot the sum is not unique. How about using that formula to find the "Highest" Ascii value, and then going to the text field to copy the rev? what I mean to say is :

Find the highest rev per =IFERROR(CHAR(MAXIFS(Formulas!I:I,Formulas!A:A,'Training Matrix'!A7)),""), then go to "Formulas Tab", and copy and paste the corresponding value in Column G?

 

[Cubist]

You'll run into another issue. For example, AZ has the same sum as ZA, which one to return?

 

[skittlz]

You'll run into another issue. For example, AZ has the same sum as ZA, which one to return?

great point - i didn't think about it. ZA vs AZ is roughly 650 revisions. I'd have the same issues much earlier - BA vs. AB - however that's easier to manage. I will eventually have to delete old data - my spreadsheet wouldn't be able to manage 26 revisions worth of entries anyway.

 

[Cubist]

Can you explain more on what you're trying to achieve with the revision? Perhaps there's a better approach.

 

[skittlz]

Hi Cubist - thank you for your patience. I needed a formula to find the "Highest" revision. For example, my spreadsheet has data like this in 4 columns:


Bob, Document10, A, Ascii value(A)
Bob, Document10, B, Ascii value(B)
Bob, Document10, C, Ascii value(C)
Bob, Document 10, D, Ascii value(D)
Bill, Document11, E, Ascii value(E)
Bill, Document11, F, Ascii value(F)
Joe, Document12, G, Ascii value(G)
Joe, Document12, H, Ascii value(H)

I needed an output to show that: Bob is Trained to Document10, Rev D, and Bill is trained to Document11, Rev F, Joe is trained to Document12, Rev H.

The best way I could figure out how to do it would be using maxifs of Ascii values.

Not sure if it makes sense.

 

[Cubist]

You can try this. It returns the latest record for each person.

Book1
	A	B	C	D	E	F	G
1	Name	Document	Rev		Name	Document	Rev
2	Bob	Document10	A		Bob	Document 10	D
3	Bob	Document10	B		Bill	Document11	F
4	Bob	Document10	C		Joe	Document12	H
5	Bob	Document 10	D
6	Bill	Document11	E
7	Bill	Document11	F
8	Joe	Document12	G
9	Joe	Document12	H
Sheet1

Cell Formulas
Range	Formula
E2:G4	E2	=CHOOSEROWS(A2:C9,XMATCH(UNIQUE(A2:A9),A2:A9,,-1))
Dynamic array formulas.

 

[felixstraube]

Just to be sure...
Is a revision AA greater than Z?
Because if you let excel order it alpabetically it will give you something different:

Name	Document	Rev
Bill	Document11	E
Bill	Document11	F
Bob	Document10	AA
Bob	Document10	C
Bob	Document10	Z
Bob	Document10	ZA
Joe	Document12	G
Joe	Document12	H

Maybe you can use something like this:

Book1
	A	B	C	D
1	Name	Document	Rev	Rev numerical
2	Bill	Document11	E	69
3	Bill	Document11	F	70
4	Bob	Document10	AA	6565
5	Bob	Document10	C	67
6	Bob	Document10	Z	90
7	Bob	Document10	ZA	9065
8	Joe	Document12	G	71
9	Joe	Document12	H	72
Sheet1

Cell Formulas
Range	Formula
D2:D9	D2	=LET(d, Sheet1!$C2, l, LEN(d), exp, SEQUENCE(,l,l-1,-1), s, SEQUENCE(,l), SUM(CODE(MID(d, s,1))*100^exp) )

To convert the text rev to numbers and then select the greatest?

 

[rlv01]

If desired, you can probably convert @felixstraube's Col D numerical value back to letter revisions with this formula

IF(LEN(D2)=4,CHAR(LEFT($D2,2)) & CHAR(RIGHT($D2,2)),CHAR($D2))