copying data from vertical to horizontal

[harriettb]

Just when I am finishing with a very time consuming project I'm now told my vertical data has to be horizontal. Oh, and did I mention it has to be ready by tomorrow noon...HELP! Here's what I got, 4 columns of data, column 1 is an unique identifier for a recipe, column 2 are the ingredients, column 3 is the ingredient amount, column 4 is an assigned ingredient code.
Rcode Ingrdt Serv Ingrdt code
R-1 eggs .0010 35
milk .0030 22
R-2 salt .0001 07
eggs .0005 35
flour .0016 100
There are about 275 recipes and the total number of ingredients are 1200. Now I'm told by the db folks that the data from each recipe has to be set up horizontally in this manner:
Rcode SVR#1 IC SVR#2 IC SVR#3 IC
R-1 .0010 35 .0030 22
R-2 .0001 07 .0005 35 .0016 100
Listed this way a recipe can have up to 10 ingredients (SVR#'s).
Could you possibly suggest a way in which I can acomplish this task without having to do so line by line. I will appreciate any help you have to offer, thank you.
Harriett
This message was edited by harriettb on 2002-10-15 00:24
This message was edited by harriettb on 2002-10-15 00:25

 

[The Idea Dude]

Don't know if this is any help but have you tried copying the range, then paste special with a tick in the transpose box?

 

[harriettb]

Thank you for mentioning paste special, I am aware of that feature. However, I would still have to apply paste special to each of the 1200 lines of the recipe ingredients. I was hoping someone would know of a quicker way to get the job done. I'm trying to write some macros that will do most of the tedious copy/paste work but I'm having a time with the absolute and relative part of the application. Again, thank you and if you should think of anything else that might be of help please let me know because this is going to be an on going project it seems.
Harriett

 

[maxflia10]

Harriet,

You might want to check out ASAP Utilities.

http://www.asap-utilities

It has a transposer. Maybe it'll save some time....

 

[shades]

Perhaps this might help. In a database list, every cell needs to be filled. It seems like you have most of the data already.

I assume based on your description that column A has R-1 and then several rows (each starting with column B) - meaning that column A is blank under the first item.

Then after say five ingredients, you have R-2 in Column A. Is this correct?

If so, then you can fill the blanks (use GoTo and special - here is a macro to do that - but it will every blank cell, which is more than you need, so use with caution and experiment with a test file, in other words some ingredients are not going to be used, so you want it filled with some descriptor - N/A for instance).

Then you can copy the entire data set (not the whole sheet), move to another sheet, and in A1, Paste Special > Transpose.

This is a simplified attempt, but might point you in the right direction.

--------
Macro to fill blanks (maybe use twice, once for column A, and once for the other columns)

Sub FillBlanks()
ActiveCell.Offset(0, 0).Range("A1").Select
Selection.CurrentRegion.Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=R[-1]C"
Selection.CurrentRegion.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
ActiveCell.Select

-----

HTH.