Excel help needed.

[Wisconsin Beth]

Ok, every year I have to export a list from a DB and edit 1 row of numbers.  We use them as seniority numbers.  People retire and quit and basically we're weeding out their name and reassigning their number to whomever's next in line.  However, we restart every rank at #1.  So more than 1 person will be #1.  We use these seniority numbers in OT hiring and to line people up for VAC picking and a few other things. 

So, I've exported my original list in Excel from the DB and pulled out the personnel that don't have/use seniority numbers in this sense.  And got rid of a bunch of fields that are not pertinent right now.
I've rec'd the list of updated seniority numbers in Excel  from Admin, who keeps track of them.

I have a couple of unique to user key fields that I can use to match up the 2 separate Excel spreadsheets (currently housed in separate workbooks)  HOW THE FLAMING HELL DO I ACTUALLY DO THIS?  I tried googling merging excel and that's not helping me any. 

I'm on Office 2013, if that matters. 

Once this is all done, I get to import this back into the DB.  Alternately, I can just spend the rest of the week manually keying these in...  And I need to have this done by end of day Thursday.  I've been trying with varying degrees of desperation since last week. 

[Wisconsin Beth]

Mods, if there's a better place for this, feel free to move it. 

[Wisconsin Beth]

Or if anyone's got a link for "Excel for the Stupid", I'll take that too.

[The Captain]

The Index with match command should work.  Vlookup should work as well, but it's more constricting.  Warning - using a command like index or sumif between two separate workbooks takes a lot of memory if working with big files.

Now, are the unique identifiers between the two spreadsheets identical as in no spaces?  That's always something to look out for.  The commands won't work unless the fields are identical.

answers.microsoft.com/en-us/office/forum/office_2003-excel/comparing-2-spreadsheets-in-a-workbook-matching/8b0a6897-0f51-4cd8-b480-d844ad8775fa?auth=1

Hope this helps.  Please let me know if it works.

[ArchietheDragon]

Can you use a vlookup?

[Wisconsin Beth]

Where do I find the Index with match command?  Sadly, I'm not kidding about the Excel for the Stupid part. 

Maybe I'm just explaining/thinking this all wrong.  I need to update 1 row from from spreadsheet to anther spreadsheet.  I have names and 2 other unique identifiers in both spreadsheets.  I'd prefer to use the names because the DB export spreadsheet's cells are formatted as text and the Admin spreadsheet is formatted at general. 

[Wisconsin Beth]

I have to use Excel to import the updated file back into the staffing DB.  If I don't use Excel, I have to manually do the updates, which is a pain because it's kind of buried in the personnel files of this program and based on my history, it's a 2-3 day task.  (For what it's worth, our vendor thinks we're damn stupid to use this particular field this way.)

 I found a vlookup video on You Tube.  It's from 2012 - it should be close enough, right? 

[ArchietheDragon]

Sept 23, 2015 10:22:02 GMT -5 Wisconsin Beth said:

Where do I find the Index with match command?  Sadly, I'm not kidding about the Excel for the Stupid part. 

Maybe I'm just explaining/thinking this all wrong.  I need to update 1 row from from spreadsheet to anther spreadsheet.  I have names and 2 other unique identifiers in both spreadsheets.  I'd prefer to use the names because the DB export spreadsheet's cells are formatted as text and the Admin spreadsheet is formatted at general. 

With Vlookup you can use the name from one spreadsheet, look it up in the other spread sheet and then return a value in a cell on that row back to the first spread sheet..   Copy that formula down to the bottom of your table (make sure to use '$' appropriately in the formals) and you will be done. 



make sure both spread sheets are sorted in alphabetical order by name. 

[The Captain]

Beth - the second example in the link in my post tells you how to write the command.

Vlookup will work as well, you just need to be more careful with how you write it.

Can you give us the layout of your files, for example

Spreadsheet #1

Column A = name (unique identifier)

Spreadsheet #2

Column D = name (same unique identifier)
Column E = field/data I want to pull into spreadsheet #1

