Can you save me hours of work??

[zookeepertx]

I’m working with Excel 2003 (I know, I know. But it’s my work computer, so I’m stuck with it). I’m hoping to save myself several hours of mind-numbing work a couple of times a month, setting up a spreadsheet. Let me mention that I do not “speak” technical-speak; I can generally get done what I want to do, but I have a lot of trouble expressing it (or comprehending it) verbally, so please, bear with me.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
What I have is a spreadsheet that’s 40 columns wide; the first 20 columns are last month’s data (LM) and the next 20 are this month’s data (TM). The number of rows varies every month – anywhere up to 1,100-1,200 rows. The first 3 columns on each side consist of a File #, File Age and Vendor/Company/Department. I have to go through & compare the 2 sides, row by row, looking at the File # and Ven/Co/Dpt. I did Text-To-Columns on the Ven/Co/Dept column to separate out the parts. So, first I need to compare the File # from LM to TM. If they match, then I compare the Ven #s. If those also match, then I compare the Dept #s. (The Co portion is actually irrelevant, by itself). If all 3 columns match exactly, then it’s all good. If either side has a row that the other side doesn’t have, I have to insert a row in all 20 columns of the other side – basically a “place-holder”. The inserted row will only contain the File # and Ven/Co/Dept, but no other data.
<o></o>
When done, both sides of the spreadsheet contain the same number of rows. When a row is inserted, it doesn’t HAVE to get the File-Ven/Co/Dept plugged in, since I’ll have to go back & format things further, anyway. However, I would be in heaven if I didn’t have to go blind spending several hours shoring up all those rows.
<o></o>
Has anybody got any ideas for a macro or something that would help me? One possible problem I thought of is that, once a row has been inserted, moving that side of the data down a row, that’s going to throw off a formula, isn’t it? Unless there’s some way to make the solution look at only 1 row at a time, not moving on until each adjustment has been made.
<o></o>
I know I’ve probably been very confusing, but if I could figure out how to post either a portion of the spreadsheet or a screenshot of it, it’d probably make more sense.
<o></o>
Anyway, I appreciate any help I can get! A co-worker & I have been tossing this around for several months & haven’t come up with a workable solution. Maybe one of you can save me!

 

[kpark91]

Hi, zookeepertx,
it sounds like a really mind-numbing work!

Will all your worksheets be 20 columns and 20 columns throughout?

If so, would you mind telling me where the data actually starts, the row # (excluding the header)
Also, do you mind telling me where TM's data will start? as in the column #.

Thanks

 

[zookeepertx]

Hi there! Fortunately, I only have to do 2 of these spreadsheets per month, but it's enough to make you a zombie!

Yes, the spreadsheet is always the same number of columns. Although, if I break out the Ven/Co/Dept column for easier comparison, that would add a couple of columns.

Before breaking out that column, TM data starts in column R. The data starts with row 8.

 

[kpark91]

Great. i'll get to it then..
I may have to leave soon but I'll try to post it on here later on if I can't. (TGIF)

Other people can take this one as well :P

 

[zookeepertx]

Terrific! I'll be leaving in a couple of hours & won't be back at work till Tuesday. But I may check here over the weekend, just because it would make me SO ridiculously happy if there's actually a way to do this!

 

[kpark91]

I hope the ordering of the data don't matter? (as long as they're all in the corresponding row of information..)

 

[zookeepertx]

Each side is already in order by File #, then Ven/Co/Dept, is that what you're referring to?

 

[kpark91]

I was just asking if the rows could be switched because in order for the algorithm to be faster, I needed to sort the columns. I hope it doesn't matter or it would need ALOT of time to run since it will be checking three conditions

Moreover, the previous month's data span from col A to Q and
present month's data span from col R to AG??? (just to confirm)


Lastly, would it be possible for you to generate sample data for me to test the code (as I've just started working on the algorithm in inserting blank rows..)

 

[kpark91]

Try using this program:
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php

to show the spreadsheet in this forum.
I'm asking quite alot from you.. haha. sorry. I just don't want to generate hundreds of data to see if the code works...

 

[zookeepertx]

Yeah, it wouldn't hurt for the rows to get switched. In fact, I re-sort them a couple of times, anyway.

LM is A-Q and TM is R-AH

That link leads to something that, I gather, I would need to download on my computer? I don't think I'm allowed to do that; I'm pretty sure only our IT people are allowed to download things. I'll check, though. (I don't blame you; I don't even want to LOOK at the hundreds of data, much less CREATE it )

Thank you!