Pulling two lists into one, and showing duplicates.

I know it's possible to have lists referenced and show an answer, but is it possible to do so with duplicates, and showing said duplicates?

Attached is a file, all I need to do is reference the ID, and show said duplicates and where they are.

I could use something like this:

=IFERROR(INDEX('Jobs List'!$F:$F,MATCH(1,INDEX(('Jobs List'!$B:$B=$AG$3)*('Jobs List'!$B:$B=$AG$3),0),0)),IFERROR(INDEX(Archive!$F:$F,MATCH(1,INDEX((Archive!$B:$B=$AG$3)*(Archive!$B:$B=$AG$3),0),0)),""))

But the problem is it should show only the first duplicate and ignore the rest.

I know it's possible, I just don't have the skills to do so. If someone can help It be appreciated.

Originally Posted by micope21

Hi

Why not use in conditional formatting/manage rules/ New Rule/Format only unique or dupicate values/dupicate?

If name are different and ID are the same? Both colour will come up.

If name and ID are the same? The yes need countif or row to go with index and match.

Because what is going to be required is that in a field lets say A1 someone is going to type in an ID, and I want the list to self populate. If I could do what you suggested I would, but it's not as easy as that...

Exactly. here, let me show you what I have so far.

Originally Posted by NBVC

Try this:

In I4 add helper formula:

=IF(H4=$C$2,COUNT(I$3:I3)+1,"")

copied down

In L4 add helper formula:

=IF(K4=$C$2,MAX($I$4:$I$7)+COUNT(L$3:L3)+1,"")

then in B7 use formula:

=IFERROR(INDEX(G$4:G$7,MATCH(ROWS(B$7:B7),$I$4:$I$7,0)),IFERROR(INDEX(J$4:J$7,MATCH(ROWS(B$7:B7),$L$4:$L$7,0)),""))

copied to next column and down.

In D7:

=IF(ISNUMBER(MATCH(ROWS(D$7:D7),$I$4:$I$7,0)),$G$2,IF(ISNUMBER(MATCH(ROWS(D$7:D7),$L$4:$L$7,0)),$J$2,""))

copied down

Perfect, thanks again!... how did you ever learn this stuff?

Hit a snag... I'm using this:

=IF($D1=NCMR!$AG$4,COUNT($R$1:R1)+1,"")

I have two duplicates, K11200811 Rev A

It isn't showing it, although as far as I can tell the code is written correctly. Can you take a peek and see where I may be going wrong?

Here is the sheet.

I knew it was something simple like that, thanks... still like to know how you learned all this stuff.

Got a quick question...

=IFERROR(INDEX('Jobs List'!$C:$C,MATCH(ROWS(AG$12:AG12),'Jobs List'!$R:$R,0)),IFERROR(INDEX(K$8:K$11,MATCH(ROWS(C$7:C7),$M$8:$M$11,0)),""))

Is there a way to make it so that it looks only through C3 to the bottom of the sheet, sort of like C$3:C?

Except when there is nothing is the part field, it shows the top information. So if there are description fields it will show said fields.