• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel is ruining my life!

R

RosieG

New Member
Hi Everyone.....hope this post comes out as technologically is out to ruin my life these days. Just got into a new position and it seems like i walked into a world of Excel...i mean i know the basics but never really used it. Now its ruining my life!


Question: I have a list of my employees (500 of them) and i have a spreadsheet already created. Every month we get benefits statements from our carriers that we have to download into excel (which i can do) and then i have to take that data and match it to the data that i have in my benefits reconcilation sheet to reconcile our totals with the carriers.....and/or figure out discrepancies. I don't have the slightest clue HOW to match data from 2 separate spreadsheets, so i can copy the carriers data into my sheet....Help before I hang myself! LOL
 
Sounds like you'll need to be doing some lookup formulas. Thankfully, these are fairly simple to use and can be learned quickly. Chandoo has an excellent article breaking down all the verious functions. Have a read here:

http://chandoo.org/wp/2012/03/30/comprehensive-guide-excel-vlookup/


If you're still struggling, perhaps you could post a sample of your data and we can help you write the formula.
 
Thank you for the quick response Luke! Good lord, i'm still lost. But let me see what I come up with. Thank you!!!
 
No problem Rosie. All of the lookup functions work by first giving the value you want to find, and then somehow stating the range to look at and get values from.

e.g., if this list is in A1:B5 on Sheet2,

[pre]
Code: 
Name	Score
Bob	95
Tom	84
Sue	65
Jim	42
[/pre]
And you had the name Tom in cell A2 of sheet 1, formula would be:

=VLOOKUP(A2,Sheet2!$A$1:$B$5,2,FALSE)
 
@RosieG

Hi!

I'm unable to imagine how Excel could ruin a payroll specialist's life... the last days of a month...

:)

Regards!
 
@SirJB7.....its been ruining my life since the 15th....I have pending benefits reconcilations to do....and its all Excel based. We have running spreadsheets that we retain for historical data, so every month we get the new #'s and create a new spreadsheet....and then take benefits from RBC for example and copy them into the existing spreadsheets. But the spreadsheets are in different order. One is based on employee # and one is based on RBC employee ID. Both are different, only similarities are Employee Names. So have to figure out how to do a vlookup from My spreadsheet, and match it to the RBC one and copy them into or spreadsheet. Do i make sense? LOL
 
Hi, RosieG!


So the right phrase should have been "Excel it's ruining half of my life!", which is an acceptable percentage, but your whole life is a bit exagerated :)

That's why Microsoft created Excel, for ruining up to half of the life... but not more!


Going to the point and focusing, I suppose that you've already done what follows (I guess you didn't but it can wait till the next first 15 days of a month), ... You can jump to the "------------------------------" line, then you answer, and then you read all that you skipped.


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


"-----------------------------------"


And about your question...


Would you please upload a sample file of each involved workbook? In the second green sticky post you'll find uploading guidelines. I realize that because of data sensitivity you can't upload the actual files, but when creating the sample files with dummy values, please take care of matching fields to be set accordingly.


Regards!
 
Hi SirJB7!


You've got quite the jokes....and yes, lets say its ruining half my life, to make you happy. I just came across the site today and will give it a look over and see if anything makes sense. When it comes to Excel, my mind does a "hard shutdown" and refuses to comprehend it...which i'm sure there is some warped logic too thats beyond my understanding. But since i've started....i will learn it.

Thanks for the tips....I'll be sure to bug you again.
 
Hi, RosieG!

You're welcome. And hurry up with your sample files... maybe you start weekend with life level at 60% :)

Regards!
 
OK! Think i got it


https://hotfile.com/dl/161677854/26f7885/Sample_file_RBC.xlsx.html


I have deleted most of the employees and created fake names etc....but keep in mind this is for 500+ employees. So if i want to combine both work sheets and take data from sheet one and match it up with data from sheet 2 with the correct employees, what do i need to do?


Thank you!
 
Hi, RosieG!


File downloaded. Three sheets. "Employee Detail", 7 persons; "Sheet1" 7 persons too, different structure; "Sheet2", a bunch of people.

What's the goal? Check one of the two first against the third one? Try to explain in detail what do you want to achieve.


Regards!
 
Hi, RosieG!


Well, a blind shoot.


Give a look at this updated file:

https://dl.dropbox.com/u/60558749/Excel%20is%20runing%20my%20life%21%20-%20Sample%20file%20RBC%20%28for%20RosieG%20at%20chandoo.org%29.xlsx


I made this:

a) created 4 named ranges so as to make easier to reference ranges: NameList (Sheet2), EmployeeList (Sheet1), EmployeeNameOnlyList (Sheet1), EmployeeFullRowList (Sheet1)

