This is probably simple to fix

[benplace]

I have a spreadsheet (Rather Large) that comes from a report I run eac
month That I need to sort various ways. The problem is the repor
layout.
It is layed out like this:

Name
data1 data2
data3
data1 data2
data3
name2
data1 data2
data3
data1 data2
data3
data1 data2
data3
data1 data2
data3
name3
data1 data2
data3
data1 data2
data3

There is no way of knowing how many data lines there are for each name
What I have been doing is highlighting the name and grabbing the blac
bar and pulling it down so the name is on each line. This takes me
hours sometimes.
Anyone got an automated idea for doing this

 

[Guest]

1st: Insert another column before A, use this to number from 1 - xxx.. (***
lets you return to the correct sorting order if you accidently get screwed up)

2nd: Use the subtotal function.. instead of a TOTAL, you can do a count,
average, etc..

HTH

 

[Dave Peterson]

Is there any indication at all on the cells that contain the name.

I don't see anything in your sample data.

Are you sure your text file looks the same way--sometimes importing it will
squeeze out those leading spaces?

 

[benplace]

The problem is I want to do sorting and make sure the data for eac
person stays attached to that person. However the persons name isnt o
each data row, but above the data

 

[benplace]

I want to be able to reference data1 and know it belongs to tha
person.
One thing that may help is that the name field is in italics. Is ther
a way to check column a for italics, then repeat the name until you ru
into another italics name?
Maybe a dumb question.
If I can figure this out it will save 3 hours of work a day.

 

[Dave Peterson]

Ahhh. If the cell in column A is italics, then that means it's a name?

But before we continue, you wrote this in your initial post.

What I have been doing is highlighting the name and grabbing the black
bar and pulling it down so the name is on each line.

But your data looked like you had names and data intermingled in column A.

Does your data really look like:

colA ColB ColC
Name data1 data2
data3

So if you could verify what your input data looks like and what you want it to
look like when you're done, it would make it easier than guessing.

I think it would be easiest to make your data look like:

ColA ColB
Name Data1
Name Data2
Name Data3

But you may not agree.

 

[benplace]

Thought of this in the middle of the night. lol
I dont care about data in column A
If I had a macro the read cell A1, checked if it contained a number o
was blank. If this was not true, meaning it was a name, it would cop
that name, check the next cell A2 and if it were blank or numeric
paste the name, then A3 and so on.
When it hits a name, it would then copy that name, and go to the nex
cell down.
It could repeat this until it hit 20 straight blank cells. Then end.
I could run the macro, come back and go to the bottom and remove the 2
blanks.
I dont think this is simple anymore. lo

 

[Dave Peterson]

So you're just looking at column A.

If the cell contains numbers or is blank then replace it with the value from the
cell above?

You could loop through the cells looking for this kind of stuff and process it
row by row--or you could let excel do some of the work.

Manually, I'd do this assuming that there are no formulas in column A.

Select column A
edit|goto|special
click constants and check only Numbers
click ok
hit the delete key (to clear those cells)

Then select column A
edit|goto|special
click blanks
type = and hit the uparrow
then hit ctrl-enter
You've now filled each blank cell with a formula that points to the cell above.

Now convert it to values
select column A
edit|copy
edit|paste special|values

In code...

Option Explicit
Sub testme01()

Dim wks As Worksheet
Set wks = ActiveSheet

With wks
If IsEmpty(.Range("a1")) Then
MsgBox "Please put something in A1!"
Exit Sub
End If
'the "on error resume next" is used
'just in case there are no numbers in that Column
'and just in case there are no blanks

On Error Resume Next

.Range("a:a").Cells _
.SpecialCells(xlCellTypeConstants, xlNumbers).ClearContents

.Range("a:a").Cells.SpecialCells(xlCellTypeBlanks).FormulaR1C1 _
= "=r[-1]c"

On Error GoTo 0

With .Range("a:a")
.Value = .Value
End With

End With
End Sub

 

[benplace]

You are a godsend! This worked perfect! I am also thinking now of
other areas I can use the goto special.
Again Thank you!

 

[Dave Peterson]

There are lots of things hidden under that edit|goto|special dialog. In fact, I
find it quicker to do this kind of stuff manually than to write the macro (or
find it and execute it).

(well, if it's not part of a larger mechanized procedure.)