Hi!

How to find repeated data and then merge and centre.

My data is from A to M.Range from A1 to M4500

Any Function Or Macro.

Thanks
Hardeep

Hi,

I think we need more detail - what do you want to merge and center, multiple
rows, multiple columns, multiple blocks of cells? You will loose the data in
all but one of the merged cells, Excel does not merge the data.

What determines if a range has repeat data - does the data in two cells
above each other need to be the same, does all the data on two adjacent rows
need to be the same, do the rows with duplicate data need to be adjacent?

--
Thanks,
Shane Devenshire


"Hardeep_kanwar" wrote:

Hi!

How to find repeated data and then merge and centre.

My data is from A to M.Range from A1 to M4500

Any Function Or Macro.

Thanks
Hardeep

A B C D
1 HR 500 200 500
2 HR 500 200 500
3 WP 1000 500 1000
4 DP 200 50 200
5 DP 200 50 200
I want like this
A B C D
1 HR 500 200 500
2
3 WP 500 200 500
4 DP 200 50 200
5

NOTE:cell A1 and A2,B1 -B2,C1-C2,D1-D2 is Merged and Centre. The data in
A1-A2,B1-B2,C1-C2,D1-D2 is same.
Or you may say that The rows are same it may goes to 3 or 4 or 7 rows are
same data. And i want to merge and centre the same data in the rows.
Merge and Centre option is on the toolbar.

Thanks

Hardeep Kanwar

"ShaneDevenshire" wrote:


Hi,

I think we need more detail - what do you want to merge and center, multiple
rows, multiple columns, multiple blocks of cells? You will loose the data in
all but one of the merged cells, Excel does not merge the data.

What determines if a range has repeat data - does the data in two cells
above each other need to be the same, does all the data on two adjacent rows
need to be the same, do the rows with duplicate data need to be adjacent?

--
Thanks,
Shane Devenshire


"Hardeep_kanwar" wrote:

Hi!

How to find repeated data and then merge and centre.

My data is from A to M.Range from A1 to M4500

Any Function Or Macro.

Thanks
Hardeep

Hardeep

Run this macro based upon contents of Column A

Sub Clear_Stuff()
'Based on code by Sandy Mann July 1st, 2007
Dim LastRow As Long
Dim x As Long
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
Application.ScreenUpdating = False

For x = LastRow To 2 Step -1
If Cells(x, 1).Value = Cells(x - 1, 1).Value Then
If Cells(x - 1, 1).Value < "" Then
If Cells(x - 1, 1).Value < "" Then
Cells(x, 1).EntireRow.ClearContents
End If
End If
End If
Next x
Application.ScreenUpdating = True
End Sub

Does not merge and center, but clears contents of duplicated row.

You don't want merge and center because only the data in the top left cell of
selected cells will remain.

i.e. if you selected A2:D2 and merged, only A2 data would remain.


Gord Dibben MS Excel MVP

On Wed, 21 May 2008 02:59:01 -0700, Hardeep_kanwar
wrote:

A B C D
1 HR 500 200 500
2 HR 500 200 500
3 WP 1000 500 1000
4 DP 200 50 200
5 DP 200 50 200
I want like this
A B C D
1 HR 500 200 500
2
3 WP 500 200 500
4 DP 200 50 200
5

NOTE:cell A1 and A2,B1 -B2,C1-C2,D1-D2 is Merged and Centre. The data in
A1-A2,B1-B2,C1-C2,D1-D2 is same.
Or you may say that The rows are same it may goes to 3 or 4 or 7 rows are
same data. And i want to merge and centre the same data in the rows.
Merge and Centre option is on the toolbar.

Thanks

Hardeep Kanwar

"ShaneDevenshire" wrote:


Hi,

I think we need more detail - what do you want to merge and center, multiple
rows, multiple columns, multiple blocks of cells? You will loose the data in
all but one of the merged cells, Excel does not merge the data.

What determines if a range has repeat data - does the data in two cells
above each other need to be the same, does all the data on two adjacent rows
need to be the same, do the rows with duplicate data need to be adjacent?

--
Thanks,
Shane Devenshire


"Hardeep_kanwar" wrote:

Hi!

How to find repeated data and then merge and centre.

My data is from A to M.Range from A1 to M4500

Any Function Or Macro.

Thanks
Hardeep

Yes, exactly i want this but i want to merge and centre a1:a2 or b1:b9
because the data is same. MY boss want not to delete repeated data but merge
and centre the same data.

"Gord Dibben" wrote:

Hardeep

Run this macro based upon contents of Column A

Sub Clear_Stuff()
'Based on code by Sandy Mann July 1st, 2007
Dim LastRow As Long
Dim x As Long
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
Application.ScreenUpdating = False