b) all my changes in cyan shadowed cell backcolor

c) sheet "Employee Detail": conditional format to column A, if name exists in NameList (sheet Sheet2) - have to add "JOHN DOE" because none matched... and I told you that!

d) same sheet: added formula in column L to retrieve column 1 (only available) from other list, NameList

e) sheet Sheet1: conditional format to range $A$3:$C$9, if name exists in Namelist

f) same sheet: added formula in column L to retrieve column 1 (only available) from other list, NameList

g) same sheet: added columns U:V to repeat previous values than the name (name in column C, third), so as to reference them as columns 19 and 20 in VLOOKUP statements

h) sheet Sheet2: conditional format to column A, if name exits in EmployeeList (sheet Sheet1)

i) same sheet: added columns B:C to retrieve values from EmployeeList, using VLOOKUP, and added D:E to retrieve values from EmployeeFullRowList, using INDEX/MATCH (VLOOKUP doesn't accept left referencing column numbers -so the U:V added earlier- and INDEX yes -so retrieves 1 and 2-, always searching by column C)


That's all I can do for now. Just advise if any issue.


Regards!
 
Hey There.....

I think I kind of get what you did. However, if I'm trying to match employee name from Sheet 2 for an example, and copying data into Employee Detail sheet, and matching up the right employees, what would that look like?

Employee Detail is what my reconcilation looks like, Sheet 1 is what the benfits companies looks like and I have to match up the same ee's by name to ensure that we both reocncile? Does that make more sense?


Hope your day is going well.


Thank you,

Rosie
 
Hi, RosieG!


I sent your different options and examples as I didn't receive any indication of what actually needed.


I'm not sure if I now understand, let me see:

a) in every search process you have three main items: argument, source and target

- argument = column A of Employee Detail sheet

- source = column A of Sheet1 sheet

- target = highlight argument and retrieve and highlight column L of Employee Detail sheet

b) if so, what's Sheet2 sheet for?


If I didn't get it, please re-send the my uploaded file with an example highlighted manually with the values expected.


Regards!
 
Hi SirJB7,


Its probably how i'm explaining sorry. As you can clearly tell, i'm not familiar with the Excel lingo. Ok, here it goes again. I have 2 sheets, one with internal values and one i get from our benefits providers site. The only similarity between the 2 sheets are employee names. Now i need to create a single sheet holding data from both our spreadsheet and the benefits spreadsheet. But theirs is sorted in a different format and may/may not have all the employees listed that I have. So what I have to do is match the names from their sheet, to mine and move the values over to reflect beside the correct employee name.


https://hotfile.com/dl/161677854/26f7885/Sample_file_RBC.xlsx.html
 
Hi, RosieG!


I resume what I think you intended, and you tell me if I'm right or wrong and you correct my assumption:


You have two sheets (one yours and other from your benefit providers), but there are three sheets in the workbook: which one is yours and which one comes from your benefit providers and which one is redundant?


I assume that yours is Employee Detail or Sheet1, and external is Sheet2.


Regards!
 
Hi There,


OK, so lets disregard Sheet 2 for now. Mine is Employee Detail and Sheet 1 is from the benefits company. They have to be merged so that when your looking across the sheet the right values are showing up for the right employees, and only common ground is their name. So it has to be matched correctly with their name so i can merge sheet 1 values onto employee detail and it gets aligned with the correct employee. IS that better? Sorry for the confusion.


Thank you,
 
Hi, RosieG!


Download again the file from my same previous link.


I renamed the sheets so as to give them more entity: Employee Detail, Benefits (ex Sheet1) and IDoNotExist (ex Sheet2, putative and with unknown parents, still unrecognized).


Match on Employee Detail against Benefits:

a) column A, cyan shadowed highligth for matching name

b) column L, idem for matching name, retrieving it with VLOOKUP function

c) columns M:S, retrieving data with INDEX/MATCH functions (first 7 columns, you can add more by copying formula to column T in advance)


Match on Benefits against Employee Detail:

a) columns A:C, idem previous a)

b) column T, idem previous b)

c) columns U:AA, idem previous d)


Match on IDoNotExist:

1) against Employee Detail:

a) column A, idem previous a)

b) column B, idem previous b)

c) columns C:F, idem previous c)

2) against Benefits:

a) column H, idem previous b)

b) columns I:M, idem previous c)


Warning: This workbook uses 6 named ranges (you can see them in Formula tab, Defined Names group, Names Administrator icon). If you copy formulas into other workbooks, define them accordingly, otherwise it wouldn't retrieve anything. In your last uploaded workbook you didn't worked over my originally sent, that's why this warning.


Just advise if any issue.


Regards!
 
You must log in or register to reply here.
Back
Top