How to transpose column A to Multiple rows?

[lyanneryan]

Hi there,

I have the following database format in column A in excel 2007:

Number
Name
Name
Exhibitor
City
Country
Descrip
Place
2012
2013
<space>
Number
Name
Name
Exhibitor
City
Country
Descrip
Place
2012
2013
<space>

and so on...


I would like to get it into multiple rows so it would look as follows:

number name exhibitor city country description place 2012 2013
number name exhibitor city country description place 2012 2013

Can anyone help me figure this out? I'm struggling!


Lyanne

[lyanneryan]

[newdoverman]

The easiest way that I know of is to copy your data into Word. Use the Search and Replace to insert tabs between each field entry and have a paragraph mark at the end of each record.

With an actual replica of your data as it really is, I could probably give you very specific directions on how to do this.

[lyanneryan]

I think i need to run a macro on it - however I don't know how to write macros!

[AB33]

Lyanne,
Please attach a sample (Excel book), including your desired result. To attach a sample, go to advance then attachment.

[lyanneryan]

Test Excel Forum Dummy.xlsx

This is an example of the database I am working with at the moment.

[arlu1201]

Try 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 Tools | Macro | Macros
Select a macro in the list, and click the Run button

[arlu1201]

As per your sample file, try this code -

Please Login or Register  to view this content.

[Marcol]

Or this formula approach

In Sheet 2 insert your headers, you can do this by copying the first block then Paste Special > Transpose

Then in A2 this CSE array formula, this must be confirmed with Ctrl+Shift+Enter not just Enter before dragging across and down.

Your profile States 2003 but your file is 2007 or above, the solution is for 2003, it can be simplified for 2007 and above.

[lyanneryan]

Hi Marcol,

Thanks so much for your help. In terms of the formula approach. Could you please break down how you did that? The CSE formula?

[jindon]

VBA

Please Login or Register  to view this content.

[Marcol]

Break down of the CSE array

Please Login or Register  to view this content.

INDEX(IF(Data!$A$1:$A$1000=A$1,ROW(Data!$A$1:$A$1000),""),,) is the part that is the array.

The IF() statement states that if any cell in A1:A1000 = the header (A$1) then return the row number, if not then return a blank "".

So INDEX(the if statement,,) returns a series array of blanks and numbers
As there is only one column in the array we could equally write INDEX(the if statement,,1)

We can then use SMALL() to extract only the row numbers using ROWS($2:2) as an incremental counter.
This is wrapped again in INDEX(desired column, SMALL() gives the row )

If this cell in the data is blank then it will return 0, so to suppress this we use an IF() statement to return "" instead of zero.

With 2003 we can use COUNT(the array) and ROWS() to end the returned results, to shorten the formula rather than use If(ISERROR()), in 2007 IFERROR() does the job.

See this workbook I have added a 2007 solution that returns blanks when the data is blank.