0

I have a data set as follows:

A       |   B       |  C      |...
1abc    |   1def    |  1ghi   |...
2abc    |   2def    |  2ghi   |...
...

This is a collection of memo notes for each account in our database. The 1 in "1abc" represents the account ID and the letters represent some text. There are a total of 177 columns and 1866 rows but not every row has values up to column 177, some may only have two columns worth of data.

I need each column to drop down to the bottom of column A so that all data only occupies Column A. For example, all the data in column B would inserted after the last value in A. Data in column C would be inserted after the last value in column A after it's been populated with the data from B and so on.

In the end, it should look something like this (from which point I can simply sort it in ascending order). 

A       |   B       |  C      |
1abc    |           |         |
2abc    |           |         |
...
1def    |           |         |
2def    |           |         |
...
1ghi    |           |         |
2ghi    |           |         |
...

Can anyone help out with this? I am assuming that I need a macro and have tried working with the solution found in this post but couldn't figure it out. Oh, and I'm using Excel 2003 if that helps.

Community
  • 1
  • 1
btnkh
  • 21
  • 1
  • 7

2 Answers2

0

I am not sure if this will help but, from what you have given me, it sounds like you have 3 columns of data that you would like to be put into one column. If I am correct in my thinking, you can do this a few different ways. So as to preserve the original document in its entirety while adding an extended sorting system you could: 1: Create new tab. Then highlight range a with your mouse. Using your mouse right click and select copy (try to keep in mind the value of the range, E.g. How many items have you copied. Paste it in your now tab. Do this for the remaining ranges. Or maybe use V look up. If you would like I have a schedule module I have created that has many different types of range associations. You can also split sting in excel to divide the id from the sting.

Roman Jordan
  • 1
  • 1
0

Without VBA, add a blank row at the top and a column on the left populated throughout with say x and then follow the steps detailed here. Then delete the left-hand two columns and filter the remaining one to remove any blanks.

Community
  • 1
  • 1
pnuts
  • 58,317
  • 11
  • 87
  • 139
  • Thanks @pnuts! This works great. One problem, excel is limited in the # of rows it will create. But that's okay, i'll break the data up into chucks and work from there. Thanks! – btnkh Jan 27 '15 at 21:28
  • 1
    I have 2013 on my laptop, but keep forgetting to bring it with me to the office. I was able to complete the data cleanup this morning. Thanks! – btnkh Jan 28 '15 at 18:38