(Again) Converting rows to columns

Would appreciate some advice: I am currently downloading some data into an Excel spreadsheet that gives me the following format:

Dept.# Account# Month Actuals
Dept.# Account# Month Budget

Dept.# Account# Month Actuals
Dept.# Account# Month Budget

Dept.# Account# Month Actuals
Dept.# Account# Month Budget

I would like to get the actuals in one column and the budget in the next column, but everything else would have to match up (ie make sure i am matching the same dept and account #). I am not quite sure how to go about this and am not an Excel expert. Would appreciate any advice you may have. Thanks

If you are doing this frequently, you may want to write a macro to do it.
Posting to programming forum for assistance.

"nickr1954" <[email protected]> wrote in
message news:[email protected]...
>
> Would appreciate some advice: I am currently downloading some data into
> an Excel spreadsheet that gives me the following format:
>
> Dept.# Account# Month Actuals
> Dept.# Account# Month Budget
>
> Dept.# Account# Month Actuals
> Dept.# Account# Month Budget
>
> Dept.# Account# Month Actuals
> Dept.# Account# Month Budget
>
> I would like to get the actuals in one column and the budget in the
> next column, but everything else would have to match up (ie make sure i
> am matching the same dept and account #). I am not quite sure how to go
> about this and am not an Excel expert. Would appreciate any advice you
> may have. Thanks
>
>
> --
> nickr1954
> ------------------------------------------------------------------------
> nickr1954's Profile:
> http://www.excelforum.com/member.php...o&userid=30907
> View this thread: http://www.excelforum.com/showthread...hreadid=505906
>

If your format is consistent, that is, one blank row between every two
rows, assuming that A1:D8 contains your data, try the following...

F1, copied down and across:

=OFFSET($A$1,COLUMN()-COLUMN($F1),ROW()-ROW(F$1))

Then, convert the formulas into values by doing the following...

1) Select the range of cells in your new columns

2) Edit > Copy > Edit > Paste Special > Values > Ok

3) Select columns containing zero values

4) Edit > Clear > Contents

Hope this helps!

In article <[email protected]>,
nickr1954 <[email protected]>
wrote:

> Would appreciate some advice: I am currently downloading some data into
> an Excel spreadsheet that gives me the following format:
>
> Dept.# Account# Month Actuals
> Dept.# Account# Month Budget
>
> Dept.# Account# Month Actuals
> Dept.# Account# Month Budget
>
> Dept.# Account# Month Actuals
> Dept.# Account# Month Budget
>
> I would like to get the actuals in one column and the budget in the
> next column, but everything else would have to match up (ie make sure i
> am matching the same dept and account #). I am not quite sure how to go
> about this and am not an Excel expert. Would appreciate any advice you
> may have. Thanks

Actually, there are no blank rows between the rows of data. It is one continuous string. Would that change anything in the formula you provided? Thanks a lot for your advice!

In article <[email protected]>,
nickr1954 <[email protected]>
wrote:

> Actually, there are no blank rows between the rows of data.

In that case, you can skip steps 3 and 4.

>It is one continuous string.

If you mean that each row is one continuous string and are not in
separate columns, as I assumed, put them in different columns first and
then use the formula I offered. To separate your text string in
separate columns...

1) Select your range of cells

2) Data > Text to Columns

....and follow the prompts.