Get value based total column in range minus Blank columns

[maabadi]

Hi.
I have data in range from Columns G to R. I have value "a" at column J and then 2 Empty Columns. After them I have data at column M and formula give me value "d" but I want to be "b". Also for Column Q, I have "h" but I want "c". If I have Data, this is my formula at Column M( change Column part [ COLUMNS($J:M) ] based column from Column K to R) :

MID("abcdefghijklmnopqrstuvwxyz",COLUMNS($J:M),1),""))

I want to change Column part to take exact value (respectively a,b,c,d,e,...).
thanks

 

[C Moore]

Can you share how it determines if a letter should be there?

 

[Peter_SSs]

I would like to see the full formula that you have just shown part of. (That may also help answer C Moore's question)

It would also help if you did two further things:

1. Update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
   
   
2. Provided your sample data with XL2BB to make it easier for helpers by not having to manually type out sample data to test with or risk typing data incorrectly.

 

[maabadi]

This is my Total formula at Excel Ranges. Formula for column J different from others (K to U).
Thanks.

Book1.xlsx
	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1				56.5	56	55.75	51	51	50.25	50	46.25	43.75	43.5	42.5	42.25
2	Rank	Genotypes	Weight
3	1	BS15	56.5	a
4	2	BS18	56	a
5	3	BS10	55.75	a
6	4	BS16	51				d
7	5	BS20	51				d
8	6	BS9	50.25				d
9	7	BS17	50				d
10	8	BS14	46.25								h
11	9	BS4	43.75									i
12	10	BS7	43.5									i
13	11	BS11	42.5									i
14	12	BS19	42.25									i
Sheet1

Cell Formulas
Range	Formula
J1:U1	J1	=TRANSPOSE(I3:I14)
J3:J14	J3	=IF(AND(OR(J$1-$I3>0,J$1-$I3=0),J$1-$I3<1.6),MID("abcdefghijklmnopqrstuvwxyz",COLUMNS($J:J),1),"")
K3:U14	K3	=IF(AND(K$1-$I3<1.6,J$1-$I3<1.6),"",IF(AND(OR(K$1-$I3>0,K$1-$I3=0),K$1-$I3<1.6),MID("abcdefghijklmnopqrstuvwxyz",COLUMNS($J:K),1),""))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Peter_SSs]

Thanks for the profile update and XL2BB sample.

See how these would go for you. J3 copied down and K3 copied across and down.

20 07 29.xlsm
	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1				56.5	56	55.75	51	51	50.25	50	46.25	43.75	43.5	42.5	42.25
2	Rank	Genotypes	Weight
3	1	BS15	56.5	a
4	2	BS18	56	a
5	3	BS10	55.75	a
6	4	BS16	51				b
7	5	BS20	51				b
8	6	BS9	50.25				b
9	7	BS17	50				b
10	8	BS14	46.25								c
11	9	BS4	43.75									d
12	10	BS7	43.5									d
13	11	BS11	42.5									d
14	12	BS19	42.25									d
maabadi

Cell Formulas
Range	Formula
J3:J14	J3	=IF(AND(OR(J$1-$I3>0,J$1-$I3=0),J$1-$I3<1.6),"a","")
K3:U14	K3	=IF(AND(K$1-$I3<1.6,J$1-$I3<1.6),"",IF(AND(OR(K$1-$I3>0,K$1-$I3=0),K$1-$I3<1.6),CHAR(MAX(IFERROR(AGGREGATE(14,6,CODE($J$3:J$14),1),96),96)+1),""))

 

[maabadi]

Yes. Peter_SSs
I work it and finally find one solution.
I add this formula down of table at J16 and across right.

=LOOKUP(2,1/(J$3:J$14<>""),ROW(J$3:J$14))

Then put J17=1 and add this formula after it at K17 and again across right.

IF(J16=K16,J17,J17+1)

and finally add one table after first table at Columns X to right and add this formula to it.

X3=J3   and  across down
Y3=IF(K3="","",IF(LOOKUP(2,1/(J$3:J$14<>""),ROW(J$3:J$14))=LOOKUP(2,1/(K$3:K$14<>""),ROW(K$3:K$14)),"",MID("abcdefghijklmnopqrstuvwxyz",K$17,1)))

and Across it down and right.
And Problem Solved.

Thanks again.

 

[maabadi]

Thanks again for your solution Peter_SSs.
I don't understand your solution at previous post.
It worked me.

 

[Peter_SSs]

You're welcome.