Transpose Multiple Columns to Single Row

I could sure use some advise.
I have a spreadsheet with information displayed like this:

Column A B C D

company 1 red 200 pencil
company 1 blue 50 pen
company 2 purple 300 marker
company 2 orange 50 peanuts
company 3 Holy Cow Woohoo
company 3 Eye Am Confused

I need to transpose it to look like:

Column A B C D E F G

Company 1 red 200 pencil blue 50 pen
Company 2 purple 300 marker orange 50 peanuts
etc.

I can build my own basic functions and a beginner at VBA Code so if you
have any suggestions...Be gentle.

Assume your data is arranged like this:
Co1 red 200 ppr
Co1 blu 50 pen
Co2 pur 300 mrk
Co2 org 60 jot
Co3 yel 250 nte
Co3 grn 502 ers
comp


Co1 red 200 ppr blu 50 pen
Co2 pur 300 mrk org 60 jot
Co3 yel 250 nte grn 502 ers

Select B1:D6 and name it array1
Use Insert > Name > Define
Verify by selecting those 18 cells and checking that
the name window says array1.
Select A1:A7 and
Insert > Name > Create > Bottom Row
Select Co1 thru Co3 and verify name.
At A10:A12, enter this formula:
=INDEX(comp,ROW()*2-19)
At B10:G12, enter this formula:
=INDEX(array1,FLOOR((COLUMN()-2)/3,1)+ROW()*2-19,MOD(COLUMN()-2,3)+1)
If you want to move the results to a different row,
you have to change the number 19 in the formulas.

This may work if...

Your data in column A is always duplicated (exactly two rows per company) and
the data is always in 3 columns (only B:D).

If the data starts in row 1, put this in E1:
=IF(MOD(ROW(),2)=1,B2,NA())
Drag it across E1:G1

Drag E1:G1 down as far as your data goes.

Now select E:G
Edit|copy
Edit|paste special Values

With E:G still selected:
Edit|Goto|Special|Constants
and uncheck Numbers, text, logicals, but leave Errors checked (hit ok)
Now all the #N/A's are selected
edit|delete|Entire row.



whicks wrote:
>
> I could sure use some advise.
> I have a spreadsheet with information displayed like this:
>
> Column A B C D
>
> company 1 red 200 pencil
> company 1 blue 50 pen
> company 2 purple 300 marker
> company 2 orange 50 peanuts
> company 3 Holy Cow Woohoo
> company 3 Eye Am Confused
>
> I need to transpose it to look like:
>
> Column A B C D E F G
>
> Company 1 red 200 pencil blue 50 pen
> Company 2 purple 300 marker orange 50 peanuts
> etc.
>
> I can build my own basic functions and a beginner at VBA Code so if you
> have any suggestions...Be gentle.

--

Dave Peterson

Here's another play to try ..

A sample construct is available at:
http://cjoint.com/?cepMimcKuN
Transpose Multiple Columns to Single Row_whicks_gen.xls

Source data is assumed in Sheet1, cols A to G, from row1 down
(each company is assumed to hold only 2 lines of data, as posted)

In Sheet2,

Put in A1 (normal ENTER):
=IF(ISERROR(SMALL(Sheet1!$E:$E,ROW(A1))),"",
INDEX(Sheet1!A:A,MATCH(SMALL(Sheet1!$E:$E,ROW(A1)),Sheet1!$E:$E,0)))
Copy A1 to D1

Put in E1, array-enter (press CTRL+SHIFT+ENTER):
=IF(ISNA(MATCH(1,($H$1:$H$100="x")*(Sheet1!$A$1:$A$100=$A1),0)),"",
INDEX(Sheet1!B:B,MATCH(1,($H$1:$H$100="x")*(Sheet1!$A$1:$A$100=$A1),0)))
Copy E1 to G1

(Adapt the ranges to suit)

Put in H1:
=IF(Sheet1!A1="","",IF(COUNTIF(Sheet1!$A$1:A1,Sheet1!A1)>1,"x",ROW()))

Select A1:H1, fill down to cover the extent of data in Sheet1
Cols A to G will return the required results
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"whicks" <whicks@kpmg.com> wrote in message
news:1138999340.983989.186690@g44g2000cwa.googlegroups.com...
> I could sure use some advise.
> I have a spreadsheet with information displayed like this:
>
> Column A B C D
>
> company 1 red 200 pencil
> company 1 blue 50 pen
> company 2 purple 300 marker
> company 2 orange 50 peanuts
> company 3 Holy Cow Woohoo
> company 3 Eye Am Confused
>
> I need to transpose it to look like:
>
> Column A B C D E F G
>
> Company 1 red 200 pencil blue 50 pen
> Company 2 purple 300 marker orange 50 peanuts
> etc.
>
> I can build my own basic functions and a beginner at VBA Code so if you
> have any suggestions...Be gentle.
>

Oops, sorry, some corrections to the (description) lines:

> Put in A1 (normal ENTER):
> =IF(ISERROR(SMALL(Sheet1!$E:$E,ROW(A1))),"",
> INDEX(Sheet1!A:A,MATCH(SMALL(Sheet1!$E:$E,ROW(A1)),Sheet1!$E:$E,0)))

Should read as:

Put in A1 (normal ENTER):
=IF(ISERROR(SMALL($H:$H,ROW(A1))),"",
INDEX(Sheet1!A:A,MATCH(SMALL($H:$H,ROW(A1)),$H:$H,0)))

(The above formula is already entered correctly in the previous sample,
but missed updating in the descriptions)

Revised sample construct (corrected descriptions) at:
http://cjoint.com/?ceqdoihxCj
Transpose Multiple Columns to Single Row_whicks_gen.xls
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Bonjour,

http://cjoint.com/?ceuKijO3RV

=SI(INDEX(mytable;EQUIV($A1;INDEX(mytable;;1);0)+1;1)=$A1;INDEX(mytable;EQUIV($A1;INDEX(mytable;;1);0)+1;2);"")

Solution VBA:

Sub cree1Ligne()
'Application.ScreenUpdating = False
Range("A1").CurrentRegion.Sort Key1:=Range("A1"), Header:=xlYes 'tri
Range("a1").Select
ligne = 1
Do While ActiveCell <> ""
matricule = ActiveCell
Sheets("Sheet2").Cells(ligne, 1) = ActiveCell
c = 2
Do While ActiveCell = matricule
Sheets("Sheet2").Cells(ligne, c) = ActiveCell.Offset(0, 1)
Sheets("Sheet2").Cells(ligne, c + 1) = ActiveCell.Offset(0, 2)
Sheets("Sheet2").Cells(ligne, c + 2) = ActiveCell.Offset(0, 3)
c = c + 3
ActiveCell.Offset(1, 0).Select
Loop
ligne = ligne + 1
Loop
Range("a2").Select
End Sub

JB

Everyone,

Thank you for all your help. You have made a daunting and highly
manual task easy. Also, your explanations were elementary enough for
my simple mind to wrap around.

Cheers!

Whicks

You're welcome !
Thanks for posting back ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"whicks" <whicks@kpmg.com> wrote in message
news:1139241021.187340.306360@z14g2000cwz.googlegroups.com...
> Everyone,
>
> Thank you for all your help. You have made a daunting and highly
> manual task easy. Also, your explanations were elementary enough for
> my simple mind to wrap around.
>
> Cheers!
>
> Whicks
>