Help required - Excel formula or Macro - Fill column with data based on cells in a table

Hello

Last night I created a thread with a problem that was going to put me into an early grave, but you wonderful people saved me quite easily.

Seeing as the result was so good I need your help with one other small formula

SEE BELOW POST FOR UPDATED EXAMPLE DATA

Solution provided by Kaper to create tables 2 and 3 (minus the red column)
=COUNTIFS(B$2:B$2000,B2,C$2:C$2000,C2)
and copy it down. now auto-filter everything except 1
you will get table 2

and slight modification:
=COUNTIFS(B$2:B2,B2,C$2:C2,C2)
copy down
and show only values with 1 - you will get table 3 (minus the red column)

What I need your help with is to create the additional column on table 3 that I have highlighted in red.

all it needs to do is list the corresponding point number AND duplicate pair if one exists.

The example data should display what I mean.

Let me know if I am not explaining it clearly

Cheers

Sdak

Refer the attached file for Pivot Table Solution

We have to use vba coding for achieving this one

Is it ok for you?

Or, if for some reason* (for instance: you have to refresh pivot when data changes) you would like to stick to formulas:
leave in column F:

Please Login or Register  to view this content.

and add in coulmn G (in G2):

Please Login or Register  to view this content.

seems long, but has simple construction

Please Login or Register  to view this content.

again - autofilter on column F equal to 1.
(if column A do not have unique values, it will require some more "playing" - probably ROW() function can be used)

*) this is not, that I am against Pivot Tables, I simply love them :-), just want to show other way to skin a cat (the third would be simple VBA macro, the 4th ...).

Macro's are fine mate.

Edit: OK, in the time I posted this Kaper replied, let me have a look and I will reply soon.

Thanks

Column E does not have to be unique. To be more precise: Column A has to had unique numerical values. See the attachment to my post above, there is already a filter applied, you can remove filtering and see all column G values.

well I tried the first step:

if number of B2,C2 pairs=F2 then just E2
else write E2 and "," and content of cell from this column (but below) where B,C are the same and count of pairs (F) is one greater than F2

first is probably clear enough, so about "else part":
the innermost part of formula is:

Please Login or Register  to view this content.

there could be only one row where all 3 criteria ar met:

Please Login or Register  to view this content.

because in every row where

Please Login or Register  to view this content.

F is increased by one (F counts a number of pairs BC above and including given row)
So the sumifs will really not "sum", but return just this one element from column A
(ok, sum of one element is this element anyway - this is why I wrote about numeric values in column A)

I used only part of columns (for instance A3:A$2000 - you noticed mixed addressing?) below current row, because anyway we dont expect meeting column F criteria above, and this way excel will have less data to crunch.


Then it is easier to understand:
MATCH returns position of this element in Column A (again only part of column below current row)
INDEX returns value from column G on this position (OFFSET could be used here as well instead of index - may be would be even a bit faster? I haven't tried).

So let's take the case where you have triplicate ;-) of BC pair (717029,4 7046365,0).

In the bottommost row where BC pair is met (row 12 in my attachment) in G12 you have just the same as in E12 (22)
going up we have row 11 where again the same BC pair showed up so in G11 we have E11&","&G12 (4,22)
going further up we find row 8 when again the BC pair is met so in G8 we have E8&","&G11 (7,4,22)

Similar - row 10 (first from bottom occurence of 717004,7 7046368,4) so only E10 in G10 and going up row 7 with the same pair so E7&","&G10 in G7

And that's it. (I am just to push button "Post Quick Reply", but it wasn't so quick )

Hi Indi_Ra,

I am not using EX 2013, and may be (?) there is option to leave only duplicates and remove unique values.
(of course removing second and next occurence is a snap already in earlier versions).
But somehow I really do not believe*, it will provide automatically list of "reference numbers" for deleted duplicates
Have you had a chance to look in
example data rev 2.xlsx from the sdak post above (third post from the beginning)
column R is the reqired one.

*) Please correct me if I am wrong, I'd have a good point to discuss upgrade with my boss.