How to map objects in Excel?-CodePudding

I am not sure if the title makes sense, but you can have a look at the example below. Considering I have a 2-column table in Excel like this:

Fruit   |   Amount
Apple   |     2
Grape   |     4
Orange  |     3

Now I want to have this row:

Apple | Apple | Grape | Grape | Grape | Grape | Orange | Orange | Orange

that when I change the number from "Amount" column in the table, the row changes accordingly as well. For example, if I now have 5 Grapes, it would become:

Apple | Apple | Grape | Grape | Grape | Grape | Grape | Orange | Orange | Orange

Is it possible to do in Excel? I am fairly new with this. Thanks!

CodePudding user response：

This can be achieved with a combination of

REPT - to generate the repated copies of the words
CONCAT - to join those repitions together
FILTERXML - to split the list into cells
TRANSPOSE - to return a row rather than a column

=TRANSPOSE(FILTERXML("<a>"&CONCAT("<b>"&REPT(B5:B7&"</b><b>",C5:C7-1)&B5:B7&"</b>")&"</a>","//b"))

CodePudding user response：

Here is a simple function to do a text multiple. You can then stack up the results.

Function TextMultiplier(MyText As String, MyMultiple As Integer)

Dim Output As String

For a = 1 To MyMultiple
    Output = Output   MyText   " "
Next a

TextMultiplier = Trim(Output)

End Function

Here is how it looks:

enter image description here