Transpose duplicate rows into columns

Hi,

I've been using this solution found on this forum here (the one in post 2):

http://www.excelforum.com/excel-prog...o-columns.html

However, this solution only works for a 2 column setup. Many times I have multiple columns and need to copy all of those contents into new column. See my attached document for an example.

If anyone knows how to alter the current macro or has a new one that would be a great help. At the moment I'm having to concatenate the columns, move stuff around, and then run the macro and it is a pain.

Any assistance would be great. Cheers.

Hi everyone - I have been bouncing back and forth between this thread and this one http://www.excelforum.com/excel-prog...o-columns.html as well. I too have multiple rows that begin with duplicate numbers (Column A) and then unique data in columns B-J which I want to combine into one row.
If I try the formula, I get a "runtime error 9 subscript out of range".

How can I tweak this so that it will put row A, 1,2,3,4,5 and row B,6,7,8 - and pull the data from ALL of the columns into the row????

(Although in some cases the data is the same in any one of columns B-J for more than one row. (In otherwords, column C is name, D is supplier - so C may be the same for row 2 and 3, but D would be different).

Editing to add further info: I have had luck with the script:

Sub x()

Dim rInput As Range, oDic As Object, sNames() As String, vInput()
Dim i As Long, nIndex As Long

Set rInput = Range("A1", Range("B65536").End(xlUp))
vInput = rInput.Value
ReDim sNames(1 To UBound(vInput, 1), 1 To 2)
Set oDic = CreateObject("Scripting.Dictionary")

With oDic
For i = 1 To UBound(vInput, 1)
If Not .Exists(vInput(i, 1)) Then
nIndex = nIndex + 1
sNames(nIndex, 1) = vInput(i, 1)
sNames(nIndex, 2) = vInput(i, 2)
.Add vInput(i, 1), nIndex
ElseIf .Exists(vInput(i, 1)) Then
sNames(.Item(vInput(i, 1)), 2) = sNames(.Item(vInput(i, 1)), 2) & ", " & vInput(i, 2)
End If
Next i
End With

Cells(1, "H").Resize(nIndex, 2) = sNames
' The line below if you want the words in separate columns
' otherwise they are in a single cell, separated by commas
Cells(1, "I").Resize(nIndex).TextToColumns , comma:=True

End Sub

-------------
works for pulling the data from column b onto the same row.... how do i modify this to pull data from column b, c, d, and e for example?

Hello sethboyardee,

Since you are still a new Member of this Great Forum, it might be possible that you may have forgotten the following;


No. 1. Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

No. 2.Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

Regards.