How do I do this.. Or can I?
 

I have data that looks like the example below:
fistname, last name, multiple rows, each with different data
RON SMITH A
RON SMITH B
RON SMITH C
RON SMITH D
RON SMITH H


My merge program reads data like the sample below:
First name. last name, one row, but multiple cells, each with different data.

RON SMITH A B C D H

Can someone tell me the best way to convert my existing data into the format
above?

Any help is much appreciated.

Thanks
R

How do I do this.. Or can I?
 

It may be shorten but this is what I "found in my sleeve":
http://img525.imageshack.us/img525/6234/nonamek.png
Micky


"Roman" wrote:

I have data that looks like the example below:
fistname, last name, multiple rows, each with different data
RON SMITH A
RON SMITH B
RON SMITH C
RON SMITH D
RON SMITH H


My merge program reads data like the sample below:
First name. last name, one row, but multiple cells, each with different data.

RON SMITH A B C D H

Can someone tell me the best way to convert my existing data into the format
above?

Any help is much appreciated.

Thanks
R

How do I do this.. Or can I?
 

That works great. I tried it out , but question for you. How do i use the
same formula for 1500 different names and 3700 rows of data?


"מיכאל (מיקי) אבידן" wrote:

It may be shorten but this is what I "found in my sleeve":
http://img525.imageshack.us/img525/6234/nonamek.png
Micky


"Roman" wrote:

I have data that looks like the example below:
fistname, last name, multiple rows, each with different data
RON SMITH A
RON SMITH B
RON SMITH C
RON SMITH D
RON SMITH H


My merge program reads data like the sample below:
First name. last name, one row, but multiple cells, each with different data.

RON SMITH A B C D H

Can someone tell me the best way to convert my existing data into the format
above?

Any help is much appreciated.

Thanks
R

How do I do this.. Or can I?
 

You could use a macro.

If you want to try...

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim TopCell As Range
Dim BotCell As Range

Set wks = Worksheets("Sheet1")

With wks
'the code adds headers!
.Range("a1").Resize(1, 4).Value _
= Array("h1", "h2", "h3", "h4")
FirstRow = 2 'avoid the header row
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

Set TopCell = Nothing 'to start process

For iRow = FirstRow To LastRow + 1 'to get last group
If .Cells(iRow, "A").Value = .Cells(iRow - 1, "A").Value _
And .Cells(iRow, "B").Value = .Cells(iRow - 1, "B").Value Then
'do nothing, still in the current group
Else
Set BotCell = .Cells(iRow - 1, "c")
If TopCell Is Nothing Then
'do nothing, first record
Else
'start of new group, so
'do copy|paste special|transpose
'of old group
.Range(TopCell, BotCell).Copy
TopCell.Offset(0, 1).PasteSpecial Transpose:=True
End If
'get ready for next time
Set TopCell = .Cells(iRow, "c")
Set BotCell = .Cells(iRow, "c")
End If
Next iRow

'uncomment these when your tests look ok.
'remove column C
.Columns(3).Delete

'get rid of the empty rows based on column C
.Columns(3).Cells.SpecialCells(xlCellTypeBlanks).E ntireRow.Delete

'get rid of the added headers
.Rows(1).Delete

End With

End Sub

If you're new to macros:

Debra Dalgleish has some notes how to implement macros he
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

Roman wrote:

I have data that looks like the example below:
fistname, last name, multiple rows, each with different data
RON SMITH A
RON SMITH B
RON SMITH C
RON SMITH D
RON SMITH H


My merge program reads data like the sample below:
First name. last name, one row, but multiple cells, each with different data.

RON SMITH A B C D H

Can someone tell me the best way to convert my existing data into the format
above?

Any help is much appreciated.

Thanks
R

--

Dave Peterson

How do I do this.. Or can I?
 

As long as you don't mind the result table to be "Name Sorted" - you may try
the my suggestion in the attached link:
http://img37.imageshack.us/img37/3514/nonameeb.png
*** Regarding the amount of data - you only have to change the references
1:9 to what ever you have there and copy-dragging the formulas...
Micky


"Roman" wrote:

That works great. I tried it out , but question for you. How do i use the
same formula for 1500 different names and 3700 rows of data?


"מיכאל (מיקי) אבידן" wrote:

It may be shorten but this is what I "found in my sleeve":
http://img525.imageshack.us/img525/6234/nonamek.png
Micky


"Roman" wrote:

