Do we have spreadsheet experts here?

[Mudcat]

I'm having some issues.


Here's a question for starters:

Suppose you have two columns of data that you want to merge into one long one. Is there an easy way to do that?



Lemme see if I can illustrate. Suppose you have this:

12
34
56
78



Is there an easy way, other than just manually transcribing, to turn it into this:

1
2
3
4
5
6
7
8


If there was a way to just insert a space between rows, I could work from there. In other words turn it into this:

12

34

56

78



Because then I could just shift the 2nd column down a notch and do left-to-right sums. But I don't know how to insert those empty rows other than doing each one by hand. That would probably be a bit better than just transcribing but I am hoping for something more elegant.

Am I making sense?

 

[mcbaseball10]

Muddy, not sure I completely understand what you are trying to do but by holding (ctrl) and getting the (+) sign to appear on the highlighted cell you can drag down and it will auto fill the numbers starting at whatever number you are on. FYI, I am not very advanced with spreadsheets so maybe you are looking for more than that.

 

[MrX]

Okay, here's a way. Assuming you have columns A and B that you wish to combine, try putting this formula in D1 and fill down:

=IF(ISODD(ROW(D1)),INDEX(A:B,(ROW(D1)+1)/2,1),INDEX(A:B,ROW(D1)/2,2))

 

[djiddish98]

Does the ordering matter / is this data that can be ordered easily? Why not just copy Column B to the bottom of column A and then resort?

 

[mcbaseball10]

Or just disregard my post and listen to the mad scientist, him pro. :

 

[Mudcat]

Okay, here's a way. Assuming you have columns A and B that you wish to combine, try putting this formula in D1 and fill down:

=IF(ISODD(ROW(D1)),INDEX(A:B,(ROW(D1)+1)/2,1),INDEX(A:B,ROW(D1)/2,2))

Shoot, got myself in a time crunch here. I will definitely try that first thing in the morning. Thanks a lot.

Does the ordering matter / is this data that can be ordered easily? Why not just copy Column B to the bottom of column A and then resort?

No that wouldn't do it - the exact ordering definitely matters and isn't sortable. Thanks though.


Thanks McB.

 

[djiddish98]

Okay, here's a way. Assuming you have columns A and B that you wish to combine, try putting this formula in D1 and fill down:

=IF(ISODD(ROW(D1)),INDEX(A:B,(ROW(D1)+1)/2,1),INDEX(A:B,ROW(D1)/2,2))

This looks like it should do the job and is fairly straight forward.

If for some reason it doesn't, you could create an index value for each column value. The items in column A could be listed as 1A, 2A, 3A and column B could be 1B, 2B, 3B etc (this can be done by using the concatenate function). Then copy the stuff on column B to column A and sort again. You should end up with 1A, 1B, 2A, 2B, etc.

This is the long winded way to go.

 

[Mudcat]

Okay, here's a way. Assuming you have columns A and B that you wish to combine, try putting this formula in D1 and fill down:

=IF(ISODD(ROW(D1)),INDEX(A:B,(ROW(D1)+1)/2,1),INDEX(A:B,ROW(D1)/2,2))

I don't know if I am misunderstanding the instructions but I end up with this in every cell in column D:

#NAME?

 

[djiddish98]

What version of excel do you have (if you are even using excel)?

This normally means that the function is not defined. I see IsOdd, row, and index in excel 2007. I'm wondering if IsOdd might be new in 2007 though?

 

[Mudcat]

It says

Microsoft Office Excel 2003 (11.8324.8324) SP3

Part of Microsoft Office Professional Edition 2003

 

[djiddish98]

If IsOdd is not included (you can try =IsOdd(2) to see if you get a result or error), you can try this work around

=IF(ROW(D1)/2<>INT(ROW(D1)/2),INDEX(A:B,(ROW(D1)+1)/2,1),INDEX(A:B,ROW(D1)/2,2))

Hopefully the INT function is in there.

 

[fiveteamer]

Do this

=SUM(

I win.

 

[Mudcat]

If IsOdd is not included (you can try =IsOdd(2) to see if you get a result or error), you can try this work around

=IF(ROW(D1)/2<>INT(ROW(D1)/2),INDEX(A:B,(ROW(D1)+1)/2,1),INDEX(A:B,ROW(D1)/2,2))

Hopefully the INT function is in there.

OH MY FREAKING GAWD IT'S WORKING!! I swear I saw that nice long column of numbers and I heard a chorus of angels go, "Ahhhhhhhhhhhhhhhh"

Between a new resource I am working with that I keep refining and tips like this, I am going to be able to do in a day what used to take me 2 weeks.

I am a happy chappy.

 

[Swarmy]

muddy, if you get a promotion remember the 10% tithe to MrX and DJ.

 

[MrX]

I don't know if I am misunderstanding the instructions but I end up with this in every cell in column D:

#NAME?

Mudcat, try this file and tell me if it works.

http://cindigo.com/mudcat/Muddy.xls

EDIT: Oops, nevermind.

 

[MrX]

So I'm curious, ISODD() is not in Excel 2003? Because I looked it up and it's supposed to be.

 

[wal66]

I still do all my stuff manually. I do it in Excel but imput data manually. I've heard I can import data from outside sources/websites and have it automatically uploaded into Excel but I don't have a clue.

I kind of would like to know how but at the same time I still kind of like old schooling it. If it became too automated I would feel like it wasn't really my own work.

 

[Mudcat]

Mudcat, try this file and tell me if it works.

http://cindigo.com/mudcat/Muddy.xls

EDIT: Oops, nevermind.

I guess not. All the cells in column D say #NAME? which I assume is not the intention.