consolidation of scattered values

[ezdizzy]

I have a large range of data containing values in some but not all of the cells. Is there a fast way to consolidate all of the values into a list?

Sheet1 of the attached file contains an example of the data I have. Sheet2 shows how I want to consolidate the data.

Any help will be greatly appreciated!

Thanks.

[Paul]

Hi Ezdizzy, perhaps the following macro will work for you. Adjust your column range as necessary.

Please Login or Register  to view this content.

[ezdizzy]

Paul--

Thanks so much for the quick reply. Unfortunately, I havae no experience with programming within excel. Do you know if there is a way to accomplish this using the funtions/formulas within excel already?

Thanks again for your help.

[Paul]

There probably are ways using multiple formulas and helper cells, but you don't need experience to run a macro. To add the code to your workbook (a copy, just in case), right-click on the sheet tab with your data and choose View Code. Paste the code I provided into the VB Editor window that appears. Adjust the Range("A:E") to include all of your columns, and change "For col = 2 to 5" to your used column range. (Column B = 2, C = 3, D = 4, E = 5, etc.) Close the VB Editor. Press ALT+F8 to open the Macros dialog window. Select reorgData and click OK/Run.

The code assumes all of the data will be moved into column A, each set below the previous column's set of data.

[ezdizzy]

Ok, I tried what you said, it worked fine with my small example but it is not working with my large actual scenario. A couple more questions:
why is your range ("A:E"), but then the next line of codes shows: For col = 2 To 5 ' columns B to E?
Also, I tried a couple of times, and now I am starting to see an error message with a red "X" and a "400" ?? any ideas?

[Paul]

Try uploading a copy of your workbook here. I can then review it to see what might be happening. You can replace confidential data with fake data as long as the type of data is the same as you would expect to be there normally. (e.g. numbers where numbers would be, dates where dates, text where text would be..)

As for using A:E on one line and then 2 to 5 on the next...

The first line, Range("A:E"), is deleting all blank cells in columns A:E and shifting non-blank cells up so each column contains a list of values with no blanks starting in row 1.. You can make this A:K, C:BR, etc.

The next line, col = 2 to 5, is there to loop through columns B:E. You don't need to loop from 1 to 5, because column A will already be done when the blanks are deleted. You can't loop through columns by letter, e.g. "col = B to E". (Well, you probably could, but would need additional code to handle that.)

So if you adjust the range from A:E to A:J, you would also change "col = 2 To 5" to "col = 2 To 10" since column J is the 10th column.

[ezdizzy]

Paul--

So I went back and saved my workbook as a Macro-enabled workbook (duh!) and the macro worked perfectly. Thanks so much for your help!