How to type a word and fill some cells with numeric values associated

One way:

AliGW on MS365 Insider (Windows) 32 bit

	L	M	N
2	LETTERS	VALUE	COUNT
3	F	1	1
4	A	2	2
5	K	3	4
6	E	4	7

Sheet: Plan1

In N3 copied down:

=SUM(M$3:M3)-M3+1

In A15 copied across:

=INDEX($L$3:$L$6,MATCH(IF(--RIGHT(A13,1)=0,10,--RIGHT(A13,1)),$N$3:$N$6,1))

Hi Aligw,

Thank you for the message, but I canīt add the COUNT column in the TABLE, because it is a static table. This is just a small example. I have all letter of the alphabet there with all values. The problem here is if I want type the word FEK instead of FAKE it will not work. The column value is also the number of times that the letter should be repeat.

So, if I want type the word FEK it will not put the letter correct below each year.


2021 2022 2023 2024 2025 2026 2027 2028 2029 2030 2031 2032 2033 2034

F E E E E K K K


Best Regards

I have clearly demonstrated that I understand what you want - did you look at the attachment I provided? Repeating what you have already said isn't going to help.

Why are you 'unable' to add a helper column?

Try this instead:

=INDEX($L$3:$L$6,MATCH(IF(--RIGHT(A13,1)=0,10,--RIGHT(A13,1)),{1,2,4,7},1))

The problem here is if I want type the word FEK instead FAKE, it will not respect the COUNT COLUMN. The COLUMN VALUE also means how many times this letters should be repeated.

This requirement was not stated in your opening post. My solutions work for what you asked for originally.

PLEASE STOP EDITING PREVIOUS POSTS!

You are making my replies look nonsensical!!!

If you want to add extra detail, add an extra post to the thread.

Provide two or three scenarios that show all possible situations and I will have another look. At the moment, it feels as if the goalposts are going to be shifting all day.

Once you have provided a more realistic sample workbook, I'll take another look.

Are you still using Excel 2013 or something newer?

If by chance you have MS365 or Excel 2021, this should work. In A8 copied across:

=LET(l,SORT($Q$2:$Q$27),v,SORTBY($R$2:$R$27,$Q$2:$Q$27,1),MID(REPT(TEXTJOIN(,1,IFNA(REPT($A$3:$L$3,LOOKUP($A$3:$L$3,l,v)),"")),10),COLUMN(),1))

VBA

Please Login or Register  to view this content.

Alternatives to post #9:

=LET(l,SORT($Q$2:$Q$27),v,SORTBY($R$2:$R$27,$Q$2:$Q$27,1),w,$A$3:$L$3,MID(REPT(TEXTJOIN(,1,IFNA(REPT(w,LOOKUP(w,l,v)),"")),10),COLUMN(),1))

or:

=LET(c,COLUMNS($A1:A1),l,SORT($Q$2:$Q$27),v,SORTBY($R$2:$R$27,$Q$2:$Q$27,1),w,$A$3:$L$3,MID(REPT(TEXTJOIN(,1,IFNA(REPT(w,LOOKUP(w,l,v)),"")),10),c,1))

That's a shame. Go with John's VBA solution, then.

Yes, I understand this - I said that the solution I offered would work if you had Excel 2021 or MS365.

I have advised you to use the VBA solution offered by John Topley instead.

If you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.