Mr. Microsoft Excel Expert actually needs Excel help :)

[Azurik]

The data are found in multiple columns, however, each row contains only one data entry. I need to consolidate them to one column only (A1 to A11, for example).

Excel Chart

For instance guys:

I have some numbers in A1, B2, C3, C4 and C5.

As you can see, numbers can be in the same COLUMN, but not the same ROW. I'm basically trying to shift the numbers left.

A1 = A1
B2 = A2
C3 = A3
C4 = A4
C5 = A5


How the hell do I do this? Is there a function for this?

Your first thread on this was moved to Software for Windows for a reason.
Moderator allisolm

 

[her209]

Didn't you start one earlier?

 

[Azurik]

Yes, but somehow it dissapeared.

 

[Epic Fail]

http://forums.anandtech.com/me...id=33&threadid=2116001

moved by the mod?

 

[Mojoed]

Looks like your bbzzdd account got suspended within the past few minutes.

 

[HomeBrewerDude]

actually, all of bbzzdd is down

 

[OdiN]

Can you put the pic somewhere so that I can see it?

I program in VBA all day sometimes. There is always a way to do something, however sometimes it may take more time than to do it manually, but still...always a way

 

[Mojoed]

Originally posted by: HomeBrewerDude
actually, all of bbzzdd is down

Oops, yep you're right. :beer:

 

[Azurik]

For instance guys:

I have some numbers in A1, B2, C3, C4 and C5.

As you can see, numbers can be in the same COLUMN, but not the same ROW. I'm basically trying to shift the numbers left.

A1 = A1
B2 = A2
C3 = A3
C4 = A4
C5 = A5

 

[HomeBrewerDude]

concatenate function.... might need to convert to text then concatenate and then use the value function

 

[jagec]

Transpose!

Switch rows of cells to columns, or columns to rows
1. Select the cells that you want to switch.
2. Click Copy .
3. Select the upper-left cell of the paste area.
The paste area must be outside the copy are4. On the Edit menu, click Paste Special.
5. Select the Transpose check box.
Note Data from the top row of the copy area appears in the left column of the paste area, and data from the left column appears in the top row.

/edit: oops, misunderstood. nm.

 

[Azurik]

Originally posted by: jagec
Transpose!

Switch rows of cells to columns, or columns to rows
1. Select the cells that you want to switch.
2. Click Copy .
3. Select the upper-left cell of the paste area.
The paste area must be outside the copy are4. On the Edit menu, click Paste Special.
5. Select the Transpose check box.
Note Data from the top row of the copy area appears in the left column of the paste area, and data from the left column appears in the top row.

Seriously, use the help function, it's what it's there for.

/edit: oops, misunderstood. nm.

Transpose won't work. It's not a row to column or column to row. Opps... just saw your edit

 

[HomeBrewerDude]

convert cells to text then:

=VALUE(CONCATENATE(A1,B1,C1))

 

[Azurik]

Originally posted by: HomeBrewerDude

convert cells to text then:

=VALUE(CONCATENATE(A1,B1,C1))

HomeBrewerDude,

Thanks for the emphasis on your suggestion. I converted my numbers into text, but where do I put the =VALUE(CONCATENATE(A1,B1,C1)) ?

 

[HomeBrewerDude]

Originally posted by: Azurik

Originally posted by: HomeBrewerDude

convert cells to text then:

=VALUE(CONCATENATE(A1,B1,C1))

HomeBrewerDude,

Thanks for the emphasis on your suggestion. I converted my numbers into text, but where do I put the =VALUE(CONCATENATE(A1,B1,C1)) ?

whereever you want to put the data.

 

[Azurik]

Originally posted by: HomeBrewerDude

Originally posted by: Azurik

Originally posted by: HomeBrewerDude

convert cells to text then:

=VALUE(CONCATENATE(A1,B1,C1))

HomeBrewerDude,

Thanks for the emphasis on your suggestion. I converted my numbers into text, but where do I put the =VALUE(CONCATENATE(A1,B1,C1)) ?

whereever you want to put the data.

I must be doing something wrong because it's not working. Will try in the AM with CONCATENATE.

 

[OdiN]

If you can post sample data, it would be much easier to assist you.

 

[RossMAN]

YGPM

 

[HomeBrewerDude]

Originally posted by: Azurik

Originally posted by: HomeBrewerDude

Originally posted by: Azurik

Originally posted by: HomeBrewerDude

convert cells to text then:

=VALUE(CONCATENATE(A1,B1,C1))

HomeBrewerDude,

Thanks for the emphasis on your suggestion. I converted my numbers into text, but where do I put the =VALUE(CONCATENATE(A1,B1,C1,D1,E1)) ?

whereever you want to put the data.

I must be doing something wrong because it's not working. Will try in the AM with CONCATENATE.

Ok, lets say you've got data in one of 5 columns (e.g., A:E) for 20 rows. You can put the above formula into column F1, then copy and past it down to F20.

If you have numbers in more than one column per row, this will give out crap

 

[OdiN]

Okay....if I understand what you have....the following will work.

Open your file and hit Alt + F11

This will bring up the VB code.

You want to copy and paste this code into the "This Workbook" object.

Change the line that has "65536" to the number of the row which has your last piece of data for faster performance.


****BEGIN CODE****

Sub Arrange()

Application.ScreenUpdating = False

For i = 1 To 65536

Range("A" & i).Select
Selection.End(xlToRight).Select
Selection.Cut
Range("A" & i).Select
ActiveSheet.Paste

Next i

Application.ScreenUpdating = True

MsgBox "All Done!", vbOKOnly

End Sub


****END CODE****

 

[Shortcut]

is the data just numbers?

if it's just #s, you might be able to get what you want by having one column sum up the contents of each row. e.g. sum(b1:zz1) in cell a1. sum (b2:zz2) in a2, etc.

if its text...i have absolutely no idea =(

 

[Mojoed]

Originally posted by: Shortcut
is the data just numbers?

if it's just #s, you might be able to get what you want by having one column sum up the contents of each row. e.g. sum(b1:zz1) in cell a1. sum (b2:zz2) in a2, etc.

if its text...i have absolutely no idea =(

^His link is working again btw, and yeah it's text.

 

[Gunslinger08]

Originally posted by: HomeBrewerDude

Originally posted by: Azurik

Originally posted by: HomeBrewerDude

Originally posted by: Azurik

Originally posted by: HomeBrewerDude

convert cells to text then:

=VALUE(CONCATENATE(A1,B1,C1))

HomeBrewerDude,

Thanks for the emphasis on your suggestion. I converted my numbers into text, but where do I put the =VALUE(CONCATENATE(A1,B1,C1,D1,E1)) ?

whereever you want to put the data.

I must be doing something wrong because it's not working. Will try in the AM with CONCATENATE.

Ok, lets say you've got data in one of 5 columns (e.g., A:E) for 20 rows. You can put the above formula into column F1, then copy and past it down to F20.

If you have numbers in more than one column per row, this will give out crap

HomeBrewerDude is correct, though I only had to use =CONCATENATE(A1,B1,C1,D1), not VALUE() as well.