combine lists

[sburkhar]

is there a way to combine the info from several columns into 1 list?

All of my columns have information in rows 1-20, but I want it all to automatically combine into a list in column B on a new sheet.

This is Excel 2007

 

[HalfAce]

This should be a good place to start. (I'm using xl'03 here but it should be the same for '07.)

All this will do is just what you've posted - pasting to column B of Sheet2. (No sorting, testing or anything fancy.)

Sub CopyAllColumnsToSheet2ColumnB()
Dim EndRw As Long, EndCol As Long, Col As Long
EndRw = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
EndCol = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
For Col = 1 To EndCol
  Range(Cells(1, Col), Cells(EndRw, Col)).Copy Sheets("Sheet2").Cells(Rows.Count, "B").End(xlUp)(2)
Next Col
End Sub

Hope it helps.

EDIT:
This copies the entire column of information. If you want to limit it to just rows 1:20 that's easy enough to do.

 

[Peg F]

Hi SBurkhar,

Yes there is a way to combine everything into one list. You can use the concatenate function or you can write a formula. I've included an example of a formula below:

=existing!A4&" "&existing!B4&" "&existing!C4

I used the name existing for the worksheet with the existing data set.

Hope this helps.
Peg

 

[sburkhar]

Concantenate combines everything in the cells into one cell...right?.

I'm looking for a running list of the part numbers. So, instead of having 4 columns with part numbers in rows 1-20, I end will up with one column with part numbers in rows 1-80

 

[sburkhar]

The macro looks promising, but I need to select certain columns from the spreadsheet. This macro looks like it encompasses all the columns.

 

[HalfAce]

So, instead of having 4 columns with part numbers in rows 1-20, I end will up with one column with part numbers in rows 1-80

yes, either Peg's formula approach or a vba approach (like posted above), can each do that.

 

[HalfAce]

Yes, right now it does include all columns with data.
You want to limit it to specific columns or rows? Which ones?

 

[Rick Rothstein]

Does this macro do what you want?

Sub CombineColumnsIntoSingleColumn()
  Dim Col As Range, OutputCell As Range
  Const ColumnsToCombine = "A:A,C:D,F:F"
  Const RowsToCombine = "1:20"
  Const OutputSheet As String = "Sheet5"
  Const OutputStartCell As String = "A1"
  Set OutputCell = Worksheets(OutputSheet).Range(OutputStartCell)
  For Each Col In Range(ColumnsToCombine).Columns
    Intersect(Rows(RowsToCombine), Col).Copy OutputCell
    Set OutputCell = OutputCell.Offset(Rows(RowsToCombine).Count)
  Next
End Sub

Just change each of the constants (Const statements) to match your actual setup (the constant names should be obvious as to what they point to). Note the method used to define the columns in the ColumnsToCombine constant... single, stand-alone columns are specified as a range (column letter, colon, column letter again), ranges of columns are specified as would be expected... all ranges are comma delimited.

 

[sburkhar]

Columns A,H,J,L,N

 

[Rick Rothstein]

Columns A,H,J,L,N

If you want to try the code I posted, change this line...

Const ColumnsToCombine = "A:A,C:D,F:F"

to this...

Const ColumnsToCombine = "A:A,H:H,J:J,L:L,N:N"