Attempting to find duplicate value in a column of cells *backwards*

[philsrule91]

Hello,

I am attempting to search a group of cells backwards to find the last duplicate cell in a range. The following is the formula I've currently constructed:

Please Login or Register  to view this content.

The above works *almost* perfectly. However, it spits out the first matching row (3) rather than the last (24).


J3-J48:

Please Login or Register  to view this content.

Are there any ideas on how to reverse the search here? Any help would be much appreciated. Thank you!

[MarvinP]

Hi Philsrule,

Have you tried to do a conditional formatting in Column J showing duplicates? Why do you need the address of the last duplicate instead of scrolling up from the bottom?

[63falcondude]

Although I'm not sure how this will be useful, this formula will result in the cell address of the last match:

="$J$"&LOOKUP(2,1/(J3:J47=J48),ROW(J3:J47))

[philsrule91]

Hi Philsrule,

Have you tried to do a conditional formatting in Column J showing duplicates? Why do you need the address of the last duplicate instead of scrolling up from the bottom?

Thanks, Marvin. I have used the conditional formatting to show duplicates on another sheet... unfortunately, it is not something that would help in my particular situation. I'm attempting to create an if/then formula based on the last cell in the column that is identical to the referenced cell.

The following is the formula that I've come up with thus far:

Please Login or Register  to view this content.

(I was able to shrink the "cell" formula into a more compact "row" formula after some trial and error.)

The above code would be a great starting point for my needs if it spit out row 24 instead of row 3 and would allow me to continue with more "if"s.

Thanks for the reply!

[protonLeah]

Post a sample workbook. The formula in post #4 does not match the sample data in post #1.
(How to) Upload a Workbook directly to the Forum
(please do not post pictures or links to worksheets)

• Click Advanced next to Quick Post button at the bottom right of the editor box.
• Scroll down until you see "Manage Attachments",
• Click the "Choose" button at the upper left (upload from your computer).
• Select your file, click "open", click "upload"
• Once the upload is completed the file name will appear below the input boxes in this window.
• Close the Attachment Manager window.
• Click "Submit Reply"

Note: Please do not attach password protected workbooks/worksheets
Ensure to disable any Workbook Open/Autorun macros before attaching!

[philsrule91]

Although I'm not sure how this will be useful, this formula will result in the cell address of the last match:

="$J$"&LOOKUP(2,1/(J3:J47=J48),ROW(J3:J47))

Thanks for responding, Falcondude. This may have been an error on my part... I was under the assumption that, at least for this purpose, there wouldn't be a difference in using Excel or Google Sheets... after testing, it looks like this formula works in Excel but results in an error (Did not find value '2' in LOOKUP evaluation.) in Google Sheets. My mistake. If anyone has any idea on how to modify the formula to make it work in Sheets, it would be greatly appreciated. If you guys would prefer not to troubleshoot this particular issue as it's Sheets and not Excel, I completely understand and can mark this thread as solved, as the solution for Excel was posted...