Excel Row/Column Merge

R

rition

Hello

I have searched Excel help but I cannot find a way of merging several
rows in an excel spreadsheet.

I was given 15 separate sheets with group lists, some of the people on
group 1 also take part in up to four other groups.

My spreadsheet is set up as follows

Column A contains first name
Column B Surname
Column C Section
Then Columns D - S my various groups

So Tom Jones may have appear on rows 1 - 5 with one entry in each of
columns D F G K M

I now need to merge the names so that Tom Jones instead of having five
rows only appears on one row and transfer all the entries for his
groups to that one row.

I have been doing this manually using the filter data on the surname.

Can I do this automatically please?

TIA
 
B

Billy Liddel

I was given 15 separate sheets with group lists, some of the people on
group 1 also take part in up to four other groups.

My spreadsheet is set up as follows

Column A contains first name
Column B Surname
Column C Section
Then Columns D - S my various groups

So Tom Jones may have appear on rows 1 - 5 with one entry in each of
columns D F G K M
Click to expand...
Hi

You can do this with a macro. I can't think how to do this in one go yet but
this macro will add to the person first line in stages so keep running it
until the the number of changes stays constant. You'll be told this.

Sub t()
Dim i As Long, nr As Long, col As Integer, nc As Integer
Dim c, d As String, tmp, count As Long, inf
Range("A1").Select
nr = Range("A1").CurrentRegion.Rows.count
nc = Range("A1").CurrentRegion.Columns.count
count = 0
'copy data to top row of each person
For i = 2 To nr
tmp = i
c = Cells(i, 1) & Cells(i, 2) & Cells(i, 3)
d = Cells(i + 1, 1) & Cells(i + 1, 2) & Cells(i + 1, 3)

