Difficult Transposing Issue

[Aeoneye]

The File
I have a list of clients and their services that I need to transpose from rows to columns. This one is a little more tricky, so I can't use "paste special > transpose".

The Problem
The clients and their services are listed separately in rows:

Cust # price
55551 $100
55552 $50
55552 $25
55553 $200
55554 $35
55555 $655
55555 $51
55555 $894
55555 $156

but I need the spreadsheet to say:

Cust # price1 price 2 price 3 price 4
55551 $100
55552 $50 $25
55553 $200
55554 $35
55555 $655 $51 $894 $156


My Idea
Important: Open attachment first.

This is the logic I have in mind for the formula, but I don't know how to build it. It is NOT an exact formula whatsoever:

insert into E2
IF D2 matches any cell in A:A, then (If adjacent B cell has the number 1 in it) copy adjacent C cell into E2, if not then ""

insert into F2
IF D2 matches any cell in A:A, then (If adjacent B cell has the number 2 in it) copy adjacent C cell into F2, if not then ""

insert into G2
IF D2 matches any cell in A:A, then (If adjacent B cell has the number 3 in it) copy adjacent C cell into G2, if not then ""

insert into H2
IF D2 matches any cell in A:A, then (If adjacent B cell has the number 4 in it) copy adjacent C cell into H2, if not then ""

insert into I2
IF D2 matches any cell in A:A, then (If adjacent B cell has the number 5 in it) copy adjacent C cell into I2, if not then ""

insert into J2
IF D2 matches any cell in A:A, then (If adjacent B cell has the number 6 in it) copy adjacent C cell into J2, if not then ""

A = Customer Number
B = Duplicate Sequence #
C = Price
D = Unduplicated Customer #
E = Price 1
F = Price 2
G = Price 3
H = Price 4
I = Price 5
J = Price 6

Further Explaination
Column A displays the customer number

55551
55552
55552
55553
55554
55555
55555
55555
55555

Column B displays the sequence of the customer duplicate:

1
1
2 (2nd time the customer number is listed)
1
1
1
2
3
4

If you can think of a better way to accomplish this, then sweet! One could say, "Why don't you just manually do this?" Well, that's because this is over 5,000 customers, and more than 6 prices.

No VBA please.

Thanks!

[tigeravatar]

Aeoneye,

Attached is a modified version of your example workbook. I noticed you had an array formula in cell D2 and copied down to get the unique cust#'s. I removed that formula in favor of an error-handling non-array formula.

In cell D2 and copied down is this regular formula:

Please Login or Register  to view this content.

Then, in cell E2 and copied over and down to cell J18 is this regular formula:

Please Login or Register  to view this content.

[JBeaucaire]

'COLUMNS TO ROWS
Here's a macro for merging columns of data to one row matching for column A. There's a sample workbook too you could drop your data into and test it out.

Columns to Rows - Part 2

[Aeoneye]

You're a genius, tigeravatar! The formula worked perfectly, and I didn't run into any bugs from what I can see within the 5,000 records. The totals add up, but I'll have to look through it. Thanks a ton.

I didn't check out the macro, Jbeaucaire, since the code I needed came through. I'm sure it will come in use for some body. Thanks for the multiple options.

Take it easy.

[JBeaucaire]

(hat tip)

If that takes care of your need, please select Thread Tools from menu above and set this topic to SOLVED.