| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format excel data
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Format excel data
Debra Dalgleish has a manual method (quicker) and code at:
http://contextures.com/xlDataEntry02.html Paramasivan wrote: 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 -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Format excel data
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Format excel data
It looks like the column formatting got goofed up, can you resubmit?
Thanks, RC- "Paramasivan" wrote in message oups.com... 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Format excel data
Manually............
Select columns and F5SpecialBlanksOK 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)valuesokesc 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 On 19 Jan 2006 09:16:46 -0800, "Paramasivan" wrote: 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 Gord Dibben MS Excel MVP |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Importing Data from Excel when data not in same format | Excel Discussion (Misc queries) | |||
| format data displayed on Excel data entry form | Setting up and Configuration of Excel | |||
| Excel data file format | Excel Discussion (Misc queries) | |||
| How do I put Excel data into a US map format? | Charts and Charting in Excel | |||
| Excel Data Format | Excel Programming | |||
Hybrid Mode
