(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


--
nickr1954
------------------------------------------------------------------------
nickr1954's Profile: http://www.excelforum.com/member.php...o&userid=30907
View this thread: http://www.excelforum.com/showthread...hreadid=505906

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

"nickr1954" wrote in
message ...

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 A18 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 ,
nickr1954
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!


--
nickr1954
------------------------------------------------------------------------
nickr1954's Profile: http://www.excelforum.com/member.php...o&userid=30907
View this thread: http://www.excelforum.com/showthread...hreadid=505906

In article ,
nickr1954
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.