Advanced Fast Excel Finger Wizard Master Class - SOLVED

Kabitzy

So I was wondering if anyone sufficiently versed in Microsoft excel could give me a little help.
I have data that I can open in excel that I need to import (ImportData) into another spreadsheet (CompiledData) in a very specific way. IE I need everything in column A, row 2 and below of the ImportData to go to Column A, row 2 of the CompiledData. I need Columns A through F row 1*only* to move to columns B through G row 1. I then have equations that concern specific data that take up H through K row 1 and column B row 2 and below.

Is there any way to make some sort of template for this process or is it too specific to support?

wonderpug

Do you have to do this multiple times or something? If it's just a one-off then it's probably more efficient to just cut & paste things the way you want than to create some kind of algorithm.

Paladin

Couldn't you do in the other sheet A2 = ImportData!A2, B1= ImportData!A1, and write the equations to use ImportData!H1 and ImportData!B2, and copy/paste in the directions you need to go down the row or column?

For instance, copying ImportData!A1 in the B1 cell and pasting it through C1 to G1 would have the info in the G1 cell be ImportData!F1. If you don't have a $ sign before the row or column designation, then it will change based on the location of the additional row or column coordinates you copy to.

Do you already know how to do this and there's a snag, or do you not understand what is admittedly hard to explain in English?

Kabitzy

wonderpug wrote:

Do you have to do this multiple times or something? If it's just a one-off then it's probably more efficient to just cut & paste things the way you want than to create some kind of algorithm.

I have to do this hundreds of times is the reason I ask.

Kabitzy

Paladin wrote:

Couldn't you do in the other sheet A2 = ImportData!A2, B1= ImportData!A1, and write the equations to use ImportData!H1 and ImportData!B2, and copy/paste in the directions you need to go down the row or column?

For instance, copying ImportData!A1 in the B1 cell and pasting it through C1 to G1 would have the info in the G1 cell be ImportData!F1. If you don't have a $ sign before the row or column designation, then it will change based on the location of the additional row or column coordinates you copy to.

Do you already know how to do this and there's a snag, or do you not understand what is admittedly hard to explain in English?

I don't understand is the problem and neither does my professor, so she left it up to me to "figure it out."

wonderpug

Have you worked with recording macros before? You basically hit the record button and it remembers all of the actions you make on a spreadsheet. Next time you want to do the sequence, just play the macro.

You'd have to hit the play button hundreds of times, but it should do the trick. Hopefully someone else will chime in with something more elegant.

Kabitzy

wonderpug wrote:

Have you worked with recording macros before? You basically hit the record button and it remembers all of the actions you make on a spreadsheet. Next time you want to do the sequence, just play the macro.

You'd have to hit the play button hundreds of times, but it should do the trick. Hopefully someone else will chime in with something more elegant.

That's a really interesting solution. I'll give that a go and see how it works!

Dr. Frenchenstein

Macro is probably your best bet. Just be careful because it records EXACTLY what you do. So if you copy over Column A, Row 1 to Row 30, and the next time you need to do Row 31-45, running that macro will copy over rows 1-30 again. IT goes to the same location too, so if you have a second spreadsheet and you need to copy rows 1-15 over to rows 31-45, that macro wont work.

I think Visual Basic is the language macros use, if you know that at all, you may need to tweak it. It's not hard to learn how to code a specific action either, because you can create a macro, record what you are trying to do, save it, then edit it and the syntax is all there for you.

LaOs

You can record a macro using relative referencing. It's a toggle button.

But yes, you must be careful when recording macros because they will do exactly what you do while recording.

ThreeCubed

I'm not here to be helpful, I just wanted to show love for your thread title.

Kabitzy

So the macro works wonderfully! Really speeds up the amount of work I can get done. Thanks a lot, guys.

Margarazzi wrote:

I'm not here to be helpful, I just wanted to show love for your thread title.

I'm glad someone got it!