Return the value of a duplicate

[MDB1]

I have a spreadsheet that shows a location number, merchant ID and tandem ID. Some location numbers are duplicate and can have multiple merchant ID's (which are unique). Tandem ID's are unique to a location number.

In my spreadsheet, the duplicate location numbers only have a tandem ID for one of the merchant ID's. For example...

location # merchant ID tandem ID
12345 55555 AAAA
12345 66666
12345 77777

How can a write a formula to fill out the 'AAAA' tandem Id for all duplicate locations. The logic would be if tandem id is blank and location number is more than one, look in an array of the duplicate location numbers and return the corresponding tandem ID.

The huge problem is sometimes the location number with the tandem ID may be the first, middle or last of the duplicates, so I can't just say count up one. Is this possible????

Thanks!!

[Richard Buttrey]

If I understand the request then I'd be inclined to use a helper column

Assuming your data is in A1:C100 then in D2 enter

Please Login or Register  to view this content.

copy this down your range and then copy the helper column and paste it back to column C as values.

[MDB1]

Thank you. This formula does work but only if the tandem ID correlated with the location number is the first instance. Using my original example, if the tandem ID is instead populated for merchant ID 66666, when the vlookup is run, it will return the tandem ID to only the first instance of location ID, which would be merchant ID 55555, thus blank.

Any other ideas?

[Richard Buttrey]

....in that case assuming I understand you should first create another helper column which concatenates the location & merchant ID and use the formula I gave you on that first helper column. If this does not do what you want please upload a workbook wit a few more examples and permutations and manually add the results you expect to see. Please use real data even if its sensitised to a degree. We often find trivial example data like 12345 AAAAA type stuff does not accurately reflect your real data sufficiently to give you a generalised solution.

[MDB1]

I've attached a file. The first 3 columns are given and the 'expected tandem ID' in column D is the intended result of the vlookup formula. Where the tandem ID is blank and the location number is more than 1, I want to lookup the duplicate or other location number and return the corresponding tandem ID. The merchant ID is the unique identifier where the location number may duplicate up to 5 times.

Hope this is clear.

Thanks for any help!

[Richard Buttrey]

Perhaps an array formula (entered with Ctrl-Shift-Enter) in D2 copied down

Please Login or Register  to view this content.

[MDB1]

I am still having issues with this.

I've attached another file that may further help with understanding. I need a formula that uses an array a populates what is in column D (Expected result)if there is a duplicate location number that has a tandem ID. If it does not have a duplicate location number the tandem Id should remain blank as in row 11.

Any help would be greatly appreciated. Thanks!

[Richard Buttrey]

Hi,

If you were to sort your data with column A as the first sort key Ascending and column C as the second Sort key Descending then you could use a trivial VLOOKUP In D2 copied down. e.g.

Please Login or Register  to view this content.

with an unsorted list then a more complex formula such as

Please Login or Register  to view this content.

is needed.

[ChemistB]

Without sorting, this worked for me (not an array)

=IF(C2="",INDEX($C$2:$C$100,MATCH(REPT("z",6),INDEX($C$2:$C$100,MATCH(A2,$A$2:$A$100,0)):INDEX($C$2:$C$100,MATCH(A2,$A$2:$A$100)))+MATCH(A2,$A$2:$A$100,0)-1),C2)

[FlameRetired]

This also worked for me without sorting. Array-entered

Please Login or Register  to view this content.

[MDB1]

Thank you both. Both suggested formulas worked however when I transferred to the real data I found some issues. I guess the generic nature of my example was the issue as suggested by Richard from the beginning.

I've attached a little more detailed file with a few exceptions that occur as well as placed them in the same column headers in my actual report.

Hope this is enough to get a good idea.

Thanks again, you guys were really close!

[FlameRetired]

This is the same array formula applied to the latest upload in column O. It returns the expected results indicated at my end.

Please Login or Register  to view this content.

How is it working with your live data?

Edit My formula is very similar to Richard's post #6. I just noticed. My apologies, Richard.

[MDB1]

This worked!! Thanks to all!!

[FlameRetired]

You're welcome.