Thread: Change data from vertical to horizontal?

Hi, I am very new to excel and im trying to figure out this simple project. We sell T-shirts and we save our inventory in a database. When we get our csv file, this is how it comes out.


A B C D E
1 style sml med lrg xl
2 8220 y y y y
3 8230 y n n y
4 8240 n y y y
5 8250 n n n n
6 8260 y y n y

We need to have it changed to how it looks below.

A B C
1 8220 sml y
2 8220 med y
3 8220 lrg y
4 8220 xl y
5 8230 sml y
6 8230 med n
7 8230 lrg n
8 8230 xl y
9 8240 sml n
10 8240 med y
11 8240 lrg y
12 8240 xl y
13 8250 sml n
14 8250 med n
15 8250 lrg n
16 8250 xl n
17 8260 sml y
18 8260 med y
19 8260 lrg n
20 8260 xl y


Here we are only displaying 5 products, but we have 100's of products that we need to do this to. If there is anyone that can help me figure this out, i would appreciate it so much! Thank you to anyone that puts in effort trying to help us with this project!!! We really got stuck on this one part and we feel handicapped...

Thanks!

The easiest way is going to be with a macro. Here's the code:

Option Explicit

Sub NewTable()
'JBeaucaire  (9/12/2009)
Dim LR As Long, i As Long, ws As Worksheet

If ActiveSheet.Name = "NewTable" Then
    MsgBox "Please start the macro from the sheet with the new data."
    Exit Sub
End If

Set ws = ActiveSheet

If Not Evaluate("ISREF(NewTable!A1)") Then    'If sheet doesn't exist, create it
    Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "NewTable"
    Range("A1") = "Style"
    Range("B1") = "Size"
    Range("C1") = "Status"
    With Range("A1:C1")
        .Font.Bold = True
        .Borders(xlEdgeBottom).Weight = xlMedium
        .Borders(xlEdgeBottom).LineStyle = xlContinuous
    End With
    Range("A2").Select
    ActiveWindow.FreezePanes = True
    ws.Activate
Else
    Sheets("NewTable").Range("A2:AA" & Rows.Count).Clear
End If

LR = Range("A" & Rows.Count).End(xlUp).Row

Range("A2:A" & LR).Copy Sheets("NewTable").Range("A2")
Range("B2:B" & LR).Copy Sheets("NewTable").Range("C2")
Range("A2:A" & LR).Copy Sheets("NewTable").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
Range("C2:C" & LR).Copy Sheets("NewTable").Range("C" & Rows.Count).End(xlUp).Offset(1, 0)
Range("A2:A" & LR).Copy Sheets("NewTable").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
Range("D2:D" & LR).Copy Sheets("NewTable").Range("C" & Rows.Count).End(xlUp).Offset(1, 0)
Range("A2:A" & LR).Copy Sheets("NewTable").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
Range("E2:E" & LR).Copy Sheets("NewTable").Range("C" & Rows.Count).End(xlUp).Offset(1, 0)

Sheets("NewTable").Activate
LR = Range("A" & Rows.Count).End(xlUp).Row
ws.Range("B1:E1").Copy
Range("B2:B" & LR).PasteSpecial Paste:=xlPasteAll, Transpose:=True
Application.CutCopyMode = False

Columns("A:C").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
End Sub

You'll need to insert this permanently into a workbook.

How to use the macro:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save your sheet

The macro is installed and ready to use.

First, bring up your CSV file so that it is onscreen, then press Alt-F8 and select this macro from the list.

It will create a worksheet called NewTable and put the data in the format you want. You can move the resulting sheet elsewhere or to another book, up to you.