1

I need some help with a spreadsheet. I have two spreadsheets. Book1 contains a list of names. Book 2 also contains a list of names. Many of the names exist in both spreadsheets. Book 1 shows users with a specific product license. Book 2 shows users with a specific product license for another product.

I need to compare / merge the two spreadsheets to show who has both product licenses and I am not sure how to merge the two, and line up the two name columns. I will try to clarify with screenshots below.

Book1:

Capture.PNG

Book 2:

Capture.PNG

I would like to see the following:

Capture.PNG

Any ideas?

3 Spice ups
2

Just my opinion, but because the data is vague, I would do this manually. Copy and paste the entries into the spreadsheet you want to be the master, sort them by person and delete the duplicates. This should go relatively quick once you have all the data in one spreadsheet.

3

Thanks Jackal, My example is only to explain what I am trying to do. In reality the spreadsheets have thousands of users so to do this manually would take months.

4

In that case, I have another idea for you. Do you or can you get Microsoft Access?

When you put all of this into a table instead of a spreadsheet, Access has rules that can help you. One of them is, if you set the person column as the unique identifier, it won’t allow a duplicate entry. So you can copy and paste as many items as you want and the duplicates it finds, it will catch and tell you.

I know this may seem a bit excessive, but when working with large amounts of data in spreadsheets, I have always found it helpful to include Access at some point.

5

Sure, I’ll try this out in Access. Thank you.

6

No worries, hit me up if you have any questions. Access/visual basic was my first love, so I enjoy it.

7

You can also use Power Query in Excel if you have Excel 2016. You can pull and query from two separate Excel Spreadsheet. From there, you can then compare.

Another option is Power Pivot. I’ve also used PowerBI. Then create relationships, etc.

8

Oh, my!
Actually, this is a simple task using Excel VLOOKUP.

I will post a formula / solution shortly… but the resulting output may look different, but hopefully better than you expect.

[EDIT] Solution:

  1. Copy the BOOK2 sheet onto TAB2 of the BOOK1 sheet and name the tab: “LICENSE B”.
  2. Sort the License B data in ascending order by user name.
  3. Highlight the License B table and “name” the table “B_LICENSE” using the [Formulas] drop-down selection on the Excel top menu.
  4. Go to the first tab that contains License A info.
  5. Name column c: “License B”
  6. Enter this formula into the cell below the column name:

| =IF(ISERROR(VLOOKUP(A2,B_License,2,FALSE))," - “,VLOOKUP(A2,B_License,2,FALSE))
7. You should be able to copy/paste the formula that I posted.
8. Now copy that formula to the rest of the column (License B)
Excel.JPGI added a “Licenses” row to show the totals:
=COUNTIF(B2,B12,“Yes”)
= COUNTIF(C2,C12,“Yes”)

Of course, the colum number in my totals formula should be the roww containing the last user data.
Additionally, you will want to use the application name instead of “LICENSE A”, LICENSE B”, etc.

|
| - |

9

Gort is right about Vlookup, it will do the job if you want to stay with Excel.

Personally I prefer using Access for things like this. You can link (or import) the 2 spreadsheets to Access and they appear as tables. Then it is easy to create a query to join the 2 tables and pull data in the format you want.

Of course, both options require matching columns to work. If the names are not identical in both spreadsheets they will not be matched up. They also need to be unique.

1 Spice up