Macro Help Needed for Excel

[TheLostBoy]

I've got a good-sized single sheet workbook (A1:I8851) that I need a fairly complicated (seems like it to me, anyway) macro to get data in line from column to column. Here's what I've got:

Column A is titled "Category", and Column D is "Description". The actual category listings are in merged cells, spanning anywhere from 5-12 rows. Column D has no merged cells, but many cells in D contain data carried over from the cell above it (instead of wrapping the text, for some reason it just inserted into a new cell).

I need a macro to combine each of the multiple rows in D into one single row, stopping and starting over when it reaches the next Category row. That way each category will have a single description line without incorporating parts of the description from the category above or below it. Does that make sense?

 

[KSoD]

Have you looked at Microsoft Office's website on how to create Macros?

http://office.microsoft.com/en-us/help/results.aspx?qu=Create+Macros&sc=9

 

[TheLostBoy]

I have a very basic understanding of macros in terms of what they are, what they do, and how to use them, but I know very little about actually programming them.

I'm trying to learn them as I go, but even a lot of the help files & references I've been able to find are over my head, or so basic as to provide no help.

I know how to record a macro, and I know how to merge & combine cells into a NEW cell =(D1&D2&D3), but I don't know how to combine them in place. I'm sure I could get around that by combining them into a "helper" column, then copying that column & special pasting only the values back into the cell, but I don't know if there's a less tedious solution.

The bigger issue is that I also don't know how, after recording a macro, to specify where the point is to stop combining cells into one & to start over combining in the next range, since the # of cells is variable & the flag is the next set of merged cells in Column A.

 

[KSoD]

You would be better off going to a Excel Specific area like the Windows Newsgroups.

http://www.microsoft.com/office/community/en-us/flyoutoverview.mspx

They will be better at trying to explain to you what to do.

 

[Lexluethar]

Just copy the information into a new colum E. Someone just held down alt while typing, making column D two or more rows deep (not truely taking up two rows).

In row 1 column E type "=" then click row 1 column D. It should copy the information. Then copy and paste that information down the rest of the column.

There really is no reason to use a macro in this instance. Macro's simply record what you type/click and what order you click it in. Very simple method of doing repedative tasks. And in your instance there really isn't a way to record the macro in a way allowing it to know which rows are the categories. I mean there are and i could tell you, but honestly it wouldn't be worth your time. When copying and pasting just get familiar with the ctrl+down arrow on a selection of data to move to the very last cell of data. I realize column E won't have data so you can't use that ctrl function, this is when you press ctrl down on the column that does have data, then hold shift+ctrl+up once you hit the bottom of your categories data. Does that make sense?

If you need any help, or need me to show you just email me. I work in Excel all freaking day. Mike.Luethye (at) terahertzpc.com - obviously replace the at with @.

 

[TheLostBoy]

Thank you much for your help, and offer of further help! I thought maybe I was looking past a really simple solution or something, but I think I just didn't do a very good job of explaining what I'm trying to do. I'd like to attach an example of what I'm working with, but I apparently don't have that ability? I'll try to explain a little better for now:

For example, A8692 is one merged cell, spanning rows 8692 through 8701. Cells D8692 : D8700 (8701 is blank) all contain parts of the item description. The copy/past I did to create the sheet basically took some wrapped text in that column and inserted each new line into a new row instead of wrapping it in a single cell, where in some other cases it did actually wrap the text; the number of cells in column A correlate directly to the number of rows used for the description in D, whether or not there's actually anything in all of the D cells or just some of them.

I need something to recognize that A spans 8692:8701, then combine all D cells within that range (D8692 : 8701) into the top row of the range (D8692).

I've got close to 9,000 rows where the positioning of the data in D is always within the range of merged cells in A, but that number of cells varies as does the cell positions within the column (as in my example, 8701 is blank, but the last row isn't always blank, nor is it always the only blank. Sometimes the 1st row in the range is blank...); Column A has 1,485 entries, each with it's own description that can be pieced together from between 1 to 12 consecutive cells in column D, and I need to get it down to 1 row per category listing.

Hopefully that didn't make things more confusing and helped demonstrate a little better than I've done before why this is a very tedious & time-consuming task to do manually...

If somebody could help me out with attaching my example file I'd be happy to do that, too... (do I need special permission from the Mods?) Sorry for the long post - an attachment would probably help a lot.

 

[TheLostBoy]

Any ideas? I hope that made some sense...

 

[Lexluethar]

I told you to email me at the above address with the attachment and explain what you are wanting to do and I could help you.

Without an excel file to work with or see what you are really trying to do we really can't do much. It is painful trying to explain without an example.

 

[TheLostBoy]

I told you to email me at the above address with the attachment and explain what you are wanting to do and I could help you.

Without an excel file to work with or see what you are really trying to do we really can't do much. It is painful trying to explain without an example.

I'll do that now - sorry for posting again, just didn't know if you really wanted to get that far into it & didn't want to just drop it on you out of nowhere without better explaining the situation since it's more in-depth than I'd apparently let on initially. Thanks again though!

 

[Lexluethar]

I wouldn't have offered if i didn't.