Excel 2007 help - combining 2 spreadsheets for the total idiot

[aruvqan]

HELP!!!

I have 2 spreadsheets. One is from my vendor. It has a lot of nonsense stuff, but I sort of have to keep it. Unfotunately they refuse to use our ID numbers for locations or even recognize them. My financial database spits out invoices by invoice number, our location ID, check number amount and date of check. I need to add this information to the vendors spreadsheet so I can email it back. The spreadsheet from them is about 8000 lines. My reports span 3 months and have something like 30000 lines, of which I really only need the 8000 invoices.

How do I bash both spreadsheets together so I can add my 5 little columns of info to their 10 or so columns but on the same line?

Up til now, the spreadsheets were much shorter, like 1000 lines. The official way I was supposed to do it was to make a copy of my little spreadsheet, go to the bottom of their spreadsheet, line up my left upper corner with the appropriate cell and paste, so all my data were more or less under theirs and then sort so that for each location there would be 2 lines, their line on top and then almost all the way to the right and lined up with the account number, our line below.

I hate the freaking idea of 2 lines per location, it makes everything 2 times as long as it really is. I want all the data to sort onto one line, and the spare crap that doesnt match up to either go the hell away, or be sorted all to the top or bottom where it can be deleted away without having to go through every freaking line, move the data from my line under their line and paste it to the tail end of their data and then delete the now more or less empty line. THe only data that the 2 sets of info share is the invoice number.

I know this sounds whacked out, and I am probably not describing it too well, but I have been fucking around with microshit excel for many many too many hours now to determine that they figure that their customers will only want to delete duplicate info, not use it as a sorting to be combined item. I am willing to share dummy spreadsheets demonstrating what I am currently doing, and what I need done.

I am waiting for someone to mention macros. What I comprehend about excel macros can comfortably be carved on my littlest fingernail with a 1 inch router. Just handing me a string and telling me that this macro does it is about as useless as tits on mrAru. Interesting to look at but meaningless. I need simple little directions like put data from vendor on spreadsheet 1 put your data on spreadsheet 2 click this button and type in X, click this button and type in Y, bend over and kiss ass goodbye. I have less than an idea on how and where to use macros.

Getting help from someone at work is useless, all they can think of is pivot tables and from what little I can figure out from reading 2 books on pivot tables is that that is about useless for what I want to do. They cant seem to grasp what I actually am doing, and I would really not want to drive the 100 miles round trip to tell the excel whiz exactly what I need which would probably take about 2 minutes in person with a copy of the table and me going and pointing at the numbers and explaining really slowly like for a 5 year old kid exactly what I need.

[Stan_Doubt]

It is not really clear from your post whether there is a common piece of info in both tables (spreadsheets) or how the multiple line sorting operation is supposed to work. If there is a column that can serve as a unique identifier that is common to rows in both tables, you can use the Excel VLOOKUP function. It sounds like “Invoice number” might be one, but all you have said is that the tables “share” this information. It is not clear whether there is a one-to-one or one-to-many relationship with respect to “invoice number”

If it is a one-to-one relationship where your record (a single row with 5 columns of info) and the vendor’s record (a single row with 10 columns of information) have the same unique invoice number, then:

Assuming you’ve got both tables in a workbook, as separate sheets, the vendor sheet has invoice number in column A, and your data have invoice number in column A and both tables have a header row in row 1 and are sorted based on column A, in the 11th column of the vendor’s sheet (cell K2) use (without brackets, replace “MySheet” with your sheet’s name) [=VLOOKUP(A2,MySheet!$A$2:$E$30000,1,FALSE)]. This will be redundant info, confirming that you have the same invoice number being pulled from MySheet. Cell L2 gets [=VLOOKUP(A2,MySheet!$A$2:$E$30000,2,FALSE)], the second column of info from your table, and so on until you have all your data in columns K through O. Excel help has a decent description of how VLOOKUP works. This may not be the best solution, but it works and is straightforward provided that the aforementioned assumptions are met.

If there are multiple records in one of the tables for the same invoice number, i.e., a one-to-many relationship, the VLOOKUP function can still work, but you have to know what you are doing to set it up correctly.

[aruvqan]

well, in teh vendor spreadsheet they have 10 columns, with one of them being the invoice number, and that spreadsheet has 8000 [eight freaking thousand lines] and my 30000 [thirty fucking thousand line] spreadsheet has 5 columns, one of which is the invoice number. I need it turned into one spreadsheet of 8000 lines, with all the columns combined so that I have all the vendor information on the same line as my invoice payment information so I can send it back to the vendor and they can see what check each invoice missing payment information in their system has. I can send them a spreadsheet of every single invoice each month but they refuse to use it because it only has OUR location Id and the invoice number, They insist on having every freaking column of THEIR information with our payment info added.

