SOLVED

How to create a dynamic array based on the other cells

Occasional Contributor

Hello,

I have a requirement where i need to populate the entries based on an input table.

The input table below has 2 columns, VM Type and # of VMs, basically it tells how many VMs are there for each of the VM Type and this number can change for our different deployments.

 

My requirement is to use this as an input and populate all the VM names for all the VM Types. The example output shows the expected result, for example for VM Type3, the input table says it should have 12 VMs and the output shows the VM names from 001 to 013.

also, if the input data changes. for example, the number of VMs for Type3 changes from 12 to 15, the output table should reflect the increase in VMs from 12 to 15 dynamically.

 

Input Table

VM Type# of VMs
Type12
Type25
Type312
Type4

6

 

Expected Output Table

VM Name
Type1001
Type1002
Type2001
Type2002
Type2003
Type2004
Type2005
Type3001
Type3002
Type3003
Type3004
Type3005
Type3006
Type3007
Type3008
Type3009
Type3010
Type3011
Type3012
Type4001
Type4002
Type4003
Type4004
Type4005
Type4006

 

please let me know if any other information is needed.

Thank you guys in advance.

4 Replies

@Vijay_Uppala I'd suggest you look into Power Query, as demonstrated in the attached file. Connect to the Input Table with PQ, do several transformation and load the end result back to Excel as illustrated in the picture below. Add rows to the (structured) blue table. Press "Refresh All" on the Data ribbon and the green table (=PQ output) will update instantly.

Screenshot 2021-12-09 at 07.57.55.png

@Riny_van_EekelenThank you very much for the solution.

Unfortunately, I should have mentioned that I'm working on MAC and I dont believe Power BI is supported on MAC.

Can you think of any other solution?

Thank You

best response confirmed by Sergei Baklan (MVP)
Solution

@Vijay_Uppala 

I have added a couple of dynamic array solutions to @Riny_van_Eekelen 's Power Query solution.  The second uses Lambda functions for the accumulation and most likely is not available to Vijay yet.

@Peter Bartholomew 

The dynamic array solution works really well.

I couldn't get the Lambda function to work, but, as you stated, may be it is not available on Mac.

Thank you very much for all your help.