Tranpose data

I have following values in Column A

Company Name: ABC
Products: XYZ
product category : Cosmo
Use: Cleaning
Application : Personal
Company Name: ABC1
Products: XYZ1
product category : Agri
Use: Pesti
Application : Field


I want to convert this dat in to multiple column with respective value i.e

CompanyName Products product category Use Application
ABC Xyz Cosmo Cleaning Personal
ABC1 XYZ1 Agri Pesti Field

Hoping to receive some help


Thanks

Is the entire data in column A? For e.g. Is Company Name: ABC in column A? Or is it split - Company Name: in column A, ABC in column B?

have you tried copy, then special paste...? There is a transpose option in it.

You can not simply transpose as you want to break the column into records rather than a row.

You can use formula to index the column of data to produce a matrix.

=TRIM(SUBSTITUTE(INDEX($A$2:$A$11,(5*(ROW()-ROW($B$1)-1)+((MOD(COLUMN()-COLUMN($B$2)-1,5)+1)))),C$1&":",""))

The TRIM and SUBSTITUTE functions will remove the field prefixes.

Use this code

Please Login or Register  to view this content.

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose View | Macros
Select a macro in the list, and click the Run button.