Currently I take a spreadsheet that has columns a through j, with their inoice number in position j, and then add my 5 columns of invoices so that when I paste my batch at the bottom of theirs the invoice numbers are all in col J. I then sort the whole spreadsheet ascending by col j. That leaves me with a mishmash of full length vendor lines interspersed wiht my half length lines, with some numbers duplicated

a b c d e f g h i j
J K L M N

So I manually either delete the J K L M N lines that do not correspond to their a b c d e f g h i j lines, or if the lines match up, I cut and paste my K L M N line to their a-j line and delete my solitary little J line so that I have a single 8000 line spreadsheet of

a b c d e f g h i j K L M N

This method is sort of how it used to be cone, except they would leave it at

a b c d e f g h i j
F K L M N

with F being the invoice number, and having 2 lines for a single location is sort of absurd, it makes the spreadsheets 2x as large as they need to be.

I was hoping to be told that it was a simple matter of using one of their cute little buttons, but the only cute little button DELETES the duplicates, where I want to easily FIND the duplicates and somehow combine the lines. I could have sworn that there was a way to bash 2 spreadsheets together so that the missing data from the second spreadsheet would automatically ADD itself to the appropriate line. As I said, macros are pretty much useless to me because I have absolutely no freaking idea how they work. Your macro info looks like that Far Side cartoon where we see 2 panels, the human one is the guy telling Biff what a good dog he is and the second one Biff hearing balh blah Biff blah blah. THink of me as Biff. I have absolutely NO idea what you are trying to tell me

[StGermain]

So basically, all you’re wanting to do is add your check number to their spreadsheet, right? So what you need is VLookup.

V Lookup will compare the first column on two spreadsheets. If there is matching data (Inv 1234 and inv 1234, for example), it can paste info from the 2nd column (check number, in your case) to the 2nd column on the other sheet. You may have to temporarily move columns around, but the first thing to do is place the invoice number as the first column in both spreadsheets. On your spreadsheet, make the check number the 2nd column. On their spreadsheet, leave a blank column in row B. Sort both spreadsheets by invoice number, column A for both. Put your cursor in box B2 of their spreadsheet (which should be blank). Go to “Insert”, then “Function”, then choose VLOOKUP. A box will pop up. Click on the red arrow in the corner of the first box (Lookup _value), then highlight all of the data on column A in their spreadsheet. Then Click on the red arrow in the Table_array box. Highlight the data in both Column A and B of your spreadsheet (Inv Number and Check Number). Put 2 in the third box and False in the fourth box. Hit “Okay”. That will tell you if the first item on both spreadsheets matches. Then click the box in the lower right corner of Cell B2, and that’ll autofill your function all the way down. Where there are matches it’ll insert the check number. Where there aren’t matches, it’ll put N/A. When that’s done and you have your values, click the tob of Columb B on their spreadsheet. Hit Copy, Paste Special, Values. If you don’t paste the values, the function won’t work if you resort the invoices. Once you’re done, you can cut and paste the columns back where you need them to be.

I hope this helps. I have a cheatsheet on V-Lookup if you want me to e-mail it to you.

StG

[mhendo]

I hope this doesn’t complicate things too much, but if you have one column of data that is common to each table, couldn’t you import both tables to MS Access, then link them using the invoice number column?

Then, a query looking for matching records would give you a final record set, which could then be exported as a flat file (Excel, CSV, etc.).

[aruvqan]

@mhendo

I hope this doesn’t complicate things too much, but if you have one column of data that is common to each table, couldn’t you import both tables to MS Access, then link them using the invoice number column?

Then, a query looking for matching records would give you a final record set, which could then be exported as a flat file (Excel, CSV, etc.).

If I had a clue, maybe?

To be very honest, the help files for any microshit product seem to assume you actually have a clue. All the suggestions so far seem to sound like you have to still do it line by line instead of 30000+ lines at a single time. I just want a simplish button click that will add my 5 columns of data to their 10 columns of data and have the 2 sets of data end up on the same line. To be freaking honest, it will go through a whole spreadsheet and delete an entire line when it finds a duplicate datum in 2 lines in a single spreadsheet. [it will keep one copy of the line with invoice 1234565 in it, instead of combining the 2 lines that have invoice 1234565 in it but moving the stuff in the full cells into the empty corresponding cells.]

I just want to have columns 1 through 15, with columns 1-10 the vendors data and 11-15 my data. Without essentially adding my data to their line line by freaking line in an 8000 line spreadsheet, then deleting the 22000 or so lines from my database that dont have corresponding invoices.

[aruvqan]

@StGermain

So basically, all you’re wanting to do is add your check number to their spreadsheet, right? So what you need is VLookup.

