Microsoft Office excel 2007 home & stude...

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 read

A 5

B 10

C 15

D 6

and dataset b:

A 10

C 20

D 8

i 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!

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 6In 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 DThen 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 8You 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.

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!

Ask Your Question

Weekly Poll

Do you trust smart speakers to not spy on you?

Discuss in The Lounge

Poll History