Formula to return a name a certain number of times

[FryGirl]

I have a date range with names in column A and column G will count how many times this person is signed up for a class between column B to column F. Taking into account how many classes the person is taking, I need to return their names in a list that many times

Data Range

	A​	B​	C​	D​	E​	F​	G​
1​		Class 1​	Class 2​	Class 3​	Class 4​	Class 5​	Count​
2​	Jim​		1​				1​
3​	Jane​	1​		1​		1​	3​
4​	Joe​		1​	1​			2​
5​	Jack​	1​		1​	1​	1​	4​

<tbody>
</tbody>

Results

	J​
1​	List​
2​	Jim​
3​	Jane​
4​	Jane​
5​	Jane​
6​	Joe​
7​	Joe​
8​	Jack​
9​	Jack​
10​	Jack​
11​	Jack​

<tbody>
</tbody>

 

[lrobbo314]

This should do it.

Sub listEm()
Dim AR()
Dim Output()
Dim Cnt As Long


AR = Range("A2:G" & Range("A" & Rows.Count).End(xlUp).Row())
Cnt = 1


For i = 1 To UBound(AR)
    For j = 1 To AR(i, 7)
        ReDim Preserve Output(1 To Cnt)
        Output(Cnt) = AR(i, 1)
        Cnt = Cnt + 1
    Next j
Next i


Range("J1").Resize(UBound(Output), 1).Value = Application.Transpose(Output)


End Sub

 

[Rick Rothstein]

Here is another macro (no loops) that you can also consider...

[table="width: 500"]
[tr]
	[td]Sub ListByCounts()
  Dim LastRow As Long, Arr As Variant
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Arr = Split(Join(Application.Transpose(Evaluate(Replace("IF({1},REPT(A2:A#&"" "",G2:G#))", "#", LastRow))), ""))
  Range("J2").Resize(UBound(Arr) + 1) = Application.Transpose(Arr)
End Sub[/td]
[/tr]
[/table]

 

[lrobbo314]

Here is a link to another way of doing it with helper columns and formulas...

 

[sheetspread]

The reverse pivot table does this:

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

But if you must have a formula:

Excel 2010
	A	B	C	D	E	F	G
1		Class 1	Class 2	Class 3	Class 4	Class 5	Count
2	Jim		1				1
3	Jane	1		1		1	3
4	Joe		1	1			2
5	Jack	1		1	1	1	4
6
7	Jim
8	Jane
9	Jane
10	Jane
11	Joe
12	Joe
13	Jack
14	Jack
15	Jack
16	Jack
Sheet4

Cell Formulas
Range	Formula
G2	=SUM(B2:F2)
A7	=LOOKUP(ROW(A1),SUMIF(OFFSET(G$1,,,ROW($1:$99),),"<>")+1,A$2:A$99)&""

 

[FryGirl]

Thanks all for the solutions.

sheetspread,

Yes, a formula solution is exactly what I was after. Thank you for your time.