Help on Transpose the Rows conatining specific words automatically...

[neetu_fzr]

Hi

I have an excel sheet with approx 700 rows in which the record of bill payments made by the customers against their different phone numbers. I have to issue the customer a consolidated receipt with the numbers printed on the same. I have consolidated the date, Customer number and the total amount paid by him. But I am unable to get the phone numbers copied to the new rows corresponding to the customer name via Transpose command. I can do this manually but I want to get this automatically. A sample file is attached herewith for further reference and the results required. I am not familiar with formulas of excel but i have learn a lot from this forum. Kindly help to solve the given problem.

[hemesh]

Hello neetu in After sheet in B2 copy and paste below
=INDEX(Before!$B$2:$B$500,MATCH(0,INDEX(COUNTIF($B$1:B1,Before!$B$2:$B$500),0),0)) and drag down

in AFter sheet A2 copy and paste below and drag down
=IFERROR(INDEX(Before!$A$2:$A$500,MATCH(Sheet1!B2,Before!$B$2:$B$500,0)),"")

for amount in C2 copy paste below
=SUMPRODUCT(--(Before!$B$2:$B$500=Sheet1!B2)*(Before!$D$2:$D$500)) and drag down

for numbers in D2 to right side copy paste below
=IFERROR(INDEX(Before!$C$2:$C$500,SMALL(IF(Before!$B$2:$B$500=Sheet1!$B2,ROW(Before!$B$2:$B$500)-1),COLUMNS($A$1:A1))),"")
then hold control and shift together and hit enter now release all three keys to make it array formula
Then drag formula down and to the right side.

Click " * " add reputation icon in the bottom left corner of my post if this helps

[neetu_fzr]

Thanks Hemesh for the prompt reply... but one thing that I forgot to mention that there is also some data with same customer name but in different dates. How can I solve that. Again sample file is attached herewith for reference.

[hemesh]

Hello Find attached !

[neetu_fzr]

Thanks again for helping but Hemesh have you noticed one thing that the values in the column J6 to M6 should be present in Column D7 to G7 because the payment of these numbers were made on different date.

[hemesh]

Neetu Gimme some time I Will go through and Respond You back!

[hemesh]

Hello Neetu!
in After sheet at D2 copy paste below
=IFERROR(INDEX(Before!$C$2:$C$500,SMALL(IF(Before!$B$2:$B$500&Before!$A$2:$A$500=After!$B2&After!$A2,ROW(Before!$B$2:$B$500)-1),COLUMNS($A$1:A1))),"")
Hold control and Shift together then hit enter. Now drag down and Drag to the Right

[neetu_fzr]

A very big thanks again Hemesh...