Solved

Copy Paste Transpose array by Value

Posted on 2012-03-12
8
193 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
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 how to use longer labels with horizontal bar charts instead of the vertical column chart.

757 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now