Formula to find ID numbers in 1000+ records

Hi there.

I have a spreadsheet with a list 915 Master ID numbers. These ID numbers sit in column A of a single tab

In a second tab I have a list over 5000 related Child ID numbers, which correlate directly to the above 915 IDs.
- The child ID numbers sit in column C of the 2nd Tab
- The associated master IDs are referenced in column D of the 2nd tab

I need some simple formulas to tell me of the 915 Master IDs, which ones do not have a correlating child ID record

Any help would be appreciated??

Use COUNTIF, something like

Formula:

=IF(COUNTIF('2nd Tab'!$D:$D,$A2)=0, "no child ID", "")

Regards, TMS

You're welcome.

"a fair few others" where? You could use MATCH, probably with IFERROR for the case(s) where there is no child ID.

You choose



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.

Well,the thing with MATCH is it gives you a row number if there is a match, but #N/A if there isn't. And it's actually the #N/A you would want .... and you can pick that up with IFERROR. BUT you'd need an IF round the MATCH to discard any row numbers it does return. So, not ideal.

Regards, TMS