Merge all unique values into one in a required column

[acsishere]

Dear Friends,

I am having around 45000 records / 38 columns in my sheet. Other than the primary key column, almost all columns have some repeated (or rather say duplicate) values. When I required the report, the duplicate values are also appearing. To avoid this, I would require a macro to do like this:
(of course, I need to merge only one column for each report).
column A consist of
AA / DATA / DATA
AA / DATA / DATA
AA / DATA / DATA
AB / DATA / DATA
AB / DATA / DATA
AB / DATA / DATA
AC / DATA / DATA
AC / DATA / DATA
AC / DATA / DATA
AC / DATA / DATA
.... & like this.

Now I would like to merge all AA with the first appearance of the concerned row. (The table is sorted according to col A). Then, AB should be merged with the first appearance column. So that, the report will look like this:
AA / DATA / DATA
/ DATA / DATA
/ DATA / DATA
AB / DATA / DATA
/ DATA / DATA....
Please help me to solve this problem.

Any help is highly appreciated.

acsishere.

[Simon Lloyd]

Sorry but your post makes little sense!, you want to merge columns or remove duplicates?, you only want to do one column at a time or all?, you really need to be a bit clearer and post a sample workbook!

[Bob Phillips]

Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = LastRow To 2 Step -1

If .Cells(i, "A").Value = .Cells(i - 1, "A").Value Then

.Cells(i, "B").Resize(, 250).Copy .Cells(i - 1, "D")
.Rows(i).Delete
End If
Next i

End With

End Sub

[Simon Lloyd]

Lol, guess i was wrong! someone did make sense of it. Bob stop showing off! ;)

[Bob Phillips]

It's a guess Simon. I may be wrong, but I thought I got it.

[Simon Lloyd]

...... I may be wrong, but .......rarely!

[acsishere]

Thanks, Thanks a lot for your code.

But I need it in a different way. I attached a sample workbook in which I explained the requirement.

I humbly request your kind help.

Thanks in advance.

acsishere.

[Bob Phillips]

I would just use conditional formatting.

Select A2:A18
Menu Fomat>Conditional Formatting
Change Condition 1 to Formula Is
Add a formula of =A2=A1
Click the Format button
Select the Font tab
Pick White from the Color dropdown
exit out

[acsishere]

Yes, it works. But I need to merge the cells so that the border and other formatting can be done with ease.

Any solution.

Thanks in advance.

acsishere.

[Bob Phillips]

Sorry, I don't agree with merged cells, they create too many problems, so I offer no help there.

[acsishere]

Ok, Mr.XLD,

Thanks for the alternate solution. I too not to merge cells. But as my boss is required in that format - (it is the end report), I have no option. I think I disturbed you.

Any way, thanks a lot.

acsishere.

[Bob Phillips]

I can't leave you dangling if the boss is pressurising you (and you're Indian and my daughter is visiting India in a couple of months <G>)



Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long
Dim StartRow As Long

Application.DisplayAlerts = False

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
StartRow = 2
For i = 3 To LastRow + 1

If .Cells(i, "A").Value <> .Cells(i - 1, "A").Value Then

.Cells(StartRow, "A").Resize(i - StartRow).Merge
.Cells(StartRow, "A").VerticalAlignment = xlTop
StartRow = i
End If
Next i

End With

Application.DisplayAlerts = True

End Sub

[Bob Phillips]

BTW you can do everything you want without merged cells, they don't give you anything special.

What do you think you can do with merged cells that you couldn't otherwise.

[acsishere]

Simply superb! You are great, great, great. Thanks a lot for understanding my pressure. You did it Exactly.

How did you observed that I am a Indian. However, I welcome your daughter to India, which is rich in cultural, hospitality, customs, etc.

Happy journey & Most welcome.

Thanks a lot. acsishere.

[Bob Phillips]

Because you are displaying the Indian flag <G> ... a bit of a giveaway.

I am sure my daughter will enjoy your country, I might even make it myself one day.