How can I compact several column of data into 1 column? 1

[snoopy80]

Hi,
I have an Excel table that looks (in a smaller version) like this:

Item Shp1 Qty1 Shp2 Qty2 Shp3 Qty3
------ ---- ---- ---- ---- ---- ----
1234 1/25 1 2/25 1 3/25 1
4567 1/26 1 2/26 1 3/26 1
xx
yy
.
.

Is there a way or codes that allow me to turn this into 1 column as follow:

Item Shp1 Qty1
------ ----- -----
1234 1/25 1
4567 1/26 1
xx
yy
.
.
1234 2/25 1
4567 2/26 1
xx
yy
.
.
1234 3/25 1
4567 3/26 1
xx
yy
.
.

Thanks for help

 

[SkipVought]

Hi,

I'd use MS query via Data/get External Data/New Database Query... Excel Files -- YOUR WORKBOOK -- YOUR SHEET...

3 UNION queries joining ...

Select Item, Shp1, Qty1 
...
UNION
Select Item, Shp2, Qty2
...
UNION
Select Item, Shp3, Qty3
...

Skip,

_{[red]Be Advised![/red] A Last Will and Testament is...
A Dead Giveaway!}

 

[snoopy80]

Thanks for your reply Skip, But I am not sure I understood what you were trying to do. Could you clarify a little? Thanks

 

[CautionMP]

What I wouldn't give for a world where all data is normalized, he, he.

Here is a purely code alternative:

Sub NormalizeData()
Dim wksInput As Worksheet, wksOutput As Worksheet
Dim lngInputRow As Long, lngInputRows As Long, lngOutputRow As Long
Dim intQuantity As Integer

[green]'Reference the input and output sheets[/green]
Set wksInput = Worksheets("Sheet1")
Set wksOutput = Worksheets("Sheet2")

[green]'Activite the first cell in the source sheet
'and return the rows to cycle through[/green]
wksInput.Cells(1, 1).Activate
lngInputRows = ActiveCell.CurrentRegion.Rows.Count

[green]'First row in the output sheet has header so
'start at row two. See inner loop below[/green]
lngOutputRow = 1

[green]'Outer loop keeps track of the three Qty fields[/green]
For intQuantity = 1 To 3
  [green]'Inner loop keeps track of the rows in the
  'source sheet. ASSUMES row 1 is header, hence 2 To...[/green]
  For lngInputRow = 2 To lngInputRows
    [green]'Keeps track of the rows in the destination sheet[/green]
    lngOutputRow = lngOutputRow + 1
    wksOutput.Cells(lngOutputRow, 1) = wksInput.Cells(lngInputRow, 1)
    wksOutput.Cells(lngOutputRow, 2) = wksInput.Cells(lngInputRow, intQuantity * 2)
    wksOutput.Cells(lngOutputRow, 3) = wksInput.Cells(lngInputRow, (intQuantity * 2) + 1)
  Next lngInputRow
Next intQuantity

Set wksInput = Nothing
Set wksOutput = Nothing
End Sub

Hope this helps,
CMP

 

[SkipVought]

Have you ever used Data/get External Data?

Skip,

_{[red]Be Advised![/red] A Last Will and Testament is...
A Dead Giveaway!}

 

[mintjulep]

How big is this table, and how many times do you want to do this?

Brute force cut and paste with columns hidden may wind up being fastest.

 

[snoopy80]

Thanks CMP, the codes work great! That was exactly what I need. Thanks

Skip, I have used Data/get External Data, but mostly to import data from tables in Access DB or SQL DB. I tried your option, But I received this message "This Data source cotains no visible tables". What Did I do wrong?

Thanks for all your help