?
Solved

Copy Paste Transpose array by Value

Posted on 2012-03-12
8
Medium Priority
?
209 Views
Last Modified: 2012-11-25
So I have a long list (hundreds) in the below format (spreadsheet attached).  I would like to have a separate column for each field to upload into database.

Whats the best way, VBA loop? or IF than? something else....

Thanks in advance.


Company Name: ABC company

ADDRESS: 490 East
Austin, TX 05050

PH: 000-000-0000

Title:  Janitor

EMAIL: janitor@g-mail.com

ASSISTANT:





Company Name: ABC company

ADDRESS: 490 East
Austin, TX 05050

PH: 000-000-0000

Title:  Janitor

EMAIL: janitor@g-mail.com

ASSISTANT:




Company      Address      Address2      Phone      Title      Email      Asst
Company Name: ABC company      ADDRESS: 490 East       Austin, TX 05050      PH: 000-000-0000      Title:  Janitor      EMAIL: janitor@g-mail.com      ASSISTANT:
CS-EE-Transpose-example.xlsx
0
Comment
Question by:DonCrow
  • 3
  • 3
6 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37711819
If your data is evenly spaced and starting as in the given file then enter this formula in C2 and copy it down and across.

=REPLACE(IF(C$1="Address2",":","")&INDEX($A:$A,(ROW()-2)*17+CHOOSE(COLUMN()-2,2,4,5,7,9,11,13),1),1,FIND(":",IF(C$1="Address2",":","")&INDEX($A:$A,(ROW()-2)*17+CHOOSE(COLUMN()-2,2,4,5,7,9,11,13),1)),"")
0
 

Author Comment

by:DonCrow
ID: 37715682
Thanks ssaqibh, that works perfectly for the evenly spaced ones like you mentioned. Unfortunately they are not evenly spaced and would need to go by the Categories or field value. All of the Field names have a colon at the end. (i.e. Address: or PH:)  Is there a way with out Regex?
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37717070
Why don't you post a sample with the not evenly spaced data so that we can have a go at it.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:DonCrow
ID: 37718071
ok see attached, thanks again
CS-EE-Transpose-example.xlsx
0
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 2000 total points
ID: 37718414
Check this file. It has these assumptions

It uses a helper column B
Data has the exact headings I have entered in row 1
The records are not spaced more than 25 rows apart
Copy-of-CS-EE-Transpose-example-.xlsx
0
 

Author Comment

by:DonCrow
ID: 37723150
I see where its going, I'm adjusting a couple things. Sorry for the slow responses.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

616 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question