0

Here is a subset data (all in one column):

Click to select Starcom
Starcom
Marketing and Advertising
Greater Chicago Area
5001-10,000 employees
View accountSaved
Saved Leads (0)
Click to select R/GA
R/GA
Marketing and Advertising
Greater New York City Area
1001-5000 employees
View accountSaved
Saved Leads (0)
Click to select McCann
McCann
Marketing and Advertising
United States
10,001+ employees
View accountSaved
Saved Leads (0)
Click to select DDB
DDB
Marketing and Advertising
Greater New York City Area
10,001+ employees
View accountSaved
Saved Leads (0)

I would like to create a table as such out of it:

+---------+---------------------------+----------------------------+-----------------------+
| Starcom | Marketing and Advertising | Greater Chicago Area       | 5001-10,000 employees |
| R/GA    | Marketing and Advertising | Greater New York City Area | 1001-5000 employees   |
| McCann  | Marketing and Advertising | United States              | 10,001+ employees     |
| DDB     | Marketing and Advertising | Greater New York City Area | 10,001+ employees     |
+---------+---------------------------+----------------------------+-----------------------+

As you can see, I only need the first four rows and then it skips three rows.

So far I have been trying to understand how to do this but can't seem to crack it... been trying to work with OFFSET and INDIRECT functions so far to no avail.

This is a handy thing to know how to do, since some data pastes in this format.

Here is my example spreadsheet URL.

1
  • 1
    Whoops sorry should be now.
    – Drewdavid
    Sep 29, 2017 at 21:37

1 Answer 1

1

Assuming data is in ColumnA, please try in B2, and copied from there across three further columns to the right and then the set of four down to suit:

=offset(indirect("A"&2+7*(row()-2)),column()-2,)

OFFSET
INDIRECT
ROW
COLUMN

1
  • Thanks that worked! Modified it to work for each column. :)
    – Drewdavid
    Sep 29, 2017 at 22:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.