Duplicate List items based on varying quantities?

[Wolfspyda]

I'm having some issues figuring out if you can create a dynamic list of values in a range, based on separate quantities per item. I have seen it done via VBA, but I'm trying to avoid that if I can.

This is an example of the Data:-

Code Qty
1 Lax 3
2 SF 1
3 Tan 2
4 Go 1

Id like the Dynamic list to show the following

Lax
Lax
Lax
SF
Tan
Tan
Go


Any help would be great. I have seen something like this in ExcelMagic Trick 493, but the list would duplicate based on one "number of repeats".

 

[sheetspread]

https://www.mrexcel.com/forum/excel...tiplied-number-create-list-2.html#post4069074

 

[Wolfspyda]

Thanks heaps for that. That is a much cleaner way of doing it I feel.

I ended up creating a helper cell

=IF($C1+$B1>SUM($B$1:$B$4)+1,"",$C1+$B1)

that would give me the following "reference" number is the C Column


Code Qty Helper D E
1 Lax 3 1 Lax
2 SF 1 4 SF
3 Tan 2 5 Tan
4 Go 1 7 Go
8

=IFNA(VLOOKUP(IF(ROWS($D$1:$D1)>SUM($B$1:$B$4),"",ROWS($D$1:$D1)),$C$1:$D$4,2),"")

 

[sheetspread]

Very similar principle, good deduction. Another non macro way is with the reverse pivot table:

https://www.youtube.com/watch?v=xmqTN0X-AgY

which requires a simple formula:

Excel 2010
	A	B	C	D	E	F	G
1	Code	Qty
2	Q	2	TRUE	TRUE	FALSE	FALSE	FALSE
3	W	2	TRUE	TRUE	FALSE	FALSE	FALSE
4	E	4	TRUE	TRUE	TRUE	TRUE	FALSE
5	R	1	TRUE	FALSE	FALSE	FALSE	FALSE
6	T	5	TRUE	TRUE	TRUE	TRUE	TRUE
7	Y	3	TRUE	TRUE	TRUE	FALSE	FALSE
Sheet5

Cell Formulas
Range	Formula
C2	=COUNTA($B2:B2)<=$B2

and filter everything not TRUE:

Excel 2010
	A	B	C
1	Row	Column	Value
3	Q		TRUE
4	Q		TRUE
9	W		TRUE
10	W		TRUE
15	E		TRUE
16	E		TRUE
17	E		TRUE
18	E		TRUE
21	R		TRUE
27	T		TRUE
28	T		TRUE
29	T		TRUE
30	T		TRUE
31	T		TRUE
33	Y		TRUE
34	Y		TRUE
35	Y		TRUE
Sheet7

 

[Wolfspyda]

Interesting.

The above idea is essentially going to be used as a helper.

The major goal is that for each “Code” (LAX) there is a corresponding range of data ($A$7:$Z$13). I have given that range a defined name “LAX_Rng”. The issue I was and still am having is duplicating that same range based on the Qty.

I have made an array formula that finds the range based on the first “Code” in the new list and returns the correct range. The issue is I haven’t firgured out the correct way to tell it to find the last cell in Column A to copy the same Rng based on the Qty.

I’m trialling an Offset function now as this is a dynamic formula that grows a number of Rows based on the size of each Rng. I have a count function that has calculated the Number of Rows per Rng, but now I’m stuck.

 

[sheetspread]

It sounds like the reverse pivot table above or unpivot in Power Query is a better option than formulas, but you can also look up the nth match or row/column without offset or a last cell finder. Post a more comprehensive sample and I'll show you.

 

[Wolfspyda]

Ok, so what I've described above is a dumbed down version of what I'm doing, as the Data set is quiet large ($A$6:$DU$1630). This Data set is a Power Query from a csv file that is now in a table on a separate sheet. That being said ill use the smaller values so it doesn't get too crazy.

So as stated there is a table/array/range of Data that corresponds to each Code.

As an example ill list a couple of names based on the above example.

LAX = ($A$1:$Z$4) its named range is LAX_Rng
A B C etc to Z
1 BP-1 2000 200
2 TP-2 2000 200
3 St-3 2600 80
4 St-4 2600 80

SF = ($A$6:$W$10) its named range is SF_Rng
A B C etc to Z
6 BP-1 3000 200
7 TP-2 3000 200
8 St-3 2600 80
9 St-4 2600 80
10 St-5 2600 80


Ive tried a few formulas but the end result needs to keep the same format, place the first range and repeat N times below itself until all ranges have been added in.

so the result would look like this:-

A B C etc to Z
1 LAX(1)-BP-1 2000 200
2 LAX(1)-TP-2 2000 200
3 LAX(1)-St-3 2600 80
4 LAX(1)-St-4 2600 80
5 LAX(2)-BP-1 2000 200
6 LAX(2)-TP-2 2000 200
7 LAX(2)-St-3 2600 80
8 LAX(2)-St-4 2600 80
9 LAX(3)-BP-1 2000 200
10 LAX(3)-TP-2 2000 200
11 LAX(3)-St-3 2600 80
12 LAX(3)-St-4 2600 80
13 SF(1)-BP-1 2000 200
14 SF(1)-TP-2 2000 200
15 SF(1)-St-3 2600 80
16 SF(1)-St-4 2600 80
etc


This is the real formula that I am currently using to find the correct Range based on the first result in the list.

={IF(AND(OrderForm!AI17=CNC_Tbl!$A$6,ROWS($A$5:A5)>VLOOKUP(OrderForm!AI17,CNC_Tbl!DX5:DY180,2)),IF(OrderForm!AI17=CNC_Tbl!$A$6,W2630NB_N3_CNC,""),"")}

each range has different numbers of Rows that i have a count for but ranges from 9 out to 30 Rows. After you mentioned Power Query it got me thinking that I could do a search Power Query to make the table. Once this table is complete i need to send it back out as a csv file.

 

[sheetspread]

You can write an append or union query to vertically merge the tables, and/or join to a table of row repeat #s attaining the format in your original post. Since you're already using Power BI, a Related() function in DAX is much better than vlookup.

 

[Wolfspyda]

After your suggestion of using Power Query i have tried this and the Query will pull all the relevant tables, but if there are duplicates it wont copy those.

 

[sheetspread]

It shouldn't unless you're trying to remove the duplicates. I only have powerpivot on this machine so can't test. Did you check the keep rows section (keep rows/keep duplicates)? May not solve the problem but it's all I can think of.