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
[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD][/TD]
[TD]

Class 1​

[/TD]
[TD]

Class 2​

[/TD]
[TD]

Class 3​

[/TD]
[TD]

Class 4​

[/TD]
[TD]

Class 5​

[/TD]
[TD]

Count​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

Jim​

[/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

Jane​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

Joe​

[/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

Jack​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD]

1​

[/TD]
[TD]

1​

[/TD]
[TD]

4​

[/TD]
[/TR]
</tbody>[/TABLE]

Results
[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

J​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

List​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

Jim​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

Jane​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

Jane​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

Jane​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

Joe​

[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]

Joe​

[/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]

Jack​

[/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]

Jack​

[/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]

Jack​

[/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD]

Jack​

[/TD]
[/TR]
</tbody>[/TABLE]

 

[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.