need to transpose the data given in B column

[Giri.hb]

Hi Team,

I have data where I want to transpose the data from B column to other columns for the specific ticket id. I have attached the spread sheet, In sheet 1 I gave the actual data and in other sheet I gave what I need. Kindly check and do the needful.

Regards,
Girish

[FlameRetired]

1. This would be simpler if you are OK with filling the blank cells with the ticket numbers. There is a simple and easy method for doing so.
2. Or simpler yet use a helper column to complete the ticket numbers.
3. The alternative is a rather lengthy array formula to return the 'Comments'.

Do you have a preference?

[alansidman]

Try this:

Please Login or Register  to view this content.

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

1. Press Alt-F8 to open the macro list
2. Select a macro in the list
3. Click the Run button

[samba_ravi]

Use helper column as suggested by FlameRetired, which is easy to understand and works well with huge data
otherwise use below formula
B2

Please Login or Register  to view this content.

Copy and paste across

[FlameRetired]

To return the Ticket ids this in A2 and fill down.

Please Login or Register  to view this content.

[Giri.hb]

Hi Flame,

Thank you for your response, I am okay with your 1st and 2nd option given above instead of lengthy array formula. Please assist me on how to do that.

[FlameRetired]

To make a helper column type this formula in C2 of 'Sheet1' and fill down.

Please Login or Register  to view this content.

In A2 use the formula I posted in post #5 to isolate the unique ids.

Use this formula in B2 filled down and across

Please Login or Register  to view this content.

[Giri.hb]

Hi Flame,

Thank you for checking, what I need is the data given in the b column should be transpose as I given in another sheet. Kindly check and assist me

[samba_ravi]

did you checked post no 4

[FlameRetired]

Hi Flame,

... what I need is the data given in the b column should be transpose as I given in another sheet ...

Did you also check the last formula in Post #7?

Both Siva's (Post #4) and Post #7 do what you describe.

[alansidman]

Did you attempt my solution in post #3.