Turning frequency tables into raw data

**janef1160** · 04-22-2015, 12:17 PM

I would like to be able to convert a table into raw data. I know there is a way of doing it in excel but can't remember how to do it. Would like to convert a list like this...

Ford 1
Audi 5
BMW 3

into this

Ford
Audi
Audi
Audi
Audi
Audi
BMW
BMW
BMW

I think you need to add a column, as in below but can't remember the formular to generate the output

1 ford 1
6 audi 5
9 BMW 3

Thanks

**mehmetcik** · 04-22-2015, 01:31 PM

Try this Macro

Sub Macro1()
'
' Macro1 Macro
'
    Sheets("Sheet1").Select
    Pos = 1
    
LoopStart:
    Entry = Split(Cells(Pos, 1).Value, " ")
    If Entry(1) * 1 > 1 Then
    Rows(Pos + 1 & ":" & Pos + Entry(1) * 1 - 1).Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range(Cells(Pos, 1), Cells(Pos + Entry(1) * 1 - 1, 1)).Value = Entry(0)
    Pos = Pos + Entry(1) * 1
    Else
    Cells(Pos, 1).Value = Entry(0)
    Pos = Pos + 1
    End If
    If Cells(Pos, 1) <> "" Then GoTo LoopStart
    Range("A1").Select
End Sub

**shg** · 04-22-2015, 01:46 PM

Is this what you were remembering?

Row\Col	A	B	C	D
1	Type	Qty	Helper
2	Ford	1	0	C2: =SUM(B1:C1)
3	Audi	5	1
4	BMW	3	6
5			9
6
7	Ford			A7: =INDEX(A$2:A$5, MATCH(ROWS(A$7:A7) - 1, $C$2:$C$5)) & ""
8	Audi
9	Audi
10	Audi
11	Audi
12	Audi
13	BMW
14	BMW
15	BMW
16