EXCEL Split Merged Cell, Duplicate Content 1

[Delboy14]

Using Excel 2003

I have a table with merged cells showing the owner of items. I would like to unmerge the cells to show the owner on each row next to the item with no merged cells.
Is there any way of unmerging merged cells and adding owner name to each of the unmerged cells. I have to do this for thousands of lines so would like an automated solution, not double clicking on the box to publish for one owner at a time.

Thanks,

Example of Problem
OWNER ITEM
PEN
Derek BRUSH
PENCIL

Want it to be
OWNER ITEM
Derek PEN
Derek BRUSH
Derek PENCIL

 

[CautionMP]

Something like this?

Public Sub UnmergeAndNormalize()[green]
'* There is no pointer to a worksheet so this routine needs to be
'* placed in the code module for the worksheet you want to normalize
'* or pointers to a sheet will need to be incorporated[/green]
Dim rngCurrent As Range
Dim lngRow As Long, lngRows As Long, lngColumn As Long
Dim strValue As String[green]
'Initialize lngRow, point to first row of data[/green]
lngRow = 2[green]
'Hard code column A[/green]
lngColumn = 1
[green]
'Get the current region & determine Rows[/green]
Set rngCurrent = Cells(lngRow, lngColumn).CurrentRegion[green]
'Get the number of rows in the current region[/green]
lngRows = rngCurrent.Rows.Count
[green]
'Iterate through all the rows in the CurrentRegion[/green]
For lngRow = lngRow To lngRows
  Set rngCurrent = Cells(lngRow, lngColumn)[green]
  'Check to see if the current cell has a value in it[/green]
  If rngCurrent.Value <> "" Then[green]
    'Grab the value before UnMerge[/green]
    strValue = rngCurrent.Value[green]
    'Split the cells[/green]
    rngCurrent.UnMerge
  Else
    rngCurrent.Value = strValue
  End If
Next lngRow[green]
'Clean  up[/green]
Set rngCurrent = Nothing
End Sub

CMP

Instant programmer, just add coffee.

 

[SkipVought]

Hi,

Beings that this is the MS OFFICE forum and not the VBA forum, here's a spreadsheet solution.

Use an IF function to determine where the value exists and propogate the value down in a new column, for instance column F...
[tt]
=If(Isblank(A2),F1,A2)
[/tt]
Copy Column F

Select A1 - Edit/Paste Special - VALUES

Delete column F

VOLA!

TIP: Use Conditional Formatting to "HIDE" duplicate values by changing the Font Color to the Background Color.

Skip,
[sub]
[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine![/sub]

 

[Delboy14]

Skivought, I think the IF solution will only display the unmerged values with blanks? I think by propogate the cells, you mean select the bottom right hand corner of the cell to fill in the name for the unmerged cells below. my problem is I have thousands of rows and merged cells. Propogating will take too long, I am looking for an automated solution.

Have I understood your suggestion correctly? I did try it..

thank you for your help

 

[SkipVought]

First you must UNMERGE (Select the column and UNMERGE). I thought that was a given.

This entire spresdsheet solution can be accomplished in about 30 seconds.

Yes, it could be automated IF you plan to do it on more than one occasion.

Skip,
[sub]
[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine![/sub]

 

[Delboy14]

Thanks Skip, I will have to do it a few times so I have decided to do it using VBA (I know this was not in the original query)

Function Unmerge()
Dim i As Integer
Dim Legal_Entity As String
Dim Portfoliwner As String

i = 4

For i = 4 To 1441

If Cells(i, 3) <> "" Then
Legal_Entity = Cells(i, 3)
Else
Cells(i, 3) = Legal_Entity
End If

If Cells(i, 4) <> "" Then
Portfoliwner = Cells(i, 4)
Else
Cells(i, 4) = Portfoliwner
End If

Next i
End Function

 

[SkipVought]

BTW,

You are missing your UNMERGE method.

And will there ALWAYS be 1441 rows???



Skip,
[sub]
[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine![/sub]

 

[CautionMP]

Skip,
Automation = Macro, now play nice.

Delboy14,
Look at my original post, it addresses Skip's concern.

CMP

Instant programmer, just add coffee.

 

[Delboy14]

thanks CMP, was just writing it quick, will add the count rows method, as I will be dowloading these files daily.