Transpose data in rows separated by common text

[HC1987]

Hi all,

I was wondering if it is possible to transpose data in rows, where each entry is separated by the same line of text. The problem for me is that each data entry contains different amount of rows for each company so cannot simply separate at every e.g. 6th row... Therefore, I need a macro that can recognize the standard line of text as a que for a new transposition. There is no spacing between the rows.

My data looks like this:

company name
address
phone
email
web
STANDARD LINE OF TEXT ("Biotech therapeutics")

I hope someone can help

 

[SteveO59L]

and how would you like your data to look after transposition?

 

[HC1987]

and how would you like your data to look after transposition?

Like this preferably (in separate columns):
company 1 address phone email web STANDARD LINE OF TEXT (could be deleted)
company 2 address phone email web STANDARD LINE OF TEXT (could be deleted)

 

[SteveO59L]

in one cell, or multiples?

 

[MAKHDOOMLIAQAT]

upload your file in simple data[/]

 

[HC1987]

upload your file in simple data[/]

I can't find out how to upload my file?

But what I would like to do is get the data below (which is in separate rows):
1) company name
2) address
3) phone
4) email
5) web
6) STANDARD LINE OF TEXT ("Biotech therapeutics")

Into separate columns (separate cells)
A B C D E F G
company name address phone email web STANDARD LINE OF TEXT


Row 7 is the next company name and so forth. As mentioned the problem is that there is not 6 rows for each company, so cannot use a simple separation by amount of rows, but need to use the STANDARD LINE OF TEXT, which comes before each company name (except for the first)

Does that help?

 

[hiker95]

HC1987,

Welcome to the MrExcel forum.

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

2. Are you using a PC or a Mac?

To start off, and, so that we can get it right on the first try:

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 a small screen shot (NOT a graphic, or, picture, or, flat text) try one of the following:

MrExcel HTML Maker
If you do not know how to install and how to use HTML Mr.Excel Maker
https://www.youtube.com/watch?v=JycvgGppxt0&feature=youtu.be

Excel Jeanie
Download

Borders-Copy-Paste
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045

To test the above:
Test Here


Or, you can upload your workbook to (the BLUE link-->) Box Net ,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.

 

[HC1987]

Hi again,

I'm using Windows 7 Profesional, Service Pack 1, Excel 2013, on a PC.

Thank you for instructions, here's a link to my workbook with two entries and example of the desired transposition: https://app.box.com/s/3dfaucqjb91my4e3wiwdx7znt981j0l8

 

[hiker95]

Hc1987,

When you respond to your helper, please use their site ID/username/handle.

This will keep thread clutter to a minimum and make the discussion easier to follow.


Thanks for the workbook.

each data entry contains different amount of rows for each company so cannot simply separate at every e.g. 6th row...

Your earlier descriptions of your raw data set, do not match your actual raw data, and, the results you are looking for?

1. Do your raw data groups in column A always contain 8 rows?

2. Do you want the results in the same worksheet, in column E, beginning in row 1?

 

[HC1987]

hiker95,

Thanks - I'm not an experienced forum-used so good to get the etiquette right

Basically, the most important for me is to get the company names extracted from row A (there's about 100) and I thought the easiest way to do that would be to transpose the data so I get all the company names alone in one column.

ad question 1: The raw data groups in column A does not always contain 8 rows, which is the problem for me.
ad question 2: I would prefer to have the result in the same worksheet, beginning in row 1, but the column doesn't matter. However, I would like to keep the original data as is, so not column A preferably.