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

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

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

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.