Letters change creatria

[sksanjeev786]

Hi Team,

I have letters in rows 7 to 10 and I need to convert all the letters from Row 2 to Row 5 in (Row 7 to 10) and remove all unwanted letters

book2
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
1		LinkedIn : Total - Q2 FY2022	LinkedIn : Total - Q3 FY2022	LinkedIn : Total - Q4 FY2022		Indeed : Total - Q2 FY2022	Indeed : Total - Q3 FY2022	Indeed : Total - Q4 FY2022		Facebook : Total - Q2 FY2022	Facebook : Total - Q3 FY2022	Facebook : Total - Q4 FY2022		LinkedIn : Infreqent members - Q2 FY2022	LinkedIn : Infreqent members - Q3 FY2022	LinkedIn : Infreqent members - Q4 FY2022		LinkedIn : Frequent members - Q2 FY2022	LinkedIn : Frequent members - Q3 FY2022	LinkedIn : Frequent members - Q4 FY2022		LinkedIn : Budding careerist - Q2 FY2022	LinkedIn : Budding careerist - Q3 FY2022	LinkedIn : Budding careerist - Q4 FY2022		LinkedIn : Balancing idealist - Q2 FY2022	LinkedIn : Balancing idealist - Q3 FY2022	LinkedIn : Balancing idealist - Q4 FY2022
2		A	B	C		D	E	F		G	H	I		M	N	O		J	K	L		P	Q	R		S	T	U
3		3	4	5		6	7	8		9	10	11		15	16	17		12	13	14		18	19	20		21	22	23
4
5		A	B	C		A	B	C		A	B	C		A	B	C		A	B	C		A	B	C		A	B	C
6
7	Career Related	30% BEGHI	29% GHI	31% BDEGHI		30% GHI	29% GHI	30% BEGHI		4%	5% GI	4%		36%	36%	37%		38% N	40% MNO	45% JKMNO		14%	26% P	29% P		-	43% PQR	48% PQRT
8	Professional Networking	38% BDEFGHI	36% DEFGHI	37% BDEFGHI		9%	10%	10%		12% DEFI	13% DEFI	12% DEF		43%	43%	45% KN		43%	42%	46% JKMN		17%	30% P	31% P		-	53% PQR	58% PQRT
9	Job Search	26% BGHI	25% GHI	26% BGHI		46% ABCGHI	46% ABCGHI	49% ABCDEGHI		6% I	6% I	5%		31%	30%	32%		38% MNO	40% MNO	42% JMNO		11%	27% P	29% P		-	36% PQR	40% PQR
10	Professional Info	15% DEFGHI	15% DEFGHI	15% DEFGHI		7%	7%	8% DE		11% DEFI	11% DEFI	10% DEF		19%	19%	19%		25% MNO	27% MNO	28% JMNO		7%	15% P	15% P		-	26% PQR	25% PQR
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I4	Cell Value	contains "F"	text	NO
J4	Cell Value	contains "E"	text	NO

 

[Peter_SSs]

.. so your results would look like ... what?

 

[sksanjeev786]

.. so your results would look like ... what?

Hi Peter,

Thank you for looking into it.
Below is my output

30 B 29 31 B 30 29 30 B 4 5 AC 4 36 36 37 38 40 45 AB 38 B 36 37 B 9 10 10 12 C 13 C 12 43 43 45 B 43 42 46 AB 26 B 25 26 B 46 46 49 AB 6 C 6 C 5 31 30 32 38 40 42 A 15 15 15 7 7 8 AB 11 C 11 C 10 19 19 19 25 27 28 A

 

[Peter_SSs]

Thanks for the results. I thought that would clarify your requirement, but I'm afraid that I still cannot see the logic. Could you explain in words how you got the results for this section?

sksanjeev786.xlsm
	H	I	J
1	Indeed : Total - Q2 FY2022	Indeed : Total - Q3 FY2022	Indeed : Total - Q4 FY2022
2	D	E	F
3	6	7	8
4
5	A	B	C
6
7	30% GHI	29% GHI	30% BEGHI
8	9%	10%	10%
9	46% ABCGHI	46% ABCGHI	49% ABCDEGHI
10	7%	7%	8% DE
Sheet1

