Rearrangement of reported data


J

JMay

Monthly I receive an excel file that looks as so:

A B C D E
1 Dept Code Date Amt Comment
2 977 123 4/05/04 100.00 asdf
3 877 234 4/03/04 90.00 sdfg
4 777 345 4/05/04 80.00 fghjgh
5 ABCD
6 877 222 4/03/04 110.00 ytry
7 777 333 4/01/04 75.00 wer
8 666 444 4/05/04 65.00 nbvc
9 555 988 4/06/04 83.00 kjlkj
10 435 922 4/06/04 125.00 lkjklj
11 555 834 4/11/04 111.00 lkj
12 DERB
.............

Above are only Two Blocks of maybe 100 Blocks;
Example Block 1 (above) has 3 lines and Block 2 has 6 lines.
A Block **CAN BE** only 1 line of data
But each block is followed by a code in Column D that applys
to each item in the block Above it.

For starters I have inserted a column Before Col A Above thinking
I could enter in new A2 an Offset() function that could somehow
bring back the "ABCD" Value.. Same in A3 that would bring back
the "ABCD: Value.

After mulling over I haven't got a clue where to begin on this.
Can anyone assist with a plan?
TIA,
 
Ad

Advertisements

N

Norman Jones

Hi JMay,

Try:

Sub Tester()

Dim ar As Range
ActiveSheet.Range("A1").EntireColumn.Insert
For Each ar In Columns(2) _
.SpecialCells(xlCellTypeConstants).Areas

ar.Offset(0, -1) = _
ar(ar.Cells.Count).Offset(1, 3)
Next

End Sub
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top