Formula to turn Text/Number list into a Text list based on the Numbers

[mimos]

Is there a formulaic way to turn a list in two columns like this:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Albert[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Charles[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]

into a new list in a single column like this (where the number of times that a name appears is dynamic based on the number next to it in the original data like above):

Albert
Albert
Albert
Bob
Bob
Charles
Charles
Charles
Charles

I feel like I need an incrementing variable and am not sure how to accomplish that in Excel or if it's even possible. Thanks for any assistance you can provide.

 

[James006]

Hello,

Assuming your list starts in cell A2 ...

You could have in cell B2, an Array Formula (instead of the Enter key ... you need to use simultaneously the three keys : Control Shift Enter

=IFERROR(INDEX($A$2:$A$10,MATCH(0,COUNTIF($B$1:B1,$A$2:$A$10),0)),"")

and in cell C2

=COUNTIF($A$2:$A$10,B2)

Hope this will help

 

[mimos]

Thanks, James - That works the other direction from what I'm trying to do. I'm starting with the Albert 2, Bob 2, Charles 4 data and trying to get the single list of names in one column, not the other way around.

 

[lrobbo314]

You can do what you are looking for with PowerQuery. It's explained here in this post. https://www.mrexcel.com/forum/power-bi/977045-pq-repeat-rows-n-times.html

 

[mimos]

Yes that's what I'm looking for! It was a hard thing to search for. Thank you!

 

[James006]

Sorry ..

Read your question too quickly ...

 

[Eric W]

Can be done with a helper column:

	A	B	C	D	E	F
Name	Count	Helper	List
Albert
Albert
Albert
Bob
Charles
Charles
	Charles
Charles

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Albert[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Bob[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Charles[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]Bob[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

</tbody>

Sheet19​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=SUM($B$1:$B1)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]=IFERROR(INDEX($A$2:$A$4,MATCH(ROWS($E$2:$E2)-1,$C$2:$C$5)),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Probably can be done without the helper, but the formula would be much messier.