Need VBA Macro Assistance - Data in three columns, transpose to row format

[matthewjberry]

All -

I'm looking for a VBA wizard who can help me out with a problem. I have a flat export file (comma delimited) that I need converted into a usable format.

The current format looks like following:

A B C
Joe Phone 400
Joe Email [email protected]
Joe Dept Sales
Sam Phone 401
Sam Email [email protected]
Sam Dept HR
Mike Phone 402
Mike Email [email protected]
Mike Dept IT
Mike Title Manager

I want it to look like:
A B C D E
Name Phone Email Dept Title
Joe 400 joe@ Sales
Sam 401 sam@ HR
Mike 402 mike@ IT Manager

Issues I'm running into:

• Variable list of attributes assigned to a user. There might be a field for "Title" if one if populated, otherwise a user without a title will not have that field listed.
• Transposing the data from vertical to horizontal.

[mehmetcik]

This is interesting.

BRB

Ok try this file.

Please Login or Register  to view this content.

[matthewjberry]

Let me see if I can tweak this to fit my scenario. I appreciate your quick response. I will get back to you once I've hammered away at this for a bit.

For reference, this is what I'm actually working with

_FOXCASEY ALIAS C6693720
_FOXCASEY CIT MWI/p12402522
_FOXCASEY FAXG3 p12402551
_FOXCASEY FAXG3# 2402551
_FOXCASEY GROUP JERICHO
_FOXCASEY GSM 19006273814
_FOXCASEY GSM# 9006273814
_FOXCASEY LOGIN_STAT 70, 2013.09.03 12:29:10 UTC, PHONEMAIL
_FOXCASEY MAILBOX FOXCASEY
_FOXCASEY NAME FoxCasey
_FOXCASEY NOT_ACTIVE 2
_FOXCASEY NOT_LAG 5
_FOXCASEY NOT_MWI.01 VOICE
_FOXCASEY NOT_SHORTLAG 1
_FOXCASEY OUTBEMPTY TRUE
_FOXCASEY PASSWORD 46273652,2013.06.26 12:14:06
_FOXCASEY PIN 282443906,2013.06.10 19:57:43
_FOXCASEY PREFERRED MAILBOX
_FOXCASEY QUOTAUSED 0
_FOXCASEY SMTP [email protected]
_FOXCASEY S_LOGIN_WEB 2013.07.09-14:34:04
_FOXCASEY VM_ANNOUNCE_01 _FOXCASEY_ANN_01_2.pcm
_FOXCASEY VM_ANNOUNCE_09 _FOXCASEY_ANN_09_3.pcm
_FOXCASEY VM_LAST_LOGIN 2013.09.03 12:29:10 UTC
_FOXCASEY VM_LEVEL_VOL 5
_FOXCASEY VM_LOGIN_FAILED 0
_FOXCASEY VM_MOBILITY 19006273814
_FOXCASEY VM_NAME _FOXCASEY_NAME_1.pcm
_FOXCASEY VM_PIN_UPDATED 2013.06.10 19:57:43 UTC
_FOXCASEY VM_PREV_ANN paa=01pba=00pia=00pea=00pah=00caa=00cea=00cia=00cah=00
_FOXCASEY VM_PROTOCOL PHONEMAIL
_FOXCASEY VM_SIMPLE_ANN SAA=0|SIA=0|SEA=0|SBA=0|SAH=0
_FOXCASEY VM_TIMEPROFILE WEEK_PROFILE_BASIC
_FOXCASEY VM_TPS_B_DAY MO=1|TU=1|WE=1|TH=1|FR=1|SA=1|SU=1
_FOXCASEY VM_TPS_B_HOURS 0000-2359
_FOXCASEY VM_USER_OPTIONS @MTU
_FOXCASEY VM_VANITY 36922739
_FOXCASEY VOICE p12402522
_FOXCASEY VOICE# 2402522
_FOXCASEY WEB_FIRST_LOGIN 2013.06.26-08:13:34

[matthewjberry]

mehmetcik -

Excellent macro. I have a follow-up question for you.

How can I add more fields than the four you created? Looking at the macro I see the following code:

• Dim myarray2(4) >> Would I bump up 4 to however many fields I am looking for (ex. 10)?
• 101 For Count = 0 To 4 >> Would I bump up 4 to however many fields I am looking for (ex. 10)?
• myarray2(4) = myarray1(Count, 3) >> For new fields, would I simply add an additional array in the format myarray2(x) where x is the next field number?

I tried to modify these fields but the macro is not respecting the new data.

[matthewjberry]

Nevermind, I found it. I had to modify the range size.

Range("E1:K1").Value = myarray2