Cell alignment

  • Thread starter Thread starter Tom Walat
  • Start date Start date
T

Tom Walat

Using Excel 2000
In the sample chart below, I'm trying to get the same text in
each alternating row to line up under the same column. I'm wondering
how to go about this. Is there a way to define a header row and have a
macro go through the entire list and, using that header row as a
reference, shift the cells so they will line up properly?
For example, if I use Row 1 has my "header row," how can I
force everything below it to line up accordingly -- and leave empty
cells for which there is no match?
The other twist is the dollar amounts directly below the pay
description need to shift along with their pay description.
I'm using the plus signs to separate the cells. Word-wrap is
wreaking havoc with this chart but I think this is the best I can do.
I'd appreciate any pointers to get this formatted properly.
Thanks.
- Tom

ROW 1: FIRSTNAME LASTNAME1++++REGULAR CITY++++HEALTH INSURANCE
ADJUSTMENT++++FIRE OVERTIME++++VACATION ++++SICK CITY++++PERSONAL
CITY++++HOLIDAY
FIRE++++LONGEVITY++++EMT++++DEFIBRILLATOR++++EDUCATIONAL
INCENTIVE++++UNIFORM++++WEEKEND DIFFERENTIAL

ROW 2:++++ $69,633.43++++ $300.00++++ $2,136.06++++ $8,012.99++++
$4,629.88++++ $308.66++++ $6,874.90++++ $850.00++++ $1,200.00++++
$1,171.52++++ $24,349.36++++ $1,400.00++++ $4,260.10

ROW 3: FIRSTNAME LASTNAME3++++REGULAR CITY++++HEALTH INSURANCE
ADJUSTMENT++++FIRE OVERTIME++++VACATION ++++SICK CITY++++PERSONAL
CITY++++HOLIDAY FIRE PREMIUM++++HOLIDAY
FIRE++++LONGEVITY++++UNIFORM++++WEEKEND DIFFERENTIAL ++++NON
SUPERVISORY
ROW 4:++++ $41,440.12++++ $300.00++++ $5,857.66++++ $5,071.09++++
$1,622.15++++ $540.72++++ $978.38++++ $3,269.98++++ $850.00++++
$1,400.00++++ $4,260.10++++ $2,982.07

ROW 5: FIRSTNAME LASTNAME3++++REGULAR CITY++++HEALTH INSURANCE
ADJUSTMENT++++FIRE OVERTIME++++VACATION ++++SICK CITY++++PERSONAL
CITY++++HOLIDAY FIRE PREMIUM++++HOLIDAY
FIRE++++LONGEVITY++++DEFIBRILLATOR++++EDUCATIONAL
INCENTIVE++++UNIFORM++++WEEKEND DIFFERENTIAL++++NON SUPERVISORY

ROW 6:++++ $33,486.02++++ $300.00++++ $181.85++++ $5,154.76++++
$13,473.88++++ $396.52++++ $1,304.54++++ $3,681.94++++ $850.00++++
$1,171.52++++ $1,546.41++++ $1,400.00++++ $4,686.10++++ $3,280.27

ROW 7: FIRSTNAME LASTNAME4++++REGULAR CITY++++HEALTH INSURANCE
ADJUSTMENT++++FIRE OVERTIME++++VACATION ++++SICK CITY++++PERSONAL
CITY++++HOLIDAY
FIRE++++LONGEVITY++++EMT++++DEFIBRILLATOR++++EDUCATIONAL INCENTIVE
++++UNIFORM++++WEEKEND DIFFERENTIAL ++++++++
ROW 8:++++ $62,641.71++++ $300.00++++ $3,024.84++++ $6,964.59++++
$6,384.18++++ $870.57++++ $5,738.50++++ $850.00++++ $1,200.00++++
$1,171.52++++ $11,317.52++++ $1,400.00++++ $4,686.10

ROW 9: FIRSTNAME LASTNAME5++++REGULAR CITY++++HEALTH INSURANCE
ADJUSTMENT++++FIRE OVERTIME++++OUT OF GRADE OVERTIME++++VACATION
++++SICK CITY++++PERSONAL CITY++++HOLIDAY FIRE PREMIUM++++HOLIDAY
FIRE++++LONGEVITY++++EMT++++DEFIBRILLATOR++++OUTSIDE
DETAIL++++EDUCATIONAL INCENTIVE++++UNIFORM++++WEEKEND DIFFERENTIAL
++++NON SUPERVISORY++++OUT OF GRADE
ROW
10:++++$42,871.37++++$300.00++++$4,751.30++++$202.54++++$4,721.29++++$720.95++++$360.47++++$969.84++++$3,347.22++++$850.00++++$1,200.00++++$1,171.52++++$1,382.63++++$1,405.83++++$1,400.00++++$4,260.10++++$2,982.07++++$964.74
 
Have you tried the Data > Text to Columns... menu option?

Tom Walat said:
Using Excel 2000
In the sample chart below, I'm trying to get the same text in
each alternating row to line up under the same column. I'm wondering
how to go about this. Is there a way to define a header row and have a
macro go through the entire list and, using that header row as a
reference, shift the cells so they will line up properly?
For example, if I use Row 1 has my "header row," how can I
force everything below it to line up accordingly -- and leave empty
cells for which there is no match?
The other twist is the dollar amounts directly below the pay
description need to shift along with their pay description.
I'm using the plus signs to separate the cells. Word-wrap is
wreaking havoc with this chart but I think this is the best I can do.
I'd appreciate any pointers to get this formatted properly.
Thanks.
- Tom

