VB Macro for Transposing Data in Excel 2003- small crisis

**jkenf** · 05-20-2013, 03:15 PM

hello everyone, I'm new to this site and without learning much of IT (never took a class), I've learned and installed macros using the web which has been hugely beneficial to my job.
Attached is a spreadsheet I derived from a website (zoominfo). I printed the list of contacts out, ctr-c, ctr-p in excel 2003. I need to upload this spreadsheet into a different CRM online program that only reads excel columns as categories for the contacts.

I know how to use the paste special/transpose fxn, however, i have >16,000 data contacts and cannot possible think I could get through it all by transposing them manually.

Is there a transpose macro which would transpose every 4thline down on column A into respective columns?

HOW IT IS…
A
A1
A2
A3
B
B1
B2
B3
C
C1
C2
C3
HOW I NEED IT TO LOOK
A A1 A2 A3
B B1 B2 B3
C C1 C2 C3

IE: I need to transpose ROWS A1,A2,A3,A4 TO A2->B1, A3->C1, A4->D1...A5->A2, A6->B2, A7->C2, A7->D2, ECT.
Transpose example.xls Transpose example.xls
Attached you'll find a spreadsheet of how the list now looks and how I need it to be transposed (in bold).
Like I said, I've made a VB macro in outlook, but I followed certain steps listed on the website in it's construction along with the coding. If anyone knows the coding for this and could give me general steps on how to create a VBA project feature in how to do this type of transpose, let me know!

Any help on this would be great.

**FDibbins** · 05-20-2013, 03:26 PM

Hi and welcome to the forum

Try this in B2, copied down and across...
=OFFSET($A$1,(ROW($A1)-1)*5+COLUMN(A3),0)

**jkenf** · 05-20-2013, 03:39 PM

no go... this is the screen shot
transpose example 2 with fxn.xls

**AB33** · 05-20-2013, 03:57 PM

Are you using step 4, or 5? You said step 4, but on the output(Bold), you have 5 columns output.
Do you also want to start from row 1, or 2?

**jkenf** · 05-20-2013, 04:04 PM

steps? are you talking about select, copy, paste special, transpose, paste?
the problem is, that column a has multiple 'contacts' so it's contact A, B, C, and their respective info, A1, A2, A3, B1, B2, B3, ect.
I can't select more than A and transpose it into a row becasue row 1 would have A1,A2,A3,B1,B2,B3 info. If you check out the above attchment, you'd have a better idea of what i'm trying to do.
Thanks so much for your help! I never knew I'd get so much feedback.

**FDibbins** · 05-20-2013, 04:05 PM

you need to start your data in row2, not row 1
also, your sample data had (what looked like) 5 rows btw eacxh set of data. If this is not the case, change the *5 to however many rows there are

**AB33** · 05-20-2013, 04:10 PM

On the attached there two codes. One of them always work, but the other one requires a bit of twicking, depending on where the row starts

**jkenf** · 05-21-2013, 12:20 AM

MANY MANY MANY THANKS. This works.

**FDibbins** · 05-21-2013, 12:24 AM

just out of curiosity, which 1 worked?

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.