Here's how vlookup works:

support.office.com/en-us/article/VLOOKUP-function-0BBC8083-26FE-4963-8AB8-93A18AD188A1

[Wisconsin Beth]

Export from DB column headings - Column A is not relevant, it comes up first and I didn't bother to delete it.  But I can.

Column A:Key
Column B: Person
Column C:Job Title
Column D: Op #
Column E: Employee ID
Column F: Payroll ID


Admin column headings -

Column A: RANK
Column B: NAME
Column C: PENSION_NO
Column D: FMIS_ID
Column E: SENIORITY

Person=name
job title=rank
OP #=seniority (this the field I need to edit)
Employee ID=pension no
Payroll ID=FMIS ID (this is a 6 digit number that MUST retain the leading zeros for import)

the employee ID/Pension no and the Payroll ID/FMIS ID are the fields I mentioned before - I have to have them as text to import back in and Admin sent them as general cells. 

[ArchietheDragon]

Sept 23, 2015 10:40:55 GMT -5 Wisconsin Beth said:

Export from DB column headings - Column A is not relevant, it comes up first and I didn't bother to delete it.  But I can.

Column A:Key
Column B: Person
Column C:Job Title
Column D: Op #
Column E: Employee ID
Column F: Payroll ID


Admin column headings -

Column A: RANK
Column B: NAME
Column C: PENSION_NO
Column D: FMIS_ID
Column E: SENIORITY

Person=name
job title=rank
OP #=seniority (this the field I need to edit)
Employee ID=pension no
Payroll ID=FMIS ID (this is a 6 digit number that MUST retain the leading zeros for import)

the employee ID/Pension no and the Payroll ID/FMIS ID are the fields I mentioned before - I have to have them as text to import back in and Admin sent them as general cells. 

You got this. 

[bean29]

Beth,

I can not help with your specific question, but I was approved for a Crystal Training Session on Excel at the Milwaukee Courtyard Hotel near the airport Sept 30th. It is $259.  When I called to sign up I learned that the course is based on Excel 2010 vs. the 2013 that we are both using.

The lady told me to google the differences between Excel 2013 and 2010 and when I did I found a UDEMY course.

This part is free:

I think that will put you where I am in the session so you will need to figure out how to restart it.

There are apparently 19 modules.  the whole course is $99, but UDEMY had a sale through yesterday for 10, so I got it for $10.

I think you can still get it for $10:

www.udemy.com/courses/search/?q=excel

It will not give you an immediate answer, but may fix gaps in your knowledge due to updated versions/changes in the software.

I know I myself will spend some time on this in the next month or so.

Good Luck

[Wisconsin Beth]

My office mate walked into me chanting fuck fuck fuck and asked what's wrong.  Then tried to help.  We've managed to do a bastardized version of Sroo's suggestions and it's having fits that one of the key fields isn't a number vs. text or general. 

Once I finish dealing with that, I get to spot check numbers.

AFTER THAT, I get to change a text fields back to number/general.  I think if I set up a new column as number, I can change it via copying that row over instead of screwing around this way.  I know I've had to do that in the past to make things work correctly. 

AFTER THAT, I can import this bastard back into the DB. 


I thank you all for your efforts with this.  Have a virtual round of drinks on me.    and some munchies to go with them. 

[garion2003]

I'm late to the party, but I was going to say VLOOKUP too, I use that one so much these days it's not even funny.

[Wisconsin Beth]

I think (Please God) that the Excel stuff is now done.  Names got broken down correctly, numbers are good, etc.  Damn DB is requiring the name fields in addition to the unique identifier, which I wasn't expecting but I think I've got that fixed via that text to column thing. 

Now I just need to doublecheck on the test bed that this is right and kick everyone off the production server once I'm good.  My System Admin is off until next week so I get 1 shot to do this right.  The DB I'm playing with feeds into our Fire Reporting Software and screwing that up is NOT an option while he's off. 

So again, thank you all for the help and assistance yesterday.