Transpose Multiple Columns to Single Row

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