copying data from vertical to horizontal

harriettb

New Member
Joined
Oct 13, 2002
Messages
2
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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

The Idea Dude

Well-known Member
Joined
Aug 15, 2002
Messages
589
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

New Member
Joined
Oct 13, 2002
Messages
2
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
 

shades

Well-known Member
Joined
Mar 20, 2002
Messages
1,550
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.
 

Forum statistics

Threads
1,144,307
Messages
5,723,627
Members
422,505
Latest member
Noar33

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top