For x = LastRow To 2 Step -1
If Cells(x, 1).Value = Cells(x - 1, 1).Value Then
If Cells(x - 1, 1).Value < "" Then
If Cells(x - 1, 1).Value < "" Then
Cells(x, 1).EntireRow.ClearContents
End If
End If
End If
Next x
Application.ScreenUpdating = True
End Sub

Does not merge and center, but clears contents of duplicated row.

You don't want merge and center because only the data in the top left cell of
selected cells will remain.

i.e. if you selected A2:D2 and merged, only A2 data would remain.


Gord Dibben MS Excel MVP

On Wed, 21 May 2008 02:59:01 -0700, Hardeep_kanwar
wrote:

A B C D
1 HR 500 200 500
2 HR 500 200 500
3 WP 1000 500 1000
4 DP 200 50 200
5 DP 200 50 200
I want like this
A B C D
1 HR 500 200 500
2
3 WP 500 200 500
4 DP 200 50 200
5

NOTE:cell A1 and A2,B1 -B2,C1-C2,D1-D2 is Merged and Centre. The data in
A1-A2,B1-B2,C1-C2,D1-D2 is same.
Or you may say that The rows are same it may goes to 3 or 4 or 7 rows are
same data. And i want to merge and centre the same data in the rows.
Merge and Centre option is on the toolbar.

Thanks

Hardeep Kanwar

"ShaneDevenshire" wrote:


Hi,

I think we need more detail - what do you want to merge and center, multiple
rows, multiple columns, multiple blocks of cells? You will loose the data in
all but one of the merged cells, Excel does not merge the data.

What determines if a range has repeat data - does the data in two cells
above each other need to be the same, does all the data on two adjacent rows
need to be the same, do the rows with duplicate data need to be adjacent?

--
Thanks,
Shane Devenshire


"Hardeep_kanwar" wrote:

Hi!

How to find repeated data and then merge and centre.

My data is from A to M.Range from A1 to M4500

Any Function Or Macro.

Thanks
Hardeep

I guess I just don't understand what your boss means by "merge and center the
same data"

Merge and center A1:A2 will delete the data in A2.


Gord

On Wed, 21 May 2008 20:32:28 -0700, Hardeep_kanwar
wrote:

Yes, exactly i want this but i want to merge and centre a1:a2 or b1:b9
because the data is same. MY boss want not to delete repeated data but merge
and centre the same data.

"Gord Dibben" wrote:

Hardeep

Run this macro based upon contents of Column A

Sub Clear_Stuff()
'Based on code by Sandy Mann July 1st, 2007
Dim LastRow As Long
Dim x As Long
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
Application.ScreenUpdating = False

For x = LastRow To 2 Step -1
If Cells(x, 1).Value = Cells(x - 1, 1).Value Then
If Cells(x - 1, 1).Value < "" Then
If Cells(x - 1, 1).Value < "" Then
Cells(x, 1).EntireRow.ClearContents
End If
End If
End If
Next x
Application.ScreenUpdating = True
End Sub

Does not merge and center, but clears contents of duplicated row.

You don't want merge and center because only the data in the top left cell of
selected cells will remain.

i.e. if you selected A2:D2 and merged, only A2 data would remain.


Gord Dibben MS Excel MVP

On Wed, 21 May 2008 02:59:01 -0700, Hardeep_kanwar
wrote:

A B C D
1 HR 500 200 500
2 HR 500 200 500
3 WP 1000 500 1000
4 DP 200 50 200
5 DP 200 50 200
I want like this
A B C D
1 HR 500 200 500
2
3 WP 500 200 500
4 DP 200 50 200
5

NOTE:cell A1 and A2,B1 -B2,C1-C2,D1-D2 is Merged and Centre. The data in
A1-A2,B1-B2,C1-C2,D1-D2 is same.
Or you may say that The rows are same it may goes to 3 or 4 or 7 rows are
same data. And i want to merge and centre the same data in the rows.
Merge and Centre option is on the toolbar.

Thanks

Hardeep Kanwar

"ShaneDevenshire" wrote:


Hi,

I think we need more detail - what do you want to merge and center, multiple
rows, multiple columns, multiple blocks of cells? You will loose the data in
all but one of the merged cells, Excel does not merge the data.

What determines if a range has repeat data - does the data in two cells
above each other need to be the same, does all the data on two adjacent rows
need to be the same, do the rows with duplicate data need to be adjacent?

--
Thanks,
Shane Devenshire


"Hardeep_kanwar" wrote:

Hi!

How to find repeated data and then merge and centre.

My data is from A to M.Range from A1 to M4500

Any Function Or Macro.

Thanks
Hardeep