We help IT Professionals succeed at work.

Copy Paste Transpose array by Value

DonCrow
DonCrow asked
on
Medium Priority
233 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
Comment
Watch Question

CERTIFIED EXPERT

Commented:
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)),"")

Author

Commented:
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?
CERTIFIED EXPERT

Commented:
Why don't you post a sample with the not evenly spaced data so that we can have a go at it.

Author

Commented:
ok see attached, thanks again
CS-EE-Transpose-example.xlsx
CERTIFIED EXPERT
Commented:
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

Author

Commented:
I see where its going, I'm adjusting a couple things. Sorry for the slow responses.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.