Breaking column pairs in Excel onto separate rows

Chris Z

I am in need of Excel assistance, due to an RE bug. I have spoken with support about the RE issue, and the simple solution is to install the patch that is out there, alas I cannot.


But to explain, I using RE 7.96 patch 8, which has the "address processing" bug. So my ability to have corporate contacts exported onto separate lines in my Excel output is broken. It likely won't be until summer that we can install the update for our ticketing software that will allow us to the ability to upgrade RE to the next patch which resolves the issue.

 

So, I'm faced with exporting all my contacts onto a single row with many columns, and then figuring out a way to make the columns "transpose" onto separate rows.

 

I can't use the transpose function in Excel, because that is going to flip the axes, and I don't want that because it will make a mess of my data.

 

I will have 6 columns that need to be duplicated onto each row (ConsID, Company Name, Add1, City, State, Zip). Then I will have pairs of columns that will need to trigger the move down (Name & Contact Type) onto a new row. There may be multiple contacts using the same type.

 

Some organizations may have 1 contact and won't need this, but others that have 10 contacts and will need it 9 times.

 

Given a list of about 1,000, this is very tedious to do manually. Hoping that someone can suggest a solution for Excel.  Is there something like a partial transpose in Excel?

Joshua Bekerman bCLO bCRE

If you have and know Access it would be a better option than Excel.


If not, I have done something similar in Excel, still manual but not one at a time.  Sort by the second set of contact columns (Contact 2 Name?), then copy the 6 columns (consID, company name, etc) along with the two contact columns.  Then paste these 8 columns at the end of your list, in columns A-H, so they match up.  Then repeat for contact 3 columns, contact 4 columns, etc.  Lather. Rinse. Repeat.  


That's all I got.  Hopefully someone else has a more automated way to do this.

 

Aaron Rothberg

Hi Chris Z‍ 


I'm not familiar with that specific bug and I may not completely understand the obstacle you are trying to overcome, but can you not use a query to solve this problem?




 

James Andrews

I think I asked this over on fb, but what's the bug?


I mean, if you reeeaaaallly wanted to you could write a VBA script in excel that would loop through each line, count how many columns were populated after your initial six, divide that number by two, insert the resulting number of lines below, copy the six columns into the new rows, and then move the pairs of columns down to each line. 


You could probably even find someone over on an excel or VBA forum to write it for you.  (I am not volunteering because I'm not that good with VBA.) But it seems like a relatively straightforward set of For loops and Do Until loops using Offset to figure out where to copy or cut and paste things.


 

Phillip Bicknell

There's lots of clever Excel stuff on the C Pearson site:
http://www.cpearson.com/Excel/ExcelPages.aspx

A resource that I've had to dip into a few times over the years.

 

Chris Z

Come to find out, the error related to Address Processing, only occurs when I run a new export or do a "Save As..." for an old one.


If I simply attach a query to the export that was working, it will still work without causing an error.


Unfortunately I didn't know this, but happened to talk to a co-worker who didn't know there was an error, and she explained she got it to work.


So, I won't have to mess around with VBA or Access or manual editing of the list.  I'm breathing a sigh of relief!


Thanks,

Chris

James Andrews

Messing around with that stuff is pretty fun though...