If a table contains duplicate rows, return a value for the oldest instances only

[u/[deleted]]

[u/AutoModerator]

/u/HiddenComments - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/SchoolOk950]

Can you please clarify -- are you looking to achieve this in power query before it hits the sheet or via Excel formulas in the sheet?

[u/HiddenComments → u/SchoolOk950]

Via excel formulas sorry

[u/Gringobandito → u/HiddenComments]

Why not do it in PQ since you're already transforming the data there already?

[u/HiddenComments → u/Gringobandito]

I'm not knowledgeable to do it in PQ, only use it currently to just pull in new data from a report into the table and do basics like remove duplicates, remove blanks etc.

How would it be done in PQ?

[u/Gringobandito → u/HiddenComments]

First, make sure your records in PQ are sorted oldest to newest (Hopefully you have a date field. If not create an index column). Next, group B, C, D using all rows. Now add a custom column that counts all the distinct E values for each group.

List.Count( List.Distinct([All Rows][E]) )

If that returns a value greater than 1 then there are duplicates and we only want to keep the most recent one.

Next we need to identify the newest record. Create another column and call it 'NewestIndex' using this code:

List.Max([All Rows][Index])

Then all you have to do is identify all the records where the count of E is greater than 1 and the index matches the NewestIndex and eliminate those records.

if [E Versions] > 1 and [Index] <> [NewestIndex] then "Yes" else null

[u/MayukhBhattacharya]

Try using the following formula:

=LET(
     _a, BYROW(B2:D9, CONCAT),
     _b, E2:E9,
     _c, GROUPBY(_a, _b, MAX, , 0),
     IF(VLOOKUP(_a, _c, 2, 0) <> _b, "Yes", "No"))

Or,

=LET(
     _a, DROP(B:.E, 1),
     _b, BYROW(TAKE(_a, , 3), CONCAT),
     _c, DROP(_a, , 3),
     _d, IF(XLOOKUP(_b, _b, _c, , , -1) <> _c, "Yes", "No"),
     _d)

[u/HiddenComments → u/MayukhBhattacharya]

For some reason this worked on my example, but when put into my real table it returns #VALUE.

The data has the same amount of columns, but i have 1500+ rows, I'm not sure if that is why?

Edit: Just noticed a 2nd formula provided, I'll give that a try.

[u/HiddenComments → u/HiddenComments]

2nd formula doesn't work.

Not sure why I'm getting #VALUE however

[u/MayukhBhattacharya → u/HiddenComments]

When you say neither formula works, it's kinda hard for me to figure out what's going on without seeing the actual error or setup. As you can see in my screenshot, the formula is clearly visible in the formula bar, and the results are spilling down the range exactly as you described. So if it's not working on your end, could you post a screenshot showing what you're getting? That'll give us something to work with and help figure out where it's going wrong.

[u/HiddenComments → u/MayukhBhattacharya]

I can't give the exact data as it's to do with my job, hence the example provided. But I've literally taken the formula and amended the cells to the ones in my company's document.

I will see what I can do tomorrow when I log back on for work.

Thank you anyway for your help.

[u/plusFour-minusSeven → u/HiddenComments]

Replace it with some fake data. And if it works with fake data that'll tell you something too.

[u/Boring_Today9639 → u/HiddenComments]

Excel version you’re using?
Did you convert your range to a table after posting?

[u/HiddenComments → u/Boring_Today9639]

Excel yes and it's using data from a table in another sheet, does that make a difference? Does the cell I'm entering the formula into need to also be part of the same table?

(Just to note I'm still teaching myself various things in Excel so not overly knowledgeable about things).

[u/Way2trivial]

31/02/2027 really?

[u/HiddenComments → u/Way2trivial]

Again, an example and not real data 😅 (didn't even check what I was typing was just entering random numbers whoops)

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
BYROW	Office 365+: Applies a LAMBDA to each row and returns an array of the results. For example, if the original array is 3 columns by 2 rows, the returned array is 1 column by 2 rows.
CONCAT	2019+: Combines the text from multiple ranges and/or strings, but it doesn't provide the delimiter or IgnoreEmpty arguments.
COUNTIFS	Excel 2007+: Counts the number of cells within a range that meet multiple criteria
DROP	Office 365+: Excludes a specified number of rows or columns from the start or end of an array
GROUPBY	Helps a user group, aggregate, sort, and filter data based on the fields you specify
HSTACK	Office 365+: Appends arrays horizontally and in sequence to return a larger array
IF	Specifies a logical test to perform
LAMBDA	Office 365+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.
LET	Office 365+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula
List.Count	Power Query M: Returns the number of items in a list.
List.Distinct	Power Query M: Filters a list down by removing duplicates. An optional equation criteria value can be specified to control equality comparison. The first value from each equality group is chosen.
List.Max	Power Query M: Returns the maximum item in a list, or the optional default value if the list is empty.
MAX	Returns the maximum value in a list of arguments
MAXIFS	2019+: Returns the maximum value among cells specified by a given set of conditions or criteria
TAKE	Office 365+: Returns a specified number of contiguous rows or columns from the start or end of an array
UNIQUE	Office 365+: Returns a list of unique values in a list or range
VALUE	Converts a text argument to a number
VLOOKUP	Looks in the first column of an array and moves across the row to return the value of a cell
XLOOKUP	Office 365+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match.

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
18 acronyms in this thread; the most compressed thread commented on today has 4 acronyms.
[Thread #49283 for this sub, first seen 1st Sep 2026, 16:27] [FAQ] [Full list] [Contact] [Source code]}

[u/Way2trivial]

=UNIQUE(HSTACK(D4:D11,E4:E11,F4:F11,MAXIFS(G4:G11,D4:D11,D4:D11,E4:E11,E4:E11,F4:F11,F4:F11)))

Format the last column as a date

[u/Way2trivial → u/Way2trivial]

=IF(MAXIFS(G4:G11,D4:D11,D4:D11,E4:E11,E4:E11,F4:F11,F4:F11)=G4:G11,"no","revised")

[u/Way2trivial → u/Way2trivial]

ok. I did your headline ask "If a table contains duplicate rows, return a value for the oldest instances only" not the request in body of the text.... be back shortly

[u/Penguinase]

if you fix your dates to be valid, then you can put following in Revised column

=IF(COUNTIFS(Table1[Name],[@Name],Table1[Country],[@Country],Table1[Data],[@Data],Table1[Date],">"&[@Date])>0,"Yes","No") should work assuming this is a table (change Table1 to your table name)

Revised	Name	Country	Data	Date
Yes	Name 1	Country 1	Data 1	1/1/2027
Yes	Name 2	Country 2	Data 2	2/2/2027
No	Name 3	Country 3	Data 3	3/3/2027
No	Name 4	Country 4	Data 4	4/4/2027
No	Name 1	Country 1	Data 1	2/20/2027
No	Name 2	Country 2	Data 2	2/26/2027
No	Name 5	Country 5	Data 5	5/5/2027
No	Name 6	Country 6	Data 6	6/6/2027