V Lookup will compare the first column on two spreadsheets. If there is matching data (Inv 1234 and inv 1234, for example), it can paste info from the 2nd column (check number, in your case) to the 2nd column on the other sheet. You may have to temporarily move columns around, but the first thing to do is place the invoice number as the first column in both spreadsheets. On your spreadsheet, make the check number the 2nd column. On their spreadsheet, leave a blank column in row B. Sort both spreadsheets by invoice number, column A for both. Put your cursor in box B2 of their spreadsheet (which should be blank). Go to “Insert”, then “Function”, then choose VLOOKUP. A box will pop up. Click on the red arrow in the corner of the first box (Lookup _value), then highlight all of the data on column A in their spreadsheet. Then Click on the red arrow in the Table_array box. Highlight the data in both Column A and B of your spreadsheet (Inv Number and Check Number). Put 2 in the third box and False in the fourth box. Hit “Okay”. That will tell you if the first item on both spreadsheets matches. Then click the box in the lower right corner of Cell B2, and that’ll autofill your function all the way down. Where there are matches it’ll insert the check number. Where there aren’t matches, it’ll put N/A. When that’s done and you have your values, click the tob of Columb B on their spreadsheet. Hit Copy, Paste Special, Values. If you don’t paste the values, the function won’t work if you resort the invoices. Once you’re done, you can cut and paste the columns back where you need them to be.

I hope this helps. I have a cheatsheet on V-Lookup if you want me to e-mail it to you.

StG

Please email it to me - I am pretty sure that my email is in my profile, otherwise just pm me=)

Will it do 5 columns if I leave the 5 first columns empty? I have 5 columns of data I need to add to teh spreadsheet…

[aruvqan]

@StGermain

Put your cursor in box B2 of their spreadsheet (which should be blank). Go to “Insert”, then “Function”, then choose VLOOKUP.

StG

Maybe I am spectacularly blind, but in wincrap excel 2007, there is no vlookup. Under the insert tab, I have pivot table, table, picture, clip art, shapes, smart art, column, line, pie, bar, area, scatter, other charts, hyperlink, text box, header and footer, wordart, signiture line, object and symbol.

The sub tab names are tables, illustrations, charts, links and text.

[aruvqan]

@StGermain

So basically, all you’re wanting to do is add your check number to their spreadsheet, right? So what you need is VLookup.

V Lookup will compare the first column on two spreadsheets. If there is matching data (Inv 1234 and inv 1234, for example), it can paste info from the 2nd column (check number, in your case) to the 2nd column on the other sheet. You may have to temporarily move columns around, but the first thing to do is place the invoice number as the first column in both spreadsheets. On your spreadsheet, make the check number the 2nd column. On their spreadsheet, leave a blank column in row B. Sort both spreadsheets by invoice number, column A for both. Put your cursor in box B2 of their spreadsheet (which should be blank). Go to “Insert”, then “Function”, then choose VLOOKUP. A box will pop up. Click on the red arrow in the corner of the first box (Lookup _value), then highlight all of the data on column A in their spreadsheet. Then Click on the red arrow in the Table_array box. Highlight the data in both Column A and B of your spreadsheet (Inv Number and Check Number). Put 2 in the third box and False in the fourth box. Hit “Okay”. That will tell you if the first item on both spreadsheets matches. Then click the box in the lower right corner of Cell B2, and that’ll autofill your function all the way down. Where there are matches it’ll insert the check number. Where there aren’t matches, it’ll put N/A. When that’s done and you have your values, click the tob of Columb B on their spreadsheet. Hit Copy, Paste Special, Values. If you don’t paste the values, the function won’t work if you resort the invoices. Once you’re done, you can cut and paste the columns back where you need them to be.

I hope this helps. I have a cheatsheet on V-Lookup if you want me to e-mail it to you.

StG

