Transpose with a twist

[TFRY]

Hi,


I would like to be able to transpose a column of data to more than 1 row. For example I would like to be able to do the following:


From

1

1

1

1

2

2

3

3

3

3


To

1 2 3

1 2 3

1 3

1 3


Any help is greatly appreciated.

 

[TFRY]

It is posting weird. But what I want is that if the last row is the same as the current row I want the data to be under it. If the data in the last row is different from the current row i want to to be in a different column

 

[shrivallabha]

Hi TFRY,


I have query for you. Suppose if I have data as below:


1

2

3

1

2

3

1

3

1

3


Will the result still be?

[pre]

1 2 3
1 2 3
1   3
1   3

[/pre]
Or your data will be always sorted or sort is allowed.


Wrap data with backtick to retain formatting of your post.

 

[TFRY]

Yes you can sort the data but that is what the outcome I want it to look like

 

[NARAYANK991]

Hi ,


This is a probably a complicated way of doing things but it should work.


1. Let us assume you have a dynamic named range called List , referring to the column of input data. I have assumed this is on Sheet1.


2. Let us assume you want the output on a different sheet , say Sheet2 , starting from cell A2.


3. You need to decide what the first output cell should contain ; is it the first cell in the input data , or is it the least of the numbers in List ; I have assumed it is the first cell in the input data ; to get this , you can either give a direct formula such as =Sheet1!A2 or the generic =INDEX(List,1)


4. Now , the first row of data will serve as the header row , since all the remaining data will come in the appropriate column under the earlier data.


5. In cell Sheet2!B2 , put in the following formula , entered as an array formula , using CTRL SHIFT ENTER :


=IFERROR(INDEX(List,MATCH(0,IF(ISNA(MATCH(List,$A$2:A2,0)),0),0)),"")


6. Now , starting from A3 , enter the following formula :


=IF(ROW()-ROW($A$2)<COUNTIF(List,A$2),A$2,"")


Copy this down and across.


Note that the input data need not be sorted.


Narayan

 

[shrivallabha]

Here's VBA based complicated way of doing things. I bet now you will like Narayan's solution more.


Wanted to try out new object which seemed suitable for its indexing and sorting ability. It doesn't need to be resized (ReDim) as in array.


Please refer this link to know about the object:

http://msdn.microsoft.com/en-us/library/system.collections.arraylist.aspx?cs-save-lang=1&cs-lang=vb#code-snippet-2

[pre]

Option Explicit
Public Sub RearrangeData()
Dim arl1 As Object, arl2 As Object
Dim vRng As Variant
Dim bool As Boolean
Dim lCol As Long, i As Long, j As Long

vRng = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row).Value
Set arl1 = CreateObject("System.Collections.ArrayList")
Set arl2 = CreateObject("System.Collections.ArrayList")

'Build Unique list
For i = LBound(vRng) To UBound(vRng)
If Not arl1.contains(vRng(i, 1)) Then
arl1.Add vRng(i, 1)
End If
Next i
'Sort arraylist data
arl1.Sort

'Build up second list getting count of elements
For i = 0 To arl1.Count - 1
For j = LBound(vRng) To UBound(vRng)
If arl1(i) = vRng(j, 1) And bool = False Then
arl2.Add 1
bool = True
ElseIf arl1(i) = vRng(j, 1) And bool = True Then
arl2(arl2.Count - 1) = arl2(arl2.Count - 1) + 1
End If
Next j
bool = False
Next i

'Find out last used column
lCol = Cells.Find("*", [A1], xlFormulas, xlPart, xlByColumns, xlPrevious).Column + 1

'Paste data
For i = 0 To arl1.Count - 1
Cells(1, lCol + i).Resize(arl2(i), 1).Value = arl1(i)
Next i

'Release arraylists
Set arl1 = Nothing
Set arl2 = Nothing
End Sub

[/pre]