Offset Comment Locations

[Doug Nguyen]

Dear Excel Forum,

I need some advice cleaning exports from a survey software called MarketSight. The software returns tables as so:

row x: blank cells possibly with a comment for the value below
row x + 1: cells with some value

This repeats over the relevant range (approximately 4000 rows x 20-30 columns). What I would like to do is combine the comments and values.


One obvious way would be to loop through the range and conditionally PasteSpecial comments to the range below, but I have found this to be very slow. I have also considered simply adding comments rather than copy + paste, but I question how much faster this would be. Any tips?


Best,
Doug

[Bernie Deitrick]

I would use a macro that creates an exactly half-sized range of formulas, below the existing table, with a formula like this - let's say in cells A5001:AZ7500, which will add the comment from the cells in the odd rows to the end of the values in the even rows:

=TRIM(INDEX($A$1:$AZ$5000,(ROW(A1)-1)*2+2,COLUMN()) & " " & INDEX($A$1:$AZ$5000,(ROW(A1)-1)*2+1,COLUMN()))

Then copy those cells with formulas, paste values, and delete the original cells. The code with this approach should be much faster:

Please Login or Register  to view this content.

[Doug Nguyen]

Dear Bernie,

Hey, thanks for getting back to me. I am going to look into this formula approach, maybe instead storing it in an array. I am really trying to avoid copy + paste though because of how cumbersome my files can get.

Would you happen to know of some alternatives? Maybe adding comments without looping (or with precise looping)?


Best,
Doug

[Bernie Deitrick]

Looping is always slower - when you add a formula to an entire range, you take advantage of all the power or Excel. That includes the copying and pasting as values. Upload an example file and I will add my suggested macro to it - have you tried to do it yourself, and run the macro?

[Doug Nguyen]

Dear Bernie,

So I have attached a data table sample to give you something to test. When I tried your macro, the empty rows were deleted, but there were no added comments and the numbers were inputted as string. Any ideas?


Best,
Doug

[Bernie Deitrick]

By "What I would like to do is combine the comments and values," I thought you meant "combine the comment string with the value in the cell"

Now that I see the file, this might be how to do it:

Please Login or Register  to view this content.

[Doug Nguyen]

Dear Bernie,

Thanks for all your help. The code above worked and coincidentally had almost the same method as my starting one. This is what I ended up going with:

Please Login or Register  to view this content.

and then adding one of the many "delete every nth row" macros. Again, I appreciate your help with this one. I've asked around quite a bit to figure this out. +rep


Best,
Doug