Row\Col | A | B | C | D | E | F | G |
2 | apel | 1 | apel | 1 | 3 | ||
3 | apel | 3 | jeruk | 6 | 2 | 5 | |
4 | jeruk | 6 | mangga | 4 | 7 | ||
5 | jeruk | 2 | |||||
6 | jeruk | 5 | |||||
7 | mangga | 4 | |||||
8 | mangga | 7 |
C | D | E | F | G | H | I | J | K | reftable | |||||||
apel | 1 | 1 | apel | 4 | ||||||||||||
apel | 3 | 13 | 13 | apel13 | 2 | 6 | 1 | 3 | apel | jeruk | 5 | |||||
jeruk | 6 | 6 | mangga | 6 | ||||||||||||
jeruk | 2 | 62 | ||||||||||||||
jeruk | 5 | 625 | 625 | jeruk625 | 3 | 8 | 6 | 2 | 5 | jeruk | ||||||
mangga | 4 | 4 | ||||||||||||||
mangga | 7 | 47 | 47 | mangga47 | 2 | 8 | 4 | 7 | mangga | |||||||
col C concatenate the scores | ||||||||||||||||
col D separate out the concatenated scores | ||||||||||||||||
col E concatenate the name to col D | ||||||||||||||||
col F the length of column A minus the length of the name | ||||||||||||||||
col G how many numerals | ||||||||||||||||
col H first numeral | ||||||||||||||||
col I second numeral | ||||||||||||||||
col J third numeral | ||||||||||||||||
col K the name | ||||||||||||||||
easy now to work with cols H:K to make desired table | ||||||||||||||||
all these columns can be located out of sight | ||||||||||||||||
if you choose to go this way I will post formulas | ||||||||||||||||
something like....
D2=IFERROR(INDEX($A$2:$A$8,SMALL(IF(FREQUENCY(IF($A$2:$A$8<>"",MATCH($A$2:$A$8,$A$2:$A$8,0)),ROW($A$2:$A$8)-ROW($A$2)+1),ROW($A$2:$A$8)-ROW($A$2)+1),ROWS($D$2:D2))),"") Control Shift Enter
E2=IFERROR(INDEX($B$2:$B$8,SMALL(IF($A$2:$A$8=$D2,ROW($A$2:$A$8)-ROW($A$2)+1),COLUMNS($E2:E2))),"") Control Shift Enter
Row\Col A B C D E F G 2apel 1apel 1 3 3apel 3jeruk 6 2 5 4jeruk 6mangga 4 7 5jeruk 2 6jeruk 5 7mangga 4 8mangga 7
<tbody>
</tbody>