How to map objects in Excel?

Question

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!

Answer 1

Currently in BETA, but if available through insider's plan, try to use SCAN():

Formula in D1:

=XLOOKUP(SEQUENCE(1,SUM(B2:B4)),SCAN(0,B2:B4,LAMBDA(a,b,a+b)),A2:A4,,1)

---

Let me try and explain the steps:

• The SCAN() functionality can be used to create a running total. A starting value of 0 will continue to grow through recursive LAMBDA() functionality. In the current example this will return {2,6,9}.
• Now the idea is to find approximate matches using XLOOKUP(). The input for this function is an array created through SEQUENCE() where the amount of columns will equal the SUM() of all amounts. Thus; {1,2,3,4,5,6,7,8,9} in this case. Using the 5th parameter we can return the position of the exact or nearest higher value in the lookup array we found using SCAN(). The result: {1,1,2,2,2,2,3,3,3}.
• The next and last step is also done in XLOOKUP() where we used A2:A4 inside the 2nd parameter to return elements from the previous found numeric indices. Obviously the result will then be {"Apple","Apple","Grape","Grape","Grape","Grape","Orange","Orange","Orange"} .

---

I tried to visualize the above in a process that may help a little.

---

And as your amount change, so does the output as required: