i have
5 4 7 9 (non adjascent columns, 3 columns apart each
8 6 9
4 1 7

how do i go about copying this data, then pasting it all in one row (does not matter which order












3

Assuming that the your data starts in cell A1, with adjacent rows but spaced three columns apart, you could use the calculations below to get everything in one column. You could then Paste Special Values or Paste Special Values Transposed to get a single column or row of data values. It could be quicker to paste the data manually, but assuming you have a lot more than 3 rows and 4 columns the below approach might be useful. (Once you have some initial values in the first two columns to establish a pattern, you can change to formulas and copy them down as far as you need to.)

JB.

X Y Z

Row 1 1 1 =OFFSET($A$1,X1-1,3*(Y1-1))
Row 2 1 2 =OFFSET($A$1,X2-1,3*(Y2-1))
Row 3 1 3 =OFFSET($A$1,X3-1,3*(Y3-1))
Row 4 1 4 =OFFSET($A$1,X4-1,3*(Y4-1))
Row 5 2 1 =OFFSET($A$1,X5-1,3*(Y5-1))
Row 6 2 2 =OFFSET($A$1,X6-1,3*(Y6-1))
Row 7 2 3 =OFFSET($A$1,X7-1,3*(Y7-1))
Row 8 2 4 =OFFSET($A$1,X8-1,3*(Y8-1))
Row 9 =X5+1 =Y5 =OFFSET($A$1,X9-1,3*(Y9-1))
Row 10 =X6+1 =Y6 =OFFSET($A$1,X10-1,3*(Y10-1))
Row 11 =X7+1 =Y7 =OFFSET($A$1,X11-1,3*(Y11-1))
Row 12 =X8+1 =Y8 =OFFSET($A$1,X12-1,3*(Y12-1))
etc. (copy) (copy) (copy)
etc. (copy) (copy) (copy)
etc. (copy) (copy) (copy)
etc. (copy) (copy) (copy)



***********
i have
5 4 7 9 (non adjascent columns, 3 columns apart each)
8 6 9 1
4 1 7 3

how do i go about copying this data, then pasting it all in one row (does not matter which order)

5
4
7
9
8
6
9
1
4
1
7
3

If the every third column is just an example then the following
macro would probably work:


Option Explicit

Sub copyNBentries()
Dim rng As Range, cell As Range, oldname As String
Dim i As Long, newsht As Worksheet
Set rng = Intersect(ActiveSheet.UsedRange, Selection)
Dim x As String
If rng.Cells.Count < 2 Then
MsgBox "single cell range is unacceptable for selection"
Exit Sub
End If
oldname = ActiveSheet.Name
'Create New Sheet
Sheets.Add After:=Sheets(Sheets.Count) '-- place at end
'Rename current Sheet
ActiveSheet.Name = Trim(oldname & "_" & _
Format(0 + Now(), "yyyymmddhhmmss"))
For Each cell In rng

If Replace(cell.Value, " ", "") < "" Then
i = i + 1
On Error Resume Next
ActiveSheet.Cells(i, 1).Value = cell.Value
If Err.Number < 0 Then
ActiveSheet.Cells(i, 3) = Err.Number & " " & Err.Description
End If
If cell.HasFormula Then ActiveSheet.Cells(i, 2).Formula = "'" & cell.Formula
End If
Next cell
Exit Sub
End Sub

If you're not familiar with macros see
http://www.mvps.org/dmcritchie/excel/getstarted.htm

I can get the macro to fail (noted in column C) with a formula in the worksheet
like ="= " and don't know why.
1004 Application-defined or object-defined error

Because of 0 and space in some cells due to formulas,
I added column B to show formula, and column C to show errors
You can delete columns B & C if you are satisfied with result.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"choice" wrote in message ...
i have
5 4 7 9 (non adjascent columns, 3 columns apart each)
8 6 9 1
4 1 7 3

how do i go about copying this data, then pasting it all in one row (does not matter which order)

5
4
7
9
8
6
9
1
4
1
7
3