I have data that looks like the example below:
fistname, last name, multiple rows, each with different data
RON SMITH A
RON SMITH B
RON SMITH C
RON SMITH D
RON SMITH H


My merge program reads data like the sample below:
First name. last name, one row, but multiple cells, each with different data.

RON SMITH A B C D H

Can someone tell me the best way to convert my existing data into the format
above?

Any help is much appreciated.

Thanks
R

How do I do this.. Or can I?
 

Is it only on my computer that the "A" for RON is missing in the Macro result
table ?
Micky

"Dave Peterson" wrote:

You could use a macro.

If you want to try...

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim TopCell As Range
Dim BotCell As Range

Set wks = Worksheets("Sheet1")

With wks
'the code adds headers!
.Range("a1").Resize(1, 4).Value _
= Array("h1", "h2", "h3", "h4")
FirstRow = 2 'avoid the header row
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

Set TopCell = Nothing 'to start process

For iRow = FirstRow To LastRow + 1 'to get last group
If .Cells(iRow, "A").Value = .Cells(iRow - 1, "A").Value _
And .Cells(iRow, "B").Value = .Cells(iRow - 1, "B").Value Then
'do nothing, still in the current group
Else
Set BotCell = .Cells(iRow - 1, "c")
If TopCell Is Nothing Then
'do nothing, first record
Else
'start of new group, so
'do copy|paste special|transpose
'of old group
.Range(TopCell, BotCell).Copy
TopCell.Offset(0, 1).PasteSpecial Transpose:=True
End If
'get ready for next time
Set TopCell = .Cells(iRow, "c")
Set BotCell = .Cells(iRow, "c")
End If
Next iRow

'uncomment these when your tests look ok.
'remove column C
.Columns(3).Delete

'get rid of the empty rows based on column C
.Columns(3).Cells.SpecialCells(xlCellTypeBlanks).E ntireRow.Delete

'get rid of the added headers
.Rows(1).Delete

End With

End Sub

If you're new to macros:

Debra Dalgleish has some notes how to implement macros he
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

Roman wrote:

I have data that looks like the example below:
fistname, last name, multiple rows, each with different data
RON SMITH A
RON SMITH B
RON SMITH C
RON SMITH D
RON SMITH H


My merge program reads data like the sample below:
First name. last name, one row, but multiple cells, each with different data.

RON SMITH A B C D H

Can someone tell me the best way to convert my existing data into the format
above?

Any help is much appreciated.

Thanks
R

--

Dave Peterson
.

How do I do this.. Or can I?
 

The code doesn't touch column A. It looks at it, but doesn't change anything.

I'd guess that something else happened.

????? (????) ????? wrote:

Is it only on my computer that the "A" for RON is missing in the Macro result
table ?
Micky

"Dave Peterson" wrote:

You could use a macro.

If you want to try...

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim TopCell As Range
Dim BotCell As Range

Set wks = Worksheets("Sheet1")

With wks
'the code adds headers!
.Range("a1").Resize(1, 4).Value _
= Array("h1", "h2", "h3", "h4")
FirstRow = 2 'avoid the header row
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

Set TopCell = Nothing 'to start process

For iRow = FirstRow To LastRow + 1 'to get last group
If .Cells(iRow, "A").Value = .Cells(iRow - 1, "A").Value _
And .Cells(iRow, "B").Value = .Cells(iRow - 1, "B").Value Then
'do nothing, still in the current group
Else
Set BotCell = .Cells(iRow - 1, "c")
If TopCell Is Nothing Then
'do nothing, first record
Else
'start of new group, so
'do copy|paste special|transpose
'of old group
.Range(TopCell, BotCell).Copy
TopCell.Offset(0, 1).PasteSpecial Transpose:=True
End If
'get ready for next time
Set TopCell = .Cells(iRow, "c")
Set BotCell = .Cells(iRow, "c")
End If
Next iRow

'uncomment these when your tests look ok.
'remove column C
.Columns(3).Delete

'get rid of the empty rows based on column C
.Columns(3).Cells.SpecialCells(xlCellTypeBlanks).E ntireRow.Delete

'get rid of the added headers
.Rows(1).Delete

End With

End Sub

If you're new to macros:

Debra Dalgleish has some notes how to implement macros he
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

Roman wrote:

I have data that looks like the example below:
fistname, last name, multiple rows, each with different data
RON SMITH A
RON SMITH B
RON SMITH C
RON SMITH D
RON SMITH H


My merge program reads data like the sample below:
First name. last name, one row, but multiple cells, each with different data.

