Solved: How to add continuous value under one set of column

[kar6842]

For Example,
i have 4 columns first column cell have value = 2, second column cell having value = 3, third column cell having value = 5 and last column cell having value = 6.
i want the result to be like this
2 5
3 6

that means 2 and 3 are continuous so under 2 the value 3 has to come, since 4 is not available the value 5 should be in other column and its continuous 6 to be under 5.

[Bob Phillips]

Just one row or more? IF more, what happend then?

[Simon Lloyd]

Also are you looking for a formula solution or vba?, what does your worksheet structure look like? how and where would you want to present the result? what would be the range of the series?

[kar6842]

[quote=kar6842]For Example,
Please find the attachemnet for Better understanding.
I am looking for a VBA solution.

[kar6842]

Please find the attachemnet for Better understanding.
I am looking for a VBA solution.

[Bob Phillips]

What determines that 2,3,4,5 and 6 all go in the same column?

[mikerickson]

This should do what you want.
[vba]Sub test()
Dim sourceSheet As Worksheet, destinationSheet As Worksheet
Dim sourceRow As Long, destinationStartRow As Long
Dim sourceCheckRange As Range, sourceCheckCell As Range, sourceChunk As Range
Dim destinationColumn As Long, lastCheckVal As Long
Dim destinationCell As Range

Set sourceSheet = ThisWorkbook.Sheets("Input"): Rem adjust
Set destinationSheet = ThisWorkbook.Sheets("Output"): Rem adjust
sourceRow = 4: Rem adjust
destinationStartRow = 4: Rem adjust

With sourceSheet.Rows(sourceRow)
Set sourceCheckRange = Range(.Cells(1, 1), .Cells(1, .Columns.Count).End(xlToLeft))
End With

With destinationSheet
Range(.Range("A1"), .UsedRange).Offset(destinationStartRow - 1, 0).Clear
End With

destinationColumn = 0
lastCheckVal = -2

For Each sourceCheckCell In sourceCheckRange
Rem if non-sequetial, next destination column
If numberFromString(CStr(sourceCheckCell.Value)) <> lastCheckVal + 1 Then
destinationColumn = destinationColumn + 1
End If
lastCheckVal = numberFromString(CStr(sourceCheckCell.Value))

Rem copy data to destination column
With sourceCheckCell.EntireColumn
Set sourceChunk = Range(sourceCheckCell, .Cells(.Rows.Count, 1).End(xlUp))
End With

With destinationSheet.Columns(destinationColumn)
Set destinationCell = destinationSheet.Cells(Application.Max(destinationStartRow, _
.Cells(.Rows.Count, 1).End(xlUp).Row + 1), destinationColumn)
End With

sourceChunk.Copy Destination:=destinationCell

Next sourceCheckCell
End Sub

Function numberFromString(aString As String) As Long
Dim i As Long
For i = 1 To Len(aString)
If Val(Mid(aString, i)) <> 0 Then
numberFromString = Val(Mid(aString, i))
Exit Function
End If
Next i
End Function
[/vba]

[kar6842]

Thank You Very Much. you did what i expected. thank you so much

[kar6842]

how to create a .exe file using Excel with the help of VB?