Transpose data

cramerbr

New Member
Joined
Sep 4, 2014
Messages
4
I have data that has in cell a1 the text "business name" then cell a2 contains the actual business name, cell b1 has the text "business genre" then cell b2 contains the actual business genre and so on for address, phone number and contact then cell a6 goes back to "business name" for the next business etc etc. I want to create a macro that transposes the whole sheet so that I end up with 5 columns - column 1 has the Business name, column 2 has the business genre etc etc
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Code:
 Sub Test ()


Dim Cell As Range 
Dim DCell 


'Moves Data from cell below to cell adjacent 


For each Cell In Range("A:A")
	If cell.value = "" Exit For  
		Cell.Offset(0,1).Value = Cell.Offset(1,0).value 
		End If  
		
Next   


''Removes Unneed data and rows


For Each DCell in Range("A:A") 


If cell.value = "" exit For 


	If DCell.value = "Business Name"   
		DCell.Offset(1,0).EntireRow.Delete 
	 End If 
	
	If DCell.Value = "Business Genre" 
		DCell.Offset(1,0).EntireRow.Delete
	End iF  


  If DCell.Value = "Address" 
		DCell.Offset(1,0).EntireRow.Delete
	End iF  
  
  If DCell.Value = "Phone Number " 
		DCell.Offset(1,0).EntireRow.Delete
	End iF


  If DCell.Value = "Contact " 
		DCell.Offset(1,0).EntireRow.Delete
	End iF
  
Next   		 
	 
End Sub
 
Upvote 0
My misake not the formatted you requested. I'll rewrite it up later to move into a single table.


Code:
 Sub Test ()


Dim Cell As Range 
Dim DCell 


'Moves Data from cell below to cell adjacent 


For each Cell In Range("A:A")
    If cell.value = "" Exit For  
        Cell.Offset(0,1).Value = Cell.Offset(1,0).value 
        End If  
        
Next   


''Removes Unneed data and rows


For Each DCell in Range("A:A") 


If cell.value = "" exit For 


    If DCell.value = "Business Name"   
        DCell.Offset(1,0).EntireRow.Delete 
     End If 
    
    If DCell.Value = "Business Genre" 
        DCell.Offset(1,0).EntireRow.Delete
    End iF  


  If DCell.Value = "Address" 
        DCell.Offset(1,0).EntireRow.Delete
    End iF  
  
  If DCell.Value = "Phone Number " 
        DCell.Offset(1,0).EntireRow.Delete
    End iF


  If DCell.Value = "Contact " 
        DCell.Offset(1,0).EntireRow.Delete
    End iF
  
Next            
     
End Sub
 
Upvote 0
Just to clarify, are all the records 5 rows long, or is it variable as to the number of rows for each record ?
 
Upvote 0
But are there always 5 rows for a record, irregardless of the values in column B ?
 
Upvote 0
tyger0951,

Welcome to the MrExcel forum.

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?


So that we can get it right the first time:

Can you post a screenshot of the actual raw data worksheet?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs:
1. MrExcel HTMLMaker20101230
https://onedrive.live.com/?cid=8cffdec0ce27e813&sc=documents&id=8CFFDEC0CE27E813!189

Installation instructions here:
http://www.mrexcel.com/forum/board-announcements/515787-forum-posting-guidelines.html#post2545970

2. Excel Jeanie
Download


If you are not able to give us screenshots:
You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.
 
Upvote 0
Windows 7 excel 2010....not my question so no screen shots from me :)

tyger0951,

Welcome to the MrExcel forum.

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?


So that we can get it right the first time:

Can you post a screenshot of the actual raw data worksheet?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs:
1. MrExcel HTMLMaker20101230
https://onedrive.live.com/?cid=8cffdec0ce27e813&sc=documents&id=8CFFDEC0CE27E813!189

Installation instructions here:
http://www.mrexcel.com/forum/board-announcements/515787-forum-posting-guidelines.html#post2545970

2. Excel Jeanie
Download


If you are not able to give us screenshots:
You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.
 
Upvote 0
cramerbr,

Welcome to the MrExcel forum.

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?


So that we can get it right the first time:


Can you post a screenshot of the actual raw data worksheet?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs:
1. MrExcel HTMLMaker20101230
https://onedrive.live.com/?cid=8cffdec0ce27e813&sc=documents&id=8CFFDEC0CE27E813!189

Installation instructions here:
http://www.mrexcel.com/forum/board-announcements/515787-forum-posting-guidelines.html#post2545970

2. Excel Jeanie
Download


If you are not able to give us screenshots:
You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.
 
Upvote 0

Forum statistics

Threads
1,213,553
Messages
6,114,279
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top