ROW 1: FIRSTNAME LASTNAME1++++REGULAR CITY++++HEALTH INSURANCE
ADJUSTMENT++++FIRE OVERTIME++++VACATION ++++SICK CITY++++PERSONAL
CITY++++HOLIDAY
FIRE++++LONGEVITY++++EMT++++DEFIBRILLATOR++++EDUCATIONAL
INCENTIVE++++UNIFORM++++WEEKEND DIFFERENTIAL

ROW 2:++++ $69,633.43++++ $300.00++++ $2,136.06++++ $8,012.99++++
$4,629.88++++ $308.66++++ $6,874.90++++ $850.00++++ $1,200.00++++
$1,171.52++++ $24,349.36++++ $1,400.00++++ $4,260.10

ROW 3: FIRSTNAME LASTNAME3++++REGULAR CITY++++HEALTH INSURANCE
ADJUSTMENT++++FIRE OVERTIME++++VACATION ++++SICK CITY++++PERSONAL
CITY++++HOLIDAY FIRE PREMIUM++++HOLIDAY
FIRE++++LONGEVITY++++UNIFORM++++WEEKEND DIFFERENTIAL ++++NON
SUPERVISORY
ROW 4:++++ $41,440.12++++ $300.00++++ $5,857.66++++ $5,071.09++++
$1,622.15++++ $540.72++++ $978.38++++ $3,269.98++++ $850.00++++
$1,400.00++++ $4,260.10++++ $2,982.07

ROW 5: FIRSTNAME LASTNAME3++++REGULAR CITY++++HEALTH INSURANCE
ADJUSTMENT++++FIRE OVERTIME++++VACATION ++++SICK CITY++++PERSONAL
CITY++++HOLIDAY FIRE PREMIUM++++HOLIDAY
FIRE++++LONGEVITY++++DEFIBRILLATOR++++EDUCATIONAL
INCENTIVE++++UNIFORM++++WEEKEND DIFFERENTIAL++++NON SUPERVISORY

ROW 6:++++ $33,486.02++++ $300.00++++ $181.85++++ $5,154.76++++
$13,473.88++++ $396.52++++ $1,304.54++++ $3,681.94++++ $850.00++++
$1,171.52++++ $1,546.41++++ $1,400.00++++ $4,686.10++++ $3,280.27

ROW 7: FIRSTNAME LASTNAME4++++REGULAR CITY++++HEALTH INSURANCE
ADJUSTMENT++++FIRE OVERTIME++++VACATION ++++SICK CITY++++PERSONAL
CITY++++HOLIDAY
FIRE++++LONGEVITY++++EMT++++DEFIBRILLATOR++++EDUCATIONAL INCENTIVE
++++UNIFORM++++WEEKEND DIFFERENTIAL ++++++++
ROW 8:++++ $62,641.71++++ $300.00++++ $3,024.84++++ $6,964.59++++
$6,384.18++++ $870.57++++ $5,738.50++++ $850.00++++ $1,200.00++++
$1,171.52++++ $11,317.52++++ $1,400.00++++ $4,686.10

ROW 9: FIRSTNAME LASTNAME5++++REGULAR CITY++++HEALTH INSURANCE
ADJUSTMENT++++FIRE OVERTIME++++OUT OF GRADE OVERTIME++++VACATION
++++SICK CITY++++PERSONAL CITY++++HOLIDAY FIRE PREMIUM++++HOLIDAY
FIRE++++LONGEVITY++++EMT++++DEFIBRILLATOR++++OUTSIDE
DETAIL++++EDUCATIONAL INCENTIVE++++UNIFORM++++WEEKEND DIFFERENTIAL
++++NON SUPERVISORY++++OUT OF GRADE
ROW
Click to expand...
10:++++$42,871.37++++$300.00++++$4,751.30++++$202.54++++$4,721.29++++$720.95
++++$360.47++++$969.84++++$3,347.22++++$850.00++++$1,200.00++++$1,171.52++++
$1,382.63++++$1,405.83++++$1,400.00++++$4,260.10++++$2,982.07++++$964.74
 
Stephen:
The text and corresponding dollar amounts are already in
columns. I'm trying to get them all to line up under the same "header"
so if one person all the WEEKEND DIFFERENTIALs and NON SUPERVISORYs,
etc., will line up in the same column throughout the 400+ rows I'm
working with.
- Tom
 
Sorry, I had misread your message.

If you want to get your data from this situation:
a b c
10 20 30
a b d
40 50 60

to this:
a b c d
10 20 30
40 50 60

....
With your data on Sheet1, insert a blank Sheet2, select all of the data on
Sheet1 and run this macro:

Sub rearrange()
For Each cell In Selection
If cell.Row Mod 2 = 0 Then
Heading = cell.Offset(-1, 0).Value
For Each headercell In Worksheets("Sheet2").Range("a1:iv1")
If headercell.Value = Heading Then
Exit For
End If
If headercell.Value = "" Then
headercell.Value = Heading
Exit For
End If
Next headercell
Worksheets("Sheet2").Range("a1").Offset(cell.Row / 2,
headercell.Column - 1) = cell.Value
End If
Next cell
End Sub
 
Back
Top