List items based on the count given
 

Hi all,

I have a problem that requires some help here.

I have a table that list items and the frequency for each item, as shown below
Item frequency
A 1
B 3
C 4
D 1
E 0

Base on the above, the system should populate a list base on the above
Expected result is:

Item Count
A 1
B 1
B 2
B 3
C 1
C 2
C 3
C 4
D 1

Item E is not displayed because the frequency count is zero.

Anyone could assist on this?
Many thanks in advance

List items based on the count given
 

Hi,

Am Wed, 12 Mar 2014 07:54:39 +0000 schrieb CS Chia:

I have a table that list items and the frequency for each item, as shown
below
Item frequency
A 1
B 3
C 4
D 1
E 0

Base on the above, the system should populate a list base on the above
Expected result is:

Item Count
A 1
B 1
B 2
B 3
C 1
C 2
C 3
C 4
D 1

your table in sheet1. The following macro writes the expected table in
sheet2:

Sub ArrangeTable()
Dim arrIn As Variant
Dim LRow As Long
Dim i As Long, j As Long, n As Long

LRow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
arrIn = Range("A2:B" & LRow)
n = 1
With Sheets("Sheet2")
For i = LBound(arrIn) To UBound(arrIn)
If arrIn(i, 2) 0 Then
For j = 1 To arrIn(i, 2)
.Cells(n, 1) = arrIn(i, 1)
.Cells(n, 2) = j
n = n + 1
Next
End If
Next
End With
End Sub


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Hi Claus,

Thanks for your feedback.
However, my company has restriction in the use of Macro.
As such, I may not be able to implement your proposal.

regards,
CS

List items based on the count given
 

I have a table that list items and the frequency for each item ...
Base on the above, the system should populate a list ...

Hopefully, this will help get started.

I lengthened columns A and B to test better for zeros:

Item frequency
A 1
B 3
C 4
D 1
E 0
F 0
G 3
H 5
I 1
J 0
K 0
L 0
M 0
N 5

In E2 put
=A2

In F2 put
1

In E3 put
=IF(COUNTIF(E$2:E2,E2)=VLOOKUP(E2,A:B,2,FALSE),
INDEX(A:A,MATCH(E2,A:A,0)+1+INDEX(D:D,MATCH(E2,A:A ,0)+1)),
E2)

In F3 put
=IF(E3=E2,F2+1,1)

In D2 put
=IF(B2=0,D3+1,0)
and copy down to D3.

Select D3:F3 and copy down as long as items from column A appear in column E.

The desired result is in columns E:F.

Column D is a helper column and can be hidden.

Result is
Item Count
A 1
B 1
B 2
B 3
C 1
C 2
C 3
C 4
D 1
G 1
G 2
G 3
H 1
H 2
H 3
H 4
H 5
I 1
N 1
N 2
N 3
N 4
N 5

List items based on the count given
 

Hopefully, this will help get started.

I lengthened columns A and B to test better for zeros.

Update:

Instead of "=A2" put this in E2:
=IF(B2=0, INDEX(A:A,D2+2),A2)
This takes care of the case when cell B2 contains 0.