How to convert last few cells of rows into the columns of spreadsheet.

How to convert last few cells of rows into the columns of spreadsheet.

Question:

(By Sanjeev Kulshreshtha)

Hi i have a spreadsheet in following format:

Sn, Train, arr, Days, Compt Company

1,  12622, 22:30, Daily, F1, ABC Co.

2,  12622, 22:30, Daily, F2, XYZ Co.

3,  12622, 22:30, Daily, R1, ABC Co.

4,  12628, 21:15, Daily, F1, PQR Co.

5,  12628, 21:15, Daily, R1, ABC Co.

I would like to convert it in following format:

Sn, Train, arr, Days,  F1, F2,  R1

1,  12622, 22:30, Daily ABC Co, XYZ Co. ABC Co.

2,  12628, 22:30, Daily PQR Co,       , ABC Co.

Let me know how to do this?

is there any function, script or gadgets available?

I have tried query function bit it could not help.

Solution:

Here is the screenshot of the Sheet1:

Here is the screenshot of the Sheet2:

I suppose that you already have data in Sheet1,

And now in Sheet2 you have to insert the following formulas:

At Cell B1:

=UNIQUE(Sheet1!B:D)

At Cell E1:

=TRANSPOSE(UNIQUE(Sheet1!E2:E))

At Cell E2:

=if(isna(FILTER(indirect("Sheet1!F2:F");indirect("Sheet1!E2:E") = indirect("E1"); indirect("Sheet1!B2:B") = B2;indirect("Sheet1!C2:C") = C2;indirect("Sheet1!D2:D") = D2));"";FILTER(indirect("Sheet1!F2:F");indirect("Sheet1!E2:E") = indirect("E1"); indirect("Sheet1!B2:B") = B2;indirect("Sheet1!C2:C") = C2;indirect("Sheet1!D2:D") = D2))

After inserting the above formula in Cell E2 you have drag it down up to the required row in Column E.

At Cell F2:

=if(isna(FILTER(indirect("Sheet1!F2:F");indirect("Sheet1!E2:E") = indirect("F1"); indirect("Sheet1!B2:B") = B2;indirect("Sheet1!C2:C") = C2;indirect("Sheet1!D2:D") = D2));"";FILTER(indirect("Sheet1!F2:F");indirect("Sheet1!E2:E") = indirect("F1"); indirect("Sheet1!B2:B") = B2;indirect("Sheet1!C2:C") = C2;indirect("Sheet1!D2:D") = D2))

After inserting the above formula in Cell F2 you have drag it down up to the required row in Column F.

And at Cell G2:

=if(isna(FILTER(indirect("Sheet1!F2:F");indirect("Sheet1!E2:E") = indirect("G1"); indirect("Sheet1!B2:B") = B2;indirect("Sheet1!C2:C") = C2;indirect("Sheet1!D2:D") = D2));"";FILTER(indirect("Sheet1!F2:F");indirect("Sheet1!E2:E") = indirect("G1"); indirect("Sheet1!B2:B") = B2;indirect("Sheet1!C2:C") = C2;indirect("Sheet1!D2:D") = D2))

After inserting the above formula in Cell G2 you have drag it down up to the required row in Column G.

And similarly if you have more columns then you have to repeat this task.

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself I will try to help you out.

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan,

iGoogleDrive