Need to Cross Refernece two sets of data

**105ben** · 02-19-2015, 08:23 AM

Hello,

I have two sets of data, one is a set of numbers which are classed as "allowed". the other is a set of numbers, each of which I need to know whether they are allowed or not, by checking the table.

I have tried doing this using a pivot table, with no luck. A colleague was trying to do it manually, and I offered to help find a quicker method thinking a pivot would work. Now Im stuck, and curious as to how to do it! Could this be done with a pivot, or is there a better way of doing it?

Any help appreciated!

**TMS** · 02-19-2015, 08:38 AM

In a separate column use COUNTIF/COUNTIFS to check if the number in the second set is in the first set.

Assuming the allowed list is in column A and the other list is in column B, starting in row 2, in cell C2 put:

Formula:

Please Login or Register  to view this content.

Regards, TMS

**105ben** · 02-19-2015, 08:47 AM

Great, thanks TMS!

So does A:A mean check the whole A range?

The formula isnt working though, its saying it contains an error, and is pointing to value is true. Any idea why that is?

**105ben** · 02-19-2015, 08:53 AM

Sorted it now, for some reason it was picking up the "Not Allowed" as part of the "Allowed" variable...

For anyone who uses the formula and has the same issue, I had to put " marks around the not allowed first, then around allowed in order to make it work.

**TMS** · 02-19-2015, 09:40 AM

Looks like, when I typed the formula on my iPad, it picked up the wrong double quote. So, if you copied and pasted the formula it didn't like the first double quote. It's more obvious if you zoom in. Sorry about that.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

**105ben** · 02-19-2015, 10:10 AM

No problem!

Thanks for your help! Saved a lot of work!

**TMS** · 02-19-2015, 02:47 PM

You're welcome.

I've edited the formula so that it will avoid any future issues.

Regards, TMS

Need to Cross Refernece two sets of data

LinkBack

Thread Tools

Rate This Thread

Display

Need to Cross Refernece two sets of data

Re: Need to Cross Refernece two sets of data

Re: Need to Cross Refernece two sets of data

Re: Need to Cross Refernece two sets of data

Re: Need to Cross Refernece two sets of data

Re: Need to Cross Refernece two sets of data

Re: Need to Cross Refernece two sets of data

Thread Information

Users Browsing this Thread

Similar Threads

Choosing which field to fill in without circular refernece

Cross reference XY coords of two data sets, find nearest points, and merge associated data

Changing horizontal cell refernece to vertical

[SOLVED] using a formula in a cell refernece?

Bookmarks

Bookmarks

Posting Permissions