Format excel data

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Paramasivan - 19 Jan 2006 17:16 GMT

Hi All,

I am having the hierarchial data in the excel in the following format..
----------------------------------------------------------------------------------------------------------------------
Column A Col B Col C
Column D
----------------------------------------------------------------------------------------------------------------------
Airwick Freshmatic    TOTAL    Air Care    Czech Republic
            France
            Germany
            Great Britain
            Greece
            Hungary
            Italy
            Netherlands
            Poland
            Portugal
Airwick Mobil'Air     COMPLETE    Air Care    Italy
            Netherlands
    TOTAL    Air Care    Czech Republic
            Greece
            Hungary
            Portugal
----------------------------------------------------------------------------------------------------------------------

I need the output in the following format.. i.e the data need to be
updated from the cell
above which have the value..
----------------------------------------------------------------------------------------------------------------------
Col A Col B Col C
Column D
----------------------------------------------------------------------------------------------------------------------
Airwick Freshmatic    TOTAL    Air Care    Czech Republic
Airwick Freshmatic    TOTAL    Air Care    France
Airwick Freshmatic    TOTAL    Air Care    Germany
Airwick Freshmatic    TOTAL    Air Care    Great Britain
Airwick Freshmatic    TOTAL    Air Care    Greece
Airwick Freshmatic    TOTAL    Air Care    Hungary
Airwick Freshmatic    TOTAL    Air Care    Italy
Airwick Freshmatic    TOTAL    Air Care    Netherlands
Airwick Freshmatic    TOTAL    Air Care    Poland
Airwick Freshmatic    TOTAL    Air Care    Portugal
Airwick Mobil'Air     COMPLETE    Air Care    Italy
Airwick Mobil'Air     COMPLETE    Air Care    Netherlands
Airwick Mobil'Air     TOTAL    Air Care    Czech Republic
Airwick Mobil'Air     TOTAL    Air Care    Greece
Airwick Mobil'Air     TOTAL    Air Care    Hungary
Airwick Mobil'Air     TOTAL    Air Care    Portugal
----------------------------------------------------------------------------------------------------------------------

Can anyone please help me in writing some macro to get the desired
output.

Regards
Param

Reply to this Message

Dave Peterson - 19 Jan 2006 18:28 GMT

Debra Dalgleish has a manual method (quicker) and code at:
http://contextures.com/xlDataEntry02.html

> Hi All,
>
[quoted text clipped - 51 lines]
> Regards
> Param

Signature

Dave Peterson

Reply to this Message

Paramasivan - 20 Jan 2006 09:38 GMT

Hi Dave,

Thanks. The VBA code snippet was really helpful.
I have got the desired output.

Thanks to the other guys who have responed to my query.

Regards
Param

Reply to this Message

RC- - 19 Jan 2006 18:31 GMT

It looks like the column formatting got goofed up, can you resubmit?

Thanks,
RC-

> Hi All,
>
[quoted text clipped - 51 lines]
> Regards
> Param

Reply to this Message

Gord Dibben - 19 Jan 2006 21:23 GMT

Manually............

Select columns and F5>Special>Blanks>OK

Now in the active blank cell type an = sign.

Point to the cell above and CTRL + ENTER to fill down.

You can then copy and paste special(in place)>values>ok>esc to get rid of the
formulas.

VBA............

Sub Fill_Blanks()
'by Dave Peterson 2004-01-06
'fill blank cells in column with value above
Dim wks As Worksheet
Dim rng As Range
Dim LastRow As Long
Dim col As Long

Set wks = ActiveSheet
With wks
col = ActiveCell.Column
'or
'col = .range("b1").column

Set rng = .UsedRange 'try to reset the lastcell
LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
Set rng = Nothing
On Error Resume Next
Set rng = .Range(.Cells(2, col), .Cells(LastRow, col)) _
.Cells.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If rng Is Nothing Then
MsgBox "No blanks found"
Exit Sub
Else
rng.FormulaR1C1 = "=R[-1]C"
End If

'replace formulas with values
With .Cells(1, col).EntireColumn
.Value = .Value
End With

End With

End Sub

Gord Dibben MS Excel MVP

>Hi All,
>
[quoted text clipped - 51 lines]
>Regards
>Param

Gord Dibben MS Excel MVP

Reply to this Message