(Again) Converting rows to columns

[nickr1954]

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

 

[Barb Reinhardt]

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

 

[Domenic]

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!

 

[nickr1954]

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!

 

[Domenic]

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.