RON SMITH A B C D H

Can someone tell me the best way to convert my existing data into the format
above?

Any help is much appreciated.

Thanks
R

--

Dave Peterson
.


--

Dave Peterson

How do I do this.. Or can I?
 

Dave,

This worked great. thanks for the help. I appreciate it very much.

R
"Dave Peterson" wrote:

You could use a macro.

If you want to try...

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim TopCell As Range
Dim BotCell As Range

Set wks = Worksheets("Sheet1")

With wks
'the code adds headers!
.Range("a1").Resize(1, 4).Value _
= Array("h1", "h2", "h3", "h4")
FirstRow = 2 'avoid the header row
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

Set TopCell = Nothing 'to start process

For iRow = FirstRow To LastRow + 1 'to get last group
If .Cells(iRow, "A").Value = .Cells(iRow - 1, "A").Value _
And .Cells(iRow, "B").Value = .Cells(iRow - 1, "B").Value Then
'do nothing, still in the current group
Else
Set BotCell = .Cells(iRow - 1, "c")
If TopCell Is Nothing Then
'do nothing, first record
Else
'start of new group, so
'do copy|paste special|transpose
'of old group
.Range(TopCell, BotCell).Copy
TopCell.Offset(0, 1).PasteSpecial Transpose:=True
End If
'get ready for next time
Set TopCell = .Cells(iRow, "c")
Set BotCell = .Cells(iRow, "c")
End If
Next iRow

'uncomment these when your tests look ok.
'remove column C
.Columns(3).Delete

'get rid of the empty rows based on column C
.Columns(3).Cells.SpecialCells(xlCellTypeBlanks).E ntireRow.Delete

'get rid of the added headers
.Rows(1).Delete

End With

End Sub

If you're new to macros:

Debra Dalgleish has some notes how to implement macros he
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

Roman wrote:

I have data that looks like the example below:
fistname, last name, multiple rows, each with different data
RON SMITH A
RON SMITH B
RON SMITH C
RON SMITH D
RON SMITH H


My merge program reads data like the sample below:
First name. last name, one row, but multiple cells, each with different data.

RON SMITH A B C D H

Can someone tell me the best way to convert my existing data into the format
above?

Any help is much appreciated.

Thanks
R

--

Dave Peterson
.

How do I do this.. Or can I?
 

thanks for the help and input.

I appreciate it very much.

R

"מיכאל (מיקי) אבידן" wrote:

Is it only on my computer that the "A" for RON is missing in the Macro result
table ?
Micky

"Dave Peterson" wrote:

You could use a macro.

If you want to try...

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim TopCell As Range
Dim BotCell As Range

Set wks = Worksheets("Sheet1")

With wks
'the code adds headers!
.Range("a1").Resize(1, 4).Value _
= Array("h1", "h2", "h3", "h4")
FirstRow = 2 'avoid the header row
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

Set TopCell = Nothing 'to start process

For iRow = FirstRow To LastRow + 1 'to get last group
If .Cells(iRow, "A").Value = .Cells(iRow - 1, "A").Value _
And .Cells(iRow, "B").Value = .Cells(iRow - 1, "B").Value Then
'do nothing, still in the current group
Else
Set BotCell = .Cells(iRow - 1, "c")
If TopCell Is Nothing Then
'do nothing, first record
Else
'start of new group, so
'do copy|paste special|transpose
'of old group
.Range(TopCell, BotCell).Copy
TopCell.Offset(0, 1).PasteSpecial Transpose:=True
End If
'get ready for next time
Set TopCell = .Cells(iRow, "c")
Set BotCell = .Cells(iRow, "c")
End If
Next iRow

'uncomment these when your tests look ok.
'remove column C
.Columns(3).Delete

'get rid of the empty rows based on column C
.Columns(3).Cells.SpecialCells(xlCellTypeBlanks).E ntireRow.Delete

'get rid of the added headers
.Rows(1).Delete

End With

End Sub

If you're new to macros:

Debra Dalgleish has some notes how to implement macros he
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

Roman wrote:

I have data that looks like the example below:
fistname, last name, multiple rows, each with different data
RON SMITH A
RON SMITH B
RON SMITH C
RON SMITH D
RON SMITH H


My merge program reads data like the sample below:
First name. last name, one row, but multiple cells, each with different data.

RON SMITH A B C D H

Can someone tell me the best way to convert my existing data into the format
above?

Any help is much appreciated.

Thanks
R

--

Dave Peterson
.