Advanced search

Excel geeks: how do I combine data/worksheets please?

(15 Posts)
VulpusinaWilfsuit Thu 13-Aug-09 14:43:50

I'm sure this is Excel 101 for idiots beginners for all you geeks but I'm struggling with the manual... I will get there but you could speed me up considerably.

Anyhow. Have multiple data sheets (by year) downloaded from another source. Need to combine into one master set. Either in one sheet.

Have different worksheets by year. Some row entries are the same, some different (but all in alphabetical order). Need to combine data so that each row is inserted into correct place into master sheet and then paste in sections of columns (eg so years with data columns x, y and z etc. are in consecutive sections of column)...

Am I making sense? Or am I making it horribly complicated? I know how to do this in SPSS but I can't renew my licence till next week and gotta do this NOW!

Thanks <weak smile>


VulpusinaWilfsuit Thu 13-Aug-09 15:07:24

Anybody? Pleeeeeeeeeeeeeeeeeeease?

Data looks like this:

Spreadsheet 1

Column 1 Column 2 2008

a b

x m 1 2
y n 2 3
z o 2 1

Spreadsheet 2

Column 1 Column 2 2007

a b

x m 3 4

z o 2 2

Where columns 1 and 2 are text entries and sometimes they are there for each year, and sometimes they are not...

Columns a and b are numbers of instances per year in different categories (so in each spreadsheet there are 3 instances of a for entry x in 2007 but only 1 instance in 2008)

I could just cut and past the 'by year' columns, but the problem is the row categories are different each year - sometimes y is in there one year, sometimes not etc...

<going slowly mad>

VulpusinaWilfsuit Thu 13-Aug-09 15:08:35

Oh feck. that didn't align at all...

I will go and lie down.

muddleduck Thu 13-Aug-09 15:10:07

Not sure I quite get this...
When Y is missing is there a blank line or does it go straight from x to z - if the former then cutting and pasting would work.
How would you do this in spss?

slug Thu 13-Aug-09 15:12:20

Are they multiple worksheets in one workbook, or multible workbooks? (i.e. lots of different files)

potplant Thu 13-Aug-09 15:14:31

Copy and paste - or is this more complicated?

Create a new tab for your master
Go to year 1 tab
Select All
Go to master tab and paste

(forgive me if I've missed the point and I'm talking like you're an idiot!)

AMumInScotland Thu 13-Aug-09 15:21:00

I'd export the whole bloody lot to MS Access and do it there....

Sorry but if your columns don't match up I don't think there is a way to join them short of using cut&paste and shuffling the lines till they do match

slug Thu 13-Aug-09 15:24:37

If they are all in one workbook, but on different sheets it's actually very easy.

Go to where you want your data to display
Type =
Find the cell which contains the data you want to display
Click in it
Press enter

Hey Presto your data is displayed in the first column. You can now drag it down if you want.

VulpusinaWilfsuit Thu 13-Aug-09 15:31:56

Many many helpful suggestions. Hmmm. BAsically there is text data which IS sometimes missing in different years. Column headings are the same. I'm not worried about columns but the row entries are all different and that is the key bit.

I guess I can copy over individual entries to a new sheet but that will take FOR EVER.

I think on SPSS (IIRC) the import function recognises and merges data. Maybe not; maybe only when there's a distinct ID number. Perhaps that is the way to solve it? Give each entry an ID number then combine/sort by that? Oh god, bitten off more than can chew quickly here...

bloss Thu 13-Aug-09 15:33:54

Message withdrawn

flashharriet Thu 13-Aug-09 15:36:37

So you want a master list of row labels? Can you put all rows together and then do a unique list? Then you could have a LOOKUP kind of clever macro thingy to put each bit of data in the right place...?

flashharriet Thu 13-Aug-09 15:37:12

Or, in fact, wot bloss sed grin

AMumInScotland Thu 13-Aug-09 15:37:23

Is there one key column which is moslty the same? If so, you could focus on that - if you insert blank rows in where a value is missing, then you could cut and paste the whole set of data and the rows would end up matching all along the row.

But you'd need to make sure each set was the same, so it depends how much work it would take to get them matching.

bloss Thu 13-Aug-09 15:37:57

Message withdrawn

VulpusinaWilfsuit Thu 13-Aug-09 20:18:33

Thanks - I think those last posts seem to have got the idea from my inane ramblings. <doofus emoticon> Will try those strategies.

Join the discussion

Registering is free, easy, and means you can join in the discussion, watch threads, get discounts, win prizes and lots more.

Register now »

Already registered? Log in with: