Inverting a matrix

[Allan]

I am working with a bunch of text, such as

name1
address1
phone1

name2
address2
phone2

name 3
address3... (and on...)


I can't seem to find anything that can "invert" this, entire column, if
possible. so it would look like

name1 address1 phone1
name2 address2 phone2
name3 address3 phone3

From my university days, we would call this inverting the matrix... Do I
need programming to do this?

============================
Allan
Alberta Canada

 

[Andy Brown]

I can't seem to find anything that can "invert" this, entire column

Do I need programming to do this?

Doubtless you could, I just get a kick out of doing this with formulas
(perverse).

Assumes you have an unbroken list from A1 down, in "3 groups". In B1,

=OFFSET($A$1,(ROW()-1)*3+(COLUMN()-2),0)

Drag across & down as far as you need. Copy & Paste Special (Values) the
results to clean up.

Rgds,
Andy

 

[Guest]

Allan:
Assuming the first name is in A1.

1) in B1 enter =A2
2) in C1 enter =A3
3) select cells B1 through C4
4) Drag the fill handle to bottom of list. (Black Square in lower right corner of selection)
5) Select columns B and C
6) Edit>Copy and Edit>Paste_Special>Values
7) Sort on B and delet extra info in column A
8) Sort on A

Good Luck,
Mark Graesser
(e-mail address removed)


----- Allan wrote: -----

I am working with a bunch of text, such as

name1
address1
phone1

name2
address2
phone2

name 3
address3... (and on...)


I can't seem to find anything that can "invert" this, entire column, if
possible. so it would look like

name1 address1 phone1
name2 address2 phone2
name3 address3 phone3

From my university days, we would call this inverting the matrix... Do I
need programming to do this?

============================
Allan
Alberta Canada

 

[Andy Brown]

I couldn't get this to work Mark.

For "3 rows & a blank", I use (in B1)

=OFFSET($A$1,(ROW()-1)*4+(COLUMN()-2),0)

dragged down & across.

Rgds,
Andy

 

[Harlan Grove]

I am working with a bunch of text, such as

name1
address1
phone1

name2
address2
phone2

name 3
address3... (and on...)


I can't seem to find anything that can "invert" this, entire column, if
possible. so it would look like

name1 address1 phone1
name2 address2 phone2
name3 address3 phone3

From my university days, we would call this inverting the matrix... Do I
need programming to do this?

It's transposing, not inverting.

Another alternative. If your first record were in cells A1:A3, select B11 and
enter the array formula =TRANSPOSE(A1:A3) . Copy B11 and paste into B55,
B99, B1313, etc.

 

[Andy Brown]

paste into B55, B99, B1313, etc.

If 4, 8, etc. is blank (hard to tell from OP). Thanks Harlan, that's cool.

Rgds,
Andy

 

[Guest]

Andy
What didn't work? This is pretty much doing the same thing that Harlan set up. It just used different formulas, left the name in column A, and had a quick way to copy the formulas to each forth row

Mark Graesser

 

[Andy Brown]

Mark,

Andy,
What didn't work?

My mistake, I had the wrong selection.

Now I have it going, agreed.

Rgds,
Andy

 

[Guest]

Andy
I like your offset formula since the table comes out on the first shot without any sorting. It always takes me longer to set up an OFFSET then to just do it manually

If Allen's data has a blank row between each data set the formula would need to be change to

=OFFSET($A$1,(ROW()-1)*4+(COLUMN()-2),0

since every record would have a fourth blank row

Regards
Mark Graesse
(e-mail address removed)

 

[Andy Brown]

It always takes longer to set up an OFFSET then to just do it manually.

For sure, but I don't have much choice since brute force methods are about
my limit.

If Allen's data has a blank row between each data set the formula would need to be change to:
=OFFSET($A$1,(ROW()-1)*4+(COLUMN()-2),0)

I'm just having one of those invisible days.

For "3 rows & a blank", I use (in B1)
=OFFSET($A$1,(ROW()-1)*4+(COLUMN()-2),0)
dragged down & across.

Best rgds,
Andy