0

I have never been so frustrated with Excel in my life. I'm coming up against two major deadlines at work, and the only way to get the right info to the right people involves solving the problem below.

I have two workbooks with different metrics for different "clients" in each, and I need to get all of the info into one sheet - can't simply copy/paste the data over because I could have Client A in the first workbook, but not in the second. Also, can't use "Client Name" as the unique identifier between workbooks because one could be "The John Smith Company" and the other could say, "The John Smith Co."

Here is a very simplified example:

Workbook A

  • Client ID
  • Number of Employees

Workbook B

  • Client ID
  • Years as Client
  • Location
  • Client Name

Before you mark me as a duplicate question, know that I have tried the following formulas to no avail:

=VLOOKUP(D2,[othersheet.xlsx]sheet1!$A:$D,2,FALSE)

=IF(ISERROR(EXACT(D2,[othersheet.xlsx]sheet1!$1:$1048576)), "True", VLOOKUP(D2,[othersheet.xlsx]sheet1!$1:$1048576,2,FALSE))

Losing my mind here - Any ideas would be marvelous. Thank you in advance!

ETA:

I'm using Excel 2011 for Mac, v. 14.4.1.

Community
  • 1
  • 1
elle.cache.money
  • 1
  • 1
  • 1
  • 4
  • give us the sheet and column names for the data above, then the answer can refer to the correct columns (will also help check your formulas) – user3616725 Oct 02 '14 at 13:40
  • So in the examples above, I'm trying those two formulas in "[mainsheet.xlsx]sheet1" in an attempt to reference the data in "[othersheet.xlsx]sheet1". Does that make sense? – elle.cache.money Oct 02 '14 at 13:43
  • What exactly is the problem? Your vlookup looks fine. Is it just that it returns #N/A where the data doesn't exist in 'othersheet'? – Dave Oct 02 '14 at 13:56
  • Dave, the problem is that using either of those formulas returns #N/A for everything, even when it shouldn't. I don't know what the exact problem is, hence the question. – elle.cache.money Oct 02 '14 at 14:01
  • neither of the vlookup formulas here are in the correct format - ie referencing a range... `[othersheet.xlsx]sheet1!$A:$D` -> `[othersheet.xlsx]sheet1!$A$1:$D$100` – user3616725 Oct 02 '14 at 14:04
  • I added a range and it still returns #N/A for every single row. – elle.cache.money Oct 02 '14 at 14:18
  • What are your lookup values? Have you got numbers stored as text or any other formatting? Also, have you got any white/blank spaces in your lookup values? – Dave Oct 02 '14 at 14:22
  • Checked the formatting on all the fields, they are accurate. There are no blanks in my lookup values although excel keeps telling me there are... Cannot figure out why that would be the case. – elle.cache.money Oct 02 '14 at 14:38

4 Answers4

0

I think the easiest way for you to do this would be to use

=match(clientID_WorkbookA_CellA2,ClientID_WorkBookB,0) in workbook A to see which ID's are in both lists. Then manually adjust the Client ID's that are the same but typed incorrectly.

Then any that do not match are not in workbook B. Filter for these and append them to the end of your table in workbook B.

Then create a column labelled number of employees workbook B and use a sumif. something like:

=sumif(ClientID_WorkBookA,ClientID_WorkBookB_CellA2,Employees_WorkBookA).

Obviously without seeing the actual spreadsheets it's hard to provide better code but I'm sure you understand the jist.

UPDATE

Worksheet A

In cell E2 put =trim(D2) and drag down

Worksheet B

In cell O2 put =trim(D2) and drag down

In cell C2 put =sumif(O2,sheetA!E:E,sheetA!B:B) and drag down

If there are any errors it means that your client ID in column O doesn't exist in sheet A.

