# inserting cells to align data

Microsoft Office excel 2007 home & stude...
January 18, 2011 at 07:25:43
Specs: Windows 7
 i have six datasets with values for several variables. most of the variable are shared between datasets however some are not. for example for variable A i have a value in each of the six datasets, however for variable B i only have data for four of the datasets. this each dataset does not have the same number of rows. for example dataset a might readA 5B 10C 15D 6and dataset b:A 10C 20D 8i have all the data in one sheet of excel and would like to be able to insert cells so as to line up the variables (eg insert cells under "A" and "20" for dataset b so that C 15 and C 20 are on the same line. then do some stats with the rows.. averages etc etc.Does anyone have some insight? thanks!

See More: inserting cells to align data

#1
January 18, 2011 at 08:53:40
 For the example data given, I was able to accomplish what you asked for. You will porbably have to modify this technique for your actual data sets.The main thing to keep in mind is that Excel formulas can not insert cells or perform "actions". The only thing that formulas can do is return results based on the cells/data that they reference. If this technique does not work for you, we'll probably have to write some VBA code to insert the cells.I started with this:``` A B C D E 1 A 5 A 10 2 B 10 C 20 3 C 15 D 8 4 D 6``` In F1, I entered tis formula and drug it down to F4:=IF(ISNA(VLOOKUP(A1,\$D\$1:\$E\$3,1,0)),"",VLOOKUP(A1,\$D\$1:\$E\$3,1,0))This gave me a table that looks like this:``` A B C D E F 1 A 5 A 10 A 2 B 10 C 20 3 C 15 D 8 C 4 D 6 D```Then in G1 I entered this formula and drug it down to G4:=IF(ISNA(VLOOKUP(F1,\$D\$1:\$E\$3,2,0)),"",VLOOKUP(F1,\$D\$1:\$E\$3,2,0))Which gave me this table:``` A B C D E F G 1 A 5 A 10 A 10 2 B 10 C 20 3 C 15 D 8 C 20 4 D 6 D 8```You could then do a Copy...PasteSpecial - Values on Columns F & G to eliminate the formulas and lock in the values.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

#2
January 19, 2011 at 01:38:34
 Thats excellent, thanks so much! Youve saved me about a week of time I think... Im doing it with 25000 rows and 6 datasets so im glad I didnt have to manually insert cells!

Report •

Related Solutions