Solved

Copy Paste Transpose array by Value

Posted on 2012-03-12
8
196 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
8 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
Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

 

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 500 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
stuck in average-1 12 65
Embed scroll excel chart and data in powerpoint 9 77
V-Lookup 11 19
VBA working with shapes 6 11
A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
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…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

828 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