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

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

tyger0951

Board Regular
Joined
Aug 9, 2014
Messages
103
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
 

tyger0951

Board Regular
Joined
Aug 9, 2014
Messages
103
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
 

ozbod

Board Regular
Joined
Apr 2, 2008
Messages
177
Just to clarify, are all the records 5 rows long, or is it variable as to the number of rows for each record ?
 

cramerbr

New Member
Joined
Sep 4, 2014
Messages
4

ADVERTISEMENT

It can be variable, sometimes there is a website and sometimes there is a fax number
 

ozbod

Board Regular
Joined
Apr 2, 2008
Messages
177
But are there always 5 rows for a record, irregardless of the values in column B ?
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649

ADVERTISEMENT

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.
 

tyger0951

Board Regular
Joined
Aug 9, 2014
Messages
103
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.
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,814
Messages
5,638,492
Members
417,028
Latest member
JFCLUK

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
Top