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

[VulpusinaWilfsuit]

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

[VulpusinaWilfsuit]

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...

[VulpusinaWilfsuit]

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

I will go and lie down.

[muddleduck]

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]

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

[potplant]

Copy and paste - or is this more complicated?

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

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

[AMumInScotland]

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]

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]

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]

Message withdrawn

[flashharriet]

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]

Or, in fact, wot bloss sed

[AMumInScotland]

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]

Message withdrawn

[VulpusinaWilfsuit]

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