Solved

Copy Paste Transpose array by Value

Posted on 2012-03-12
8
194 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

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…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

911 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

23 Experts available now in Live!

Get 1:1 Help Now