0

Main table contains names and number of appearance. I need new list of names where every name will be repeated as many times as value in number of appearance cell. Expected result

Darko Petrović
  • 23
  • 1
  • 5

2 Answers2

1

Assuming your "MAIN LIST" is in A2:C7 (including headers), try the following array formula

=IFERROR(FILTERXML("<t><s>"&TEXTJOIN("",TRUE,REPT($B$3:$B$7&"</s><s>",$C$3:$C$7))&"</s></t>","//s"),"")

Note: Clever idea from answer by @VafāSarmast here

Super Symmetry
  • 2,837
  • 1
  • 6
  • 17
  • excel 2019 is required to use TEXTJOIN function. There is errors in function: need to replace every , with ; finction does not provide expected result. No new rows are added. I have tried to correct yours solution widhouth " := IFERROR(FILTERXML(""&TEXTJOIN("";TRUE;REPT($B$3:$B$7;$C$3:$C$7))&"";"//s");"") and got repeated names in same cell. But that is not what I need. Any ideas? – Darko Petrović Dec 25 '20 at 13:26
  • What version of excel are you using? I tested it on 365 – Super Symmetry Dec 25 '20 at 14:05
  • Are you entring the formula with Ctrl+Shift+Enter? It's an array formula – Super Symmetry Dec 25 '20 at 14:06
  • 2019 version, tried also with Ctrl+Shift+Enter but , error persist. Only when replase , with ; works but not as expected – Darko Petrović Dec 25 '20 at 14:26
  • https://drive.google.com/uc?export=download&id=1ir4lszXAoLp_BpvwCPcD4EshSek_w8XM – Super Symmetry Dec 25 '20 at 16:16
  • Its working. My lack of knowledge about arrays was a problem. Thanks a lot – Darko Petrović Dec 25 '20 at 16:32
1

Using JvdV's solution for this Find row number of sum result in range

Assuming your names are in column B and appearance in column C, use the following: =INDEX(B$1:B$7,MATCH(TRUE,--SUBTOTAL(9,OFFSET(C$1:C$7,,,ROW(C$1:C$7)))>=ROWS($1:1);0)

Enter as array formula (ctrl + shift + enter).

It returns the row number of the sum of values in column C that equals the number of rows used in your results list and shows the value in that row from column A.

P.b
  • 8,293
  • 2
  • 10
  • 25