Rereading this, I dont think it can be done, I have 5 columns of crap I need to add, not just 1. I am not really interested in doing it 5 freaking times, one for each column of data to add. Sorry=( guess I am stuck with a true jack ass of a fucking useless prgram that the designers didnt have a clue about making user friendly. Sorry to waste everybodies time.

[Johnny_L.A]

I’d be interested in seeing a sample, if anyone would care to post it.

I’m not an Excel guru (indeed, I’ve asked a bunch of Excel questions here!) so I wouldn’t be able to help; but I’d like to understand the problem and see the solution.

FWIW, this is how I do my job:

Before I started with the company, Excel files were opened and then ‘cleaned up’ (names and addresses fixed, formatting, etc.) by moving things around manually. A lot of our data can be sent to its destination as an Excel file. But new data must be in a defined .txt format. There are two things I do: I run the data and output the fixed format file with several additional Constant fields, or I do the ‘clean-up’ on the more odious files by matching the previous month’s data with the current month’s data. I’ll describe the latter.
[ul][li]Open the Excel file and change all commas to spaces[/li][li]Save the Excel file as comma delimited (.csv)[/li][li]Do the same for the previous month’s file (which should already not have commas, but I check anyway)[/li][li]Import both files into Access, defining all fields as Text[/li][li]Export, fixed-format, Advanced button, defining starting positions and lengths[/li][li]I’ve written an Easytrieve program (sorta-kinda a simpler version of COBOL, but not really) that sorts both files. Run the two files through that.[/li][li]Run another Easytrieve I wrote that compares the two files using the a/r numbers as keys (IF MATCHED [use the pre-existing name and address information and write to Matched file], ELSE [write the unmatched record to Not_Matched file])[/li][li]Open both files in Excel, using fixed-width[/li][li]Clean up the non-matched records[/li][li]Add the unmatched records to the ‘clean’ records and sort[/li][li]Save the file and send it off[/li][li]This becomes the next ‘previous file’[/ul][/li]It saves a lot of time when you have thousands of records, four address fields that need to be two, reversing surnames and given names, etc. And of course I have code that counts how many records are and aren’t matched, I can delete records that I know will be deleted, and so on.

But if I can do the same thing without the Access and Easytrieve steps and just do it in Excel, then I can show other people how to do it.

And no, I don’t know vb or OO or any of the other programming that can be used in Excel.

[StGermain]

aruqvan - You should have e-mail. Even if you have to do the VLookup 5 times, it should take 10 minutes at max to add the extra five columns. If you send them to me, I can do it and return it to you.

StG

[aruvqan]

@StGermain

aruqvan - You should have e-mail. Even if you have to do the VLookup 5 times, it should take 10 minutes at max to add the extra five columns. If you send them to me, I can do it and return it to you.

StG

Got it and replied, many thanks. I am going to actually play with it tomorrow, just taking a break this evening from the whole situation [though I am down to only 4830 more invoices sigh ]

[aruvqan]

@Johnny L.A.

I’d be interested in seeing a sample, if anyone would care to post it.

I’m not an Excel guru (indeed, I’ve asked a bunch of Excel questions here!) so I wouldn’t be able to help; but I’d like to understand the problem and see the solution.

FWIW, this is how I do my job:

Before I started with the company, Excel files were opened and then ‘cleaned up’ (names and addresses fixed, formatting, etc.) by moving things around manually. A lot of our data can be sent to its destination as an Excel file. But new data must be in a defined .txt format. There are two things I do: I run the data and output the fixed format file with several additional Constant fields, or I do the ‘clean-up’ on the more odious files by matching the previous month’s data with the current month’s data. I’ll describe the latter.
[ul][li]Open the Excel file and change all commas to spaces[/li][li]Save the Excel file as comma delimited (.csv)[/li][li]Do the same for the previous month’s file (which should already not have commas, but I check anyway)[/li][li]Import both files into Access, defining all fields as Text[/li][li]Export, fixed-format, Advanced button, defining starting positions and lengths[/li][li]I’ve written an Easytrieve program (sorta-kinda a simpler version of COBOL, but not really) that sorts both files. Run the two files through that.[/li][li]Run another Easytrieve I wrote that compares the two files using the a/r numbers as keys (IF MATCHED [use the pre-existing name and address information and write to Matched file], ELSE [write the unmatched record to Not_Matched file])[/li][li]Open both files in Excel, using fixed-width[/li][li]Clean up the non-matched records[/li][li]Add the unmatched records to the ‘clean’ records and sort[/li][li]Save the file and send it off[/li][li]This becomes the next ‘previous file’[/ul][/li]It saves a lot of time when you have thousands of records, four address fields that need to be two, reversing surnames and given names, etc. And of course I have code that counts how many records are and aren’t matched, I can delete records that I know will be deleted, and so on.

But if I can do the same thing without the Access and Easytrieve steps and just do it in Excel, then I can show other people how to do it.

And no, I don’t know vb or OO or any of the other programming that can be used in Excel.

Thankfully each bit of datum is in its own cell. i would go insane if it wasnt!

[Johnny_L.A]

@aruvqan

Thankfully each bit of datum is in its own cell. i would go insane if it wasnt!

Oh, the stuff I work with is in their own cells. (Most of it, anyway. I can Text-to-columns those that aren’t.) The problem is that they’re in their wrong own cells. The good news is that the Easytrieve save a LOT of work. The bad news is we only have one license, and nobody else is interested in learning how to use it in any case. (Not that I’m complaining!) But if there was a way to match the two files only in Excel and replace the data that needs it, then my boss and coworker can save time too.