Aligning related data

  • Thread starter Thread starter gary
  • Start date Start date
G

gary

For each line beginning with 0ASMNT-NBR:
there is either one line beginning with 0PAID STATUS or one line
beginning with 0ERRB004
and none to many lines beginning with 000, 018, 028, 038, 058, 068.

How can I get, on one row:

the line beginning with 0ASMNT-nbr in one column
the line beginning with 0PAID STATUS or the line beginning with
0ERRB004 in another column
the first line beginning with 000, 018, 028, 038, 058, 068 in a third
column. (Note: if there are other lines beginning with 000, 018, 028,
038, 058, 068, each line needs to be in the thid column but on
different rows).

Here's a sample of my spreadsheet. (To clarify the above, I've added
a blank line between each "set" of data).

0ASMNT-NBR 000803511-6 2009 TRA 011-003 BILL 200943931
0000000 22,040.40 2,204.04 28.00 24,272.44
0681378 3.62 .36 3.98
0681864 9.50 .94 10.44
0682617 124.32 12.42 136.74
0PAID STATUS PAID 05/26/2011

0ASMNT-NBR 000803512-7 2008 TRA 011-003 BILL 200847567
0000000 28,038.50 2,803.84 31.00 30,873.34
0681864 38.34 3.82 42.16
0PAID STATUS NOT PAID

0ASMNT-NBR 000803514-9 2003 TRA 017-018 BILL 200357074
0000000 1,534.00 153.40 20.00 1,707.40
0681865 2.34 .23 2.57
0682683 30.00 3.00 33.00
0682697 9.48 .94 10.42
0684836 80.40 8.04 88.44
0PAID STATUS PAID 10/19/2004

0ASMNT-NBR 000803514-9 2006 TRA 017-018 BILL 200649919
0000000 3,293.72 329.36 20.00 3,643.08
0681865 4.68 .46 5.14
0682683 60.00 6.00 66.00
0682694 13.66 1.36 15.02
0682695 26.42 2.64 29.06
0682697 19.80 1.98 21.78
0684836 240.00 24.00 264.00
0PAID STATUS PAID 06/76/2008

0ASMNT-NBR 000803514-9 2007 TRA 017-018 BILL
0000000 3,408.98 340.88 20.00 3,769.86
0681865 4.68 .46 5.14
0682683 60.00 6.00 66.00
0682694 13.66 1.36 15.02
0682695 26.42 2.64 29.06
0682697 20.64 2.06 22.70
0684836 258.00 25.80 283.80
0PAID STATUS NOT PAID

0ASMNT-NBR 000803515-0 1996
0ERRB004 TAX YEAR NOT FOUND ON DATA BASE

0ASMNT-NBR 000803516-1 1980
0ERRB004 TAX YEAR NOT FOUND ON DATA BASE

0ASMNT-NBR 000803516-1 1981
0ERRB004 TAX YEAR NOT FOUND ON DATA BASE

0ASMNT-NBR 000803516-1 1982 TRA 011-035
0000000 3,610.24 361.02 5.00 3,976.26
0184061 .00 .00 .00
0681366 80.64 8.06 88.70
0PAID STATUS PARTIAL PAID

0ASMNT-NBR 000803520-4 2005 TRA 085-000 BILL 200552240
0000000 322.12 32.20 20.00 374.32
0PAID STATUS PAID 10/74/2007

0ASMNT-NBR 000803523-7 1995
0ERRB004 TAX YEAR NOT FOUND ON DATA BASE

0ASMNT-NBR 000803523-7 1996
0ERRB004 TAX YEAR NOT FOUND ON DATA BASE

0ASMNT-NBR 000803523-7 1997
0ERRB004 TAX YEAR NOT FOUND ON DATA BASE

0ASMNT-NBR 000803525-9 1977 TRA 085-000 BILL
0000000 10.58 .63 .00 11.21
0PAID STATUS NOT PAID

0ASMNT-NBR 000803525-9 1978 TRA 085-000 BILL
0000000 4.90 .28 3.00 8.18
0PAID STATUS NOT PAID

0ASMNT-NBR 000803525-9 1979 TRA 085-000 BILL
0000000 33.34 2.00 3.00 38.34
0PAID STATUS NOT PAID

0ASMNT-NBR 000803525-9 1983
0ERRB004 TAX YEAR NOT FOUND ON DATA BASE

0ASMNT-NBR 000803525-9 2000 TRA 085-000 BILL 200049063
0000000 51.03 .00 .00 51.03
0PAID STATUS PAID 10/77/2001
 
I find the cells with '0PAID STATUS" and find the cells with
"0ERRB004" then do INSERT CELLS so these cells are shifted into Col B.

Then, in Col B, I delete the top three empty cells so the first cell
with "0PAID STATUS" is on the same row as its corresponding cell with
"0ASMNT-NBR" in Col A.

However, the subsequent cells with "0PAID STATUS" or "0ERRB004" in Col
B are NOT on the same row as their corresponding cells with "0ASMNT-
NBR" in Col A.

How can I get the cells with "0PAID STATUS" or "0ERRB004" in Col B
moved they're on the same row as their corresponding cells with
"0ASMNT-NBR" in Col A?
 
I find the cells with '0PAID STATUS" and find the cells with
"0ERRB004"
then do INSERT CELLS so these cells are shifted right into Col B.

Then, in Col B, I delete the top three empty cells so the first cell
with
"0PAID STATUS" is on the same row as its corresponding cell with
"0ASMNT-NBR" in Col A.

However, the subsequent cells with "0PAID STATUS" or "0ERRB004"
in Col B are NOT on the same row as their corresponding cells with
"0ASMNT-NBR" in Col A.

How can I get the cells with "0PAID STATUS" or "0ERRB004" in
Col B moved up so they're on the same row as their corresponding
cells with "0ASMNT-NBR" in Col A?

Then, how can I get the cells in each "group" in Col C moved up so
the first cell in each "group" is on the same row as the corresponding
cells in Col A and Col B?
 
Back
Top