sksanjeev786.xlsm
	H	I	J
7	30	29	30 B
8	9	10	10
9	46	46	49 AB
10	7	7	8 AB
Sheet2

 

[sksanjeev786]

Thanks for the results. I thought that would clarify your requirement, but I'm afraid that I still cannot see the logic. Could you explain in words how you got the results for this section?

sksanjeev786.xlsm
	H	I	J
1	Indeed : Total - Q2 FY2022	Indeed : Total - Q3 FY2022	Indeed : Total - Q4 FY2022
2	D	E	F
3	6	7	8
4
5	A	B	C
6
7	30% GHI	29% GHI	30% BEGHI
8	9%	10%	10%
9	46% ABCGHI	46% ABCGHI	49% ABCDEGHI
10	7%	7%	8% DE
Sheet1

sksanjeev786.xlsm
	H	I	J
7	30	29	30 B
8	9	10	10
9	46	46	49 AB
10	7	7	8 AB
Sheet2

Hi Peter,

Currently row no 2 and row no 7-10 letters are the same so I need to replace the letter based on Row num 4

so i.e D, E,F I need convert with ABC (D will replace with A , E with B and F with C ) and rest letters need to remove

Let us know if you need more info..

Regards,
Sanjeev

 

[Peter_SSs]

so I need to replace the letter based on Row num 4

I assume that you mean row 5 since row 4 is empty?

I am not sure whether I will have a suggestion or not, but I have more questions anyway:

1. Do you want the results to over-write the values in rows 7:10 or do you want the results in some new rows (eg say rows 17:20?
   
   
2. For each block of columns (eg columns D:F)
   • are the letters in row 2 always in alphabetical order like your samples?
   • are the letters in row 5 always in alphabetical order like your samples?
   • are the letters in rows 7:10 always in alphabetical order like your samples?

 

[sksanjeev786]

I assume that you mean row 5 since row 4 is empty?

I am not sure whether I will have a suggestion or not, but I have more questions anyway:

1. Do you want the results to over-write the values in rows 7:10 or do you want the results in some new rows (eg say rows 17:20?
   
   
2. For each block of columns (eg columns D:F)
   • are the letters in row 2 always in alphabetical order like your samples?
   • are the letters in row 5 always in alphabetical order like your samples?
   • are the letters in rows 7:10 always in alphabetical order like your samples?

Hi Peter,

Yes, I need the output in row 17:20 sorry for the inconvenience
And for 2nd points: row 2 will have a different letter for the following column and 5 will have same ABC
and 7:10 will have the same formate mentioned in the table

Thank you so much for your help on this

Regards,
Sanjeev

 

[Peter_SSs]

And for 2nd points: row 2 will have a different letter for the following column and 5 will have same ABC
and 7:10 will have the same formate mentioned in the table

Perhaps my questions were not clear. What I was asking was

Can the letters in row 2 be out of order? Like this

sksanjeev786.xlsm
	H	I	J
2	D	F	E
Sheet3

Same this for the replacement values in row 5. Can they be out of order like this?

sksanjeev786.xlsm
	H	I	J
5	C	A	B
Sheet3

Similar in rows 7:10. Can they be out of alphabetical order like this?

sksanjeev786.xlsm
	H
9	46% GBIAHC
Sheet3

 

[sksanjeev786]

Perhaps my questions were not clear. What I was asking was

Can the letters in row 2 be out of order? Like this

sksanjeev786.xlsm
	H	I	J
2	D	F	E
Sheet3

Same this for the replacement values in row 5. Can they be out of order like this?

sksanjeev786.xlsm
	H	I	J
5	C	A	B
Sheet3

Similar in rows 7:10. Can they be out of alphabetical order like this?

sksanjeev786.xlsm
	H
9	46% GBIAHC
Sheet3

Hi Peter,

Row no 2 can be out of order but row 4 always be in order ie. ABC and 7:10 will always be in order

Let me know if you need more infomation wtih this

Regards,
Sanjeev