Thomas Almond
  • 30
  • 2
  • 3
  • 10
  • This didn't work for me - The match function didn't even work for some reason although as I sit here, I can visually match the "Client ID" for one of the clients. I don't even know how it's possible that something so simple wouldn't work. Very frustrating but thank you for helping. – elle.cache.money Oct 02 '14 at 14:19
  • could you possibly add a picture of your spreadsheets to your question? I'll update my formula accordingly. Or you could paste your match formula and I can tell you what's wrong. – Thomas Almond Oct 02 '14 at 14:23
  • I really can't share an image of the sheet as the data is proprietary. I can provide mocked up data though... Give me a few minutes and I'll throw some on here. – elle.cache.money Oct 02 '14 at 14:42
  • you should use `=trim()` on your client ID and point the match at the trimmed column. This might fix it. – Thomas Almond Oct 02 '14 at 14:58
  • Thanks Thomas Almond - I need at least 10 reputation on Stack overflow to post pictures, so couldn't add them, but here are the links: Workbook A - http://i973.photobucket.com/albums/ae214/ellemoncada/ScreenShot2014-10-02at104944AM_zps21cdc9aa.png & Workbook B - http://i973.photobucket.com/albums/ae214/ellemoncada/ScreenShot2014-10-02at104933AM_zpsdd9f140b.png – elle.cache.money Oct 02 '14 at 14:59
  • Thanks Thomas Almond - I definitely think you guys are right about the client_id column. Something fishy going on... did =LEN to see if there were inconsistencies in the character count - all client_ids are 17 characters. I am now beating my head off my desk. – elle.cache.money Oct 02 '14 at 15:13
  • `=trim` gets rid of extra spaces after or before your text in a cell, I think this is your problem. Try my update. It should work – Thomas Almond Oct 02 '14 at 15:15
  • Trim didn't work. I think I'll probably try putting my fist through my laptop next. Thanks for the help. – elle.cache.money Oct 02 '14 at 16:40
0

have you tried :
=VLOOKUP(D2,[othersheet.xlsx]sheet1!$A$1:$D$1000,2,FALSE)

user3616725
  • 3,485
  • 1
  • 18
  • 27
  • what answer/error do you get? Also, try: `=if(D2=[othersheet.xlsx]sheet1!A10,TRUE,FALSE)` (where client IDs in [master]D2 and [other]A10 look the same ) and tell us what the result is. – user3616725 Oct 02 '14 at 14:27
  • user3616725 - Tried that, didn't work. No error message, just "FALSE" down the entire column. – elle.cache.money Oct 02 '14 at 14:41
  • that means that your "Client ID"s are not exactly the same on both sheets. leave the vlookup alone for a while. Choose two cells on both seets that look to have the same Client ID in them. put `=if(D2=[othersheet.xlsx]sheet1!A10,TRUE,FALSE)` anywhere on mastersheet (change D2 and A10 to correct references) and remove any spaces etc from the cells with the ID untill the result shows TRUE – user3616725 Oct 02 '14 at 14:46
  • Thanks user3616725 - This must be the problem. I am going to look into that column and try to see why they aren't matching. – elle.cache.money Oct 02 '14 at 15:01
0

When using a Vlookup, the value you are checking must be in the leftmost column of the range you are looking in. Your current formula is attempting to look up a Client ID against the Client Name.

Have you tried doing a Match() against Column D? You can combine this with the Index() function to get what you want:

=INDEX([othersheet.xlsx]sheet1!$B:$B,MATCH(D2,[othersheet.xlsx]sheet1!$D:$D,FALSE))
Dave
  • 1,643
  • 1
  • 9
  • 9
  • This one didn't work out for me either. I've tried matching, and magically, it doesn't work at all - The data must not match, somehow. I have tried trimming, counting characters... doing everything I can think of to understand the issues with the data. Nothing is going to help me now but a bottle of whiskey, I'm afraid. – elle.cache.money Oct 02 '14 at 16:41
0

Unless finding a solution in Excel is a compulsion, a solution may be found through MS Access.

Import both the excel files in to a database in Access, open design view of the QUERY, open both files, link (innerjoin) on customer id, drag the required fields from both the files in to the table below and view the result in database view of the QUERY.

Save with a proper name and close QUERY. Right click the saved QUERY in left side pane; export the file to excel. Excel file is ready.

It is stated that that data is arranged in multiple matrices in the original file. If multiple tables are created in the same worksheet, naming the ranges is a good idea for easy import of data into database. Header and order of data in columns are to be similar for all tables. Multiple worksheets pose no problem, because Access will ask if worksheet is to be imported or a Range is to be imported.

Patrick
  • 5,526
  • 14
  • 64
  • 101
Ashok P
  • 1
  • 1