If c = d Then
For j = 4 To nc
If IsEmpty(Cells(tmp, j)) Then
Cells(tmp, j) = Cells(i + 1, j)
count = count + 1
End If
Next j
End If
Next i
inf = MsgBox("Changes Made: " & count, vbDefaultButton1, "Changes made to
sheet")
End Sub

When you are happy use the next sub to delete the dupilcate rows. Only once
will do.

Sub DelDupes()
Dim i As Long, nr As Long, col As Integer, nc As Integer
Dim c, d As String, tmp
Range("A1").Select
nr = Range("A1").CurrentRegion.Rows.count
nc = Range("A1").CurrentRegion.Columns.count
'copy data to top row of each person
For i = nr To 2 Step -1
tmp = i
c = Cells(i, 1) & Cells(i, 2) & Cells(i, 3)
d = Cells(i - 1, 1) & Cells(i - 1, 2) & Cells(i - 1, 3)
If c = d Then
Range(Cells(i, 1), Cells(i, nc)).Delete
End If
Next i
End Sub

OPen the VB Editor (ALT + F11), >Insert >Module and copy the code into the
Module. the code will work on the active sheet in the workbook. Place the
cursor into Sub T and press F5 to run the code.
Do not run the DelDupes until you are happy with the results. Perhaps you
can copy the workbook and use this.

Regards
Peter
 
R

rition

You can do this with a macro. I can't think how to do this in one go yet but
this macro will add to the person first line in stages so keep running it
until the the number of changes stays constant. You'll be told this.

Sub t()
Dim i As Long, nr As Long, col As Integer, nc As Integer
Dim c, d As String, tmp, count As Long, inf
Range("A1").Select
nr = Range("A1").CurrentRegion.Rows.count
nc = Range("A1").CurrentRegion.Columns.count
count = 0
'copy data to top row of each person
For i = 2 To nr
tmp = i
c = Cells(i, 1) & Cells(i, 2) & Cells(i, 3)
d = Cells(i + 1, 1) & Cells(i + 1, 2) & Cells(i + 1, 3)

If c = d Then
For j = 4 To nc
If IsEmpty(Cells(tmp, j)) Then
Cells(tmp, j) = Cells(i + 1, j)
count = count + 1
End If
Next j
End If
Next i
inf = MsgBox("Changes Made: " & count, vbDefaultButton1, "Changes made to
sheet")
End Sub

When you are happy use the next sub to delete the dupilcate rows. Only once
will do.

Sub DelDupes()
Dim i As Long, nr As Long, col As Integer, nc As Integer
Dim c, d As String, tmp
Range("A1").Select
nr = Range("A1").CurrentRegion.Rows.count
nc = Range("A1").CurrentRegion.Columns.count
'copy data to top row of each person
For i = nr To 2 Step -1
tmp = i
c = Cells(i, 1) & Cells(i, 2) & Cells(i, 3)
d = Cells(i - 1, 1) & Cells(i - 1, 2) & Cells(i - 1, 3)
If c = d Then
Range(Cells(i, 1), Cells(i, nc)).Delete
End If
Next i
End Sub

OPen the VB Editor (ALT + F11), >Insert >Module and copy the code into the
Module. the code will work on the active sheet in the workbook. Place the
cursor into Sub T and press F5 to run the code.
Do not run the DelDupes until you are happy with the results. Perhaps you
can copy the workbook and use this.

Regards
Peter
Click to expand...

Hello Peter

Thank you for taking the time to, reply I wish I could say that I
understood all your hard work.

I have run this twice, each time the first formula runs to 1067
changes and then remains static.

Then I run the second macro.

This merges some of the duplicated names but not all of them. The
lines that are not merged are identical.

I don't know where I am going wrong.
 
R

Ron Coderre

Perhaps this approach?

Example:
A1:G11 contains this list, where (blank) means a blank cell:

First Last Grp_1 Grp_2 Grp_3 Grp_4 Grp_5
Arnold Baggins SignedUp (blank) (blank) (blank) (blank)
Arnold Baggins (blank) (blank) (blank) (blank) (blank)
Arnold Baggins (blank) (blank) (blank) (blank) (blank)
Arnold Baggins (blank) (blank) (blank) SignedUp (blank)
Arnold Baggins (blank) (blank) (blank) (blank) SignedUp
Oprah Lohan SignedUp (blank) (blank) (blank) (blank)
Oprah Lohan (blank) SignedUp (blank) (blank) (blank)
Oprah Lohan (blank) (blank) SignedUp (blank) (blank)
Oprah Lohan (blank) (blank) (blank) SignedUp (blank)
Oprah Lohan (blank) (blank) (blank) (blank) SignedUp

Select A1:G11

From the Excel main menu:
<Data><Pivot Table>
Use: Excel
Select your data if not already selected
Click the [Layout] button

ROW:
Drag the "First" field here
Drag the "Last" field here

DATA:
Drag the Grp_1, Grp_2, Grp_3, Grp_4, and Grp_5 fields here.
They'll all list as Count of Grp_1, Count of Grp_2, etc
Click [OK]

Select where you want the Pivot Table...and click the [Finish] button.

Not quite there yet (as I'm sure you noticed)

Finally....Drag the "DATA" field over the "Total" field...and release it.

Now the Pivot Table looks like this (I abbreviated to avoid text wrap):

First Last Ct_Grp1 Ct_Grp2 Ct_Grp3 Ct_Grp4 Ct of Grp5
Arnold Baggins 1 1 1
Oprah Lohan 1 1 1 1 1

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP
 
B

Billy Liddel

Hi

Yes you have to keep running it and there is a lot of redundancy in the
code. I ran my test data eight times on 11 rows of code to complete the
copying. Sorry it did not work completely for you.

Still I've sorted out how to do it automatically with another loop. Sub T
will run until it has finshed the duplications then calls the deletedupes. If
you want to see the result before the duplicates are finished put an
apostrophe before the Call DelDupes.

Sub t()
Dim i As Long, nr As Long, col As Integer, nc As Integer
Dim c, d As String, tmp, count As Long, max As Long
Range("A1").Select
nr = Range("A1").CurrentRegion.Rows.count
nc = Range("A1").CurrentRegion.Columns.count
count = 0
'copy data to top row of each person
Cells(1, nc + 2) = nr
max = (nr - 1) * (nc - 3)
For count = 1 To max
For i = 2 To nr

c = Cells(i, 1) & Cells(i, 2) & Cells(i, 3)
d = Cells(i + 1, 1) & Cells(i + 1, 2) & Cells(i + 1, 3)
If c = d Then
For j = 4 To nc
If IsEmpty(Cells(i, j)) And Not IsEmpty(Cells(i + 1, j)) Then
Cells(i, j) = Cells(i + 1, j)
Else
'nothing
End If
Next j
End If
Next i
Next count
'rem next line to see the dupes
Call DelDupes
End Sub

Sub DelDupes()
Dim i As Long, nr As Long, col As Integer, nc As Integer
Dim c, d As String, tmp
Range("A1").Select
nr = Range("A1").CurrentRegion.Rows.count
nc = Range("A1").CurrentRegion.Columns.count
'copy data to top row of each person
For i = nr To 2 Step -1
tmp = i
c = Cells(i, 1) & Cells(i, 2) & Cells(i, 3)
d = Cells(i - 1, 1) & Cells(i - 1, 2) & Cells(i - 1, 3)
If c = d Then
Range(Cells(i, 1), Cells(i, nc)).Delete
End If
Next i
End Sub


Best of luck
Peter
 
B

Billy Liddel

Very neat Ron, Its a pity you can't change the field titles though?

Regards
Peter

Ron Coderre said:
Perhaps this approach?

Example:
A1:G11 contains this list, where (blank) means a blank cell:

First Last Grp_1 Grp_2 Grp_3 Grp_4 Grp_5
Arnold Baggins SignedUp (blank) (blank) (blank) (blank)
Arnold Baggins (blank) (blank) (blank) (blank) (blank)
Arnold Baggins (blank) (blank) (blank) (blank) (blank)
Arnold Baggins (blank) (blank) (blank) SignedUp (blank)
Arnold Baggins (blank) (blank) (blank) (blank) SignedUp
Oprah Lohan SignedUp (blank) (blank) (blank) (blank)
Oprah Lohan (blank) SignedUp (blank) (blank) (blank)
Oprah Lohan (blank) (blank) SignedUp (blank) (blank)
Oprah Lohan (blank) (blank) (blank) SignedUp (blank)
Oprah Lohan (blank) (blank) (blank) (blank) SignedUp

Select A1:G11

From the Excel main menu:
<Data><Pivot Table>
Use: Excel
Select your data if not already selected
Click the [Layout] button

ROW:
Drag the "First" field here
Drag the "Last" field here

DATA:
Drag the Grp_1, Grp_2, Grp_3, Grp_4, and Grp_5 fields here.
They'll all list as Count of Grp_1, Count of Grp_2, etc
Click [OK]

Select where you want the Pivot Table...and click the [Finish] button.

Not quite there yet (as I'm sure you noticed)

Finally....Drag the "DATA" field over the "Total" field...and release it.

Now the Pivot Table looks like this (I abbreviated to avoid text wrap):

First Last Ct_Grp1 Ct_Grp2 Ct_Grp3 Ct_Grp4 Ct of Grp5
Arnold Baggins 1 1 1
Oprah Lohan 1 1 1 1 1

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP


[email protected] said:
Hello

I have searched Excel help but I cannot find a way of merging several
rows in an excel spreadsheet.

I was given 15 separate sheets with group lists, some of the people on
group 1 also take part in up to four other groups.

My spreadsheet is set up as follows

Column A contains first name
Column B Surname
Column C Section
Then Columns D - S my various groups

So Tom Jones may have appear on rows 1 - 5 with one entry in each of
columns D F G K M

I now need to merge the names so that Tom Jones instead of having five
rows only appears on one row and transfer all the entries for his
groups to that one row.

I have been doing this manually using the filter data on the surname.

Can I do this automatically please?

TIA
Click to expand...
Click to expand...
 
R

Ron Coderre

Thanks, Billy

Actually....you CAN change the any of the Col or Row titles.

In my original post, I figured that if the method was not acceptable, no
point writing a dissertation on how to finesse it. :)

INSTRUCTIONS:
In the Layout window of the Pivot Table Wizard
Dbl-Click on a field in the DATA, ROW, or COLUMN section
Name: (enter whatever you like)
Click [OK]
....repeat for each field...

Now the example I posted could look like this:

FirstName LastName Grp 1 Grp 2 Grp 3 Grp 4 Grp 5
Arnold Baggins 1 1 1
Oprah Lohan 1 1 1 1 1


( Hmmm..that wasn't much of a dissertation. )

***********
Regards,
Ron

XL2002, WinXP


Billy Liddel said:
Very neat Ron, Its a pity you can't change the field titles though?

Regards
Peter

Ron Coderre said:
Perhaps this approach?

Example:
A1:G11 contains this list, where (blank) means a blank cell:

First Last Grp_1 Grp_2 Grp_3 Grp_4 Grp_5
Arnold Baggins SignedUp (blank) (blank) (blank) (blank)
Arnold Baggins (blank) (blank) (blank) (blank) (blank)
Arnold Baggins (blank) (blank) (blank) (blank) (blank)
Arnold Baggins (blank) (blank) (blank) SignedUp (blank)
Arnold Baggins (blank) (blank) (blank) (blank) SignedUp
Oprah Lohan SignedUp (blank) (blank) (blank) (blank)
Oprah Lohan (blank) SignedUp (blank) (blank) (blank)
Oprah Lohan (blank) (blank) SignedUp (blank) (blank)
Oprah Lohan (blank) (blank) (blank) SignedUp (blank)
Oprah Lohan (blank) (blank) (blank) (blank) SignedUp

Select A1:G11

From the Excel main menu:
<Data><Pivot Table>
Use: Excel
Select your data if not already selected
Click the [Layout] button

ROW:
Drag the "First" field here
Drag the "Last" field here

DATA:
Drag the Grp_1, Grp_2, Grp_3, Grp_4, and Grp_5 fields here.
They'll all list as Count of Grp_1, Count of Grp_2, etc
Click [OK]

Select where you want the Pivot Table...and click the [Finish] button.

Not quite there yet (as I'm sure you noticed)

Finally....Drag the "DATA" field over the "Total" field...and release it.

Now the Pivot Table looks like this (I abbreviated to avoid text wrap):

First Last Ct_Grp1 Ct_Grp2 Ct_Grp3 Ct_Grp4 Ct of Grp5
Arnold Baggins 1 1 1
Oprah Lohan 1 1 1 1 1

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP


[email protected] said:
Hello

I have searched Excel help but I cannot find a way of merging several
rows in an excel spreadsheet.

I was given 15 separate sheets with group lists, some of the people on
group 1 also take part in up to four other groups.

My spreadsheet is set up as follows

Column A contains first name
Column B Surname
Column C Section
Then Columns D - S my various groups

So Tom Jones may have appear on rows 1 - 5 with one entry in each of
columns D F G K M

I now need to merge the names so that Tom Jones instead of having five
rows only appears on one row and transfer all the entries for his
groups to that one row.

I have been doing this manually using the filter data on the surname.

Can I do this automatically please?

TIA
Click to expand...
Click to expand...
Click to expand...
 
B

Billy Liddel

Dissertation enough though Ron!

Great - Thanks again

Peter

Ron Coderre said:
Thanks, Billy

Actually....you CAN change the any of the Col or Row titles.

In my original post, I figured that if the method was not acceptable, no
point writing a dissertation on how to finesse it. :)

INSTRUCTIONS:
In the Layout window of the Pivot Table Wizard
Dbl-Click on a field in the DATA, ROW, or COLUMN section
Name: (enter whatever you like)
Click [OK]
...repeat for each field...

Now the example I posted could look like this:

FirstName LastName Grp 1 Grp 2 Grp 3 Grp 4 Grp 5
Arnold Baggins 1 1 1
Oprah Lohan 1 1 1 1 1


( Hmmm..that wasn't much of a dissertation. )

***********
Regards,
Ron

XL2002, WinXP


Billy Liddel said:
Very neat Ron, Its a pity you can't change the field titles though?

Regards
Peter

Ron Coderre said:
Perhaps this approach?

Example:
A1:G11 contains this list, where (blank) means a blank cell:

First Last Grp_1 Grp_2 Grp_3 Grp_4 Grp_5
Arnold Baggins SignedUp (blank) (blank) (blank) (blank)
Arnold Baggins (blank) (blank) (blank) (blank) (blank)
Arnold Baggins (blank) (blank) (blank) (blank) (blank)
Arnold Baggins (blank) (blank) (blank) SignedUp (blank)
Arnold Baggins (blank) (blank) (blank) (blank) SignedUp
Oprah Lohan SignedUp (blank) (blank) (blank) (blank)
Oprah Lohan (blank) SignedUp (blank) (blank) (blank)
Oprah Lohan (blank) (blank) SignedUp (blank) (blank)
Oprah Lohan (blank) (blank) (blank) SignedUp (blank)
Oprah Lohan (blank) (blank) (blank) (blank) SignedUp

Select A1:G11

From the Excel main menu:
<Data><Pivot Table>
Use: Excel
Select your data if not already selected
Click the [Layout] button

ROW:
Drag the "First" field here
Drag the "Last" field here

DATA:
Drag the Grp_1, Grp_2, Grp_3, Grp_4, and Grp_5 fields here.
They'll all list as Count of Grp_1, Count of Grp_2, etc
Click [OK]

Select where you want the Pivot Table...and click the [Finish] button.

Not quite there yet (as I'm sure you noticed)

Finally....Drag the "DATA" field over the "Total" field...and release it.

Now the Pivot Table looks like this (I abbreviated to avoid text wrap):

First Last Ct_Grp1 Ct_Grp2 Ct_Grp3 Ct_Grp4 Ct of Grp5
Arnold Baggins 1 1 1
Oprah Lohan 1 1 1 1 1

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP


:

Hello

I have searched Excel help but I cannot find a way of merging several
rows in an excel spreadsheet.

I was given 15 separate sheets with group lists, some of the people on
group 1 also take part in up to four other groups.

My spreadsheet is set up as follows

Column A contains first name
Column B Surname
Column C Section
Then Columns D - S my various groups

So Tom Jones may have appear on rows 1 - 5 with one entry in each of
columns D F G K M

I now need to merge the names so that Tom Jones instead of having five
rows only appears on one row and transfer all the entries for his
groups to that one row.

I have been doing this manually using the filter data on the surname.

Can I do this automatically please?

TIA
Click to expand...
Click to expand...
Click to expand...
 
R

rition

Thanks, Billy

Actually....you CAN change the any of the Col or Row titles.

In my original post, I figured that if the method was not acceptable, no
point writing a dissertation on how to finesse it. :)

INSTRUCTIONS:
In the Layout window of the Pivot Table Wizard
Dbl-Click on a field in the DATA, ROW, or COLUMN section
Name: (enter whatever you like)
Click [OK]
...repeat for each field...

Now the example I posted could look like this:

FirstName LastName Grp 1 Grp 2 Grp 3 Grp 4 Grp 5
Arnold Baggins 1 1 1
Oprah Lohan 1 1 1 1 1


( Hmmm..that wasn't much of a dissertation. )

***********
Regards,
Ron

XL2002, WinXP


Billy Liddel said:
Very neat Ron, Its a pity you can't change the field titles though?

Regards
Peter

Ron Coderre said:
Perhaps this approach?

Example:
A1:G11 contains this list, where (blank) means a blank cell:

First Last Grp_1 Grp_2 Grp_3 Grp_4 Grp_5
Arnold Baggins SignedUp (blank) (blank) (blank) (blank)
Arnold Baggins (blank) (blank) (blank) (blank) (blank)
Arnold Baggins (blank) (blank) (blank) (blank) (blank)
Arnold Baggins (blank) (blank) (blank) SignedUp (blank)
Arnold Baggins (blank) (blank) (blank) (blank) SignedUp
Oprah Lohan SignedUp (blank) (blank) (blank) (blank)
Oprah Lohan (blank) SignedUp (blank) (blank) (blank)
Oprah Lohan (blank) (blank) SignedUp (blank) (blank)
Oprah Lohan (blank) (blank) (blank) SignedUp (blank)
Oprah Lohan (blank) (blank) (blank) (blank) SignedUp

Select A1:G11

From the Excel main menu:
<Data><Pivot Table>
Use: Excel
Select your data if not already selected
Click the [Layout] button

ROW:
Drag the "First" field here
Drag the "Last" field here

DATA:
Drag the Grp_1, Grp_2, Grp_3, Grp_4, and Grp_5 fields here.
They'll all list as Count of Grp_1, Count of Grp_2, etc
Click [OK]

Select where you want the Pivot Table...and click the [Finish] button.

Not quite there yet (as I'm sure you noticed)

Finally....Drag the "DATA" field over the "Total" field...and release it.

Now the Pivot Table looks like this (I abbreviated to avoid text wrap):

First Last Ct_Grp1 Ct_Grp2 Ct_Grp3 Ct_Grp4 Ct of Grp5
Arnold Baggins 1 1 1
Oprah Lohan 1 1 1 1 1

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP
Click to expand...
Click to expand...
Click to expand...
Thank you - that is brilliant, I work in a Junior School and this will
be really useful as it is the sort of format that I work with
regularly. Currently I enter them all and then I copy them manually.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Mail Merge Help 1
Need Help with a VBA subroutine 0
Mail Merge Directory Conditional Page Break 0
Merging from an Excel file with graphics 1
MS Project Import Error Row 1 Column 5 2
Booklet in Word, Merge Directory from Excel 0
Integrating information from Word to Excel (not mail merge) 1
lookup and return column number 2

Top