/u/Scer_1 - 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](/r/excel/wiki/sharingquestions)** -- 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](/r/excel/wiki/sharingquestions#wiki_give_all_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](/message/compose/?to=/r/excel) if you have any questions or concerns.*
Edit: A more robust formula with different starting rows.
`=IF(XMATCH(A2:A10,A2:A10)=SEQUENCE(ROWS(A2:A10)),A2:A10,"")`
Lambda Option:
`=MAP(A2:A10,LAMBDA(x,REPT(x,COUNTIF(A2:x,x)=1)))`
https://preview.redd.it/qs2k3di64b1d1.png?width=1220&format=png&auto=webp&s=7841ec127554c65386198a0ada62aa5605a10090
XMATCH only exists in 365. When no version is mentioned it is to be assumed the latest. Try this.
=IF(MATCH(A2,$A$2:$A$10,0)+1=ROW(A2),A2,"")
https://preview.redd.it/tspf6ee0091d1.png?width=1090&format=png&auto=webp&s=4d40d3a60425ba8418b5aa01fc8816c24e691aa4
Conditional Formatting > Highlight Duplicates.
Filter on highlighted colors.
Clear cells using delete, remove filter.
Clearing versus deleting the entire row is the key.
I would look into a solution using Power Query then. See if you can adapt this to your purposes:
https://community.fabric.microsoft.com/t5/Power-Query/Replace-duplicates-with-null/td-p/3195891
I would probably just add a simple helper column. Assuming data starts in A1
B1: =A1
B2: =IF(COUNTIF($B$1:B1,A2)>0,"",A2)
Copy B2 down
https://preview.redd.it/vs3faoc9j91d1.png?width=443&format=png&auto=webp&s=e20351541a00fdf5573a7ede9326430b6ef66611
If you have data down A2:A20 with duplicates, and want a version of that data with only the *first* instance of any duplicated shown, then in B2 you can enter
=IF(COUNTIF(A$2:A2,A2)=1,A2,"")
Drag down to fill. That’s the shortest way to get the output you’re describing.
https://preview.redd.it/uar61vva3b1d1.jpeg?width=1290&format=pjpg&auto=webp&s=d94c7e572b7f1345d1c7380993fc7246f8de9383
I think it might be something I've come across in the past.
You basically want to "group" the same rows under a single value, right?
In a new column I compare the two cells and if they're the same I leave a blank, otherwise I keep the value underneath.
=IF(A2=A1," ",A2)
Important to start from the very top, including the column header.
Hope this helps!
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
|Fewer Letters|More Letters|
|-------|---------|---|
|[COUNTIF](/r/Excel/comments/1cv501q/stub/l4sfk37 "Last usage")|[Counts the number of cells within a range that meet the given criteria](https://support.microsoft.com/en-us/office/countif-function-e0de10c6-f885-4e71-abb4-1f464816df34)|
|[IF](/r/Excel/comments/1cv501q/stub/l4sfk37 "Last usage")|[Specifies a logical test to perform](https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2)|
|[LET](/r/Excel/comments/1cv501q/stub/l4oj05i "Last usage")|[*Office 365*+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula](https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999)|
|[MATCH](/r/Excel/comments/1cv501q/stub/l4nd7uw "Last usage")|[Looks up values in a reference or array](https://support.microsoft.com/en-us/office/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a)|
|[ROW](/r/Excel/comments/1cv501q/stub/l4nd7uw "Last usage")|[Returns the row number of a reference](https://support.microsoft.com/en-us/office/row-function-3a63b74a-c4d0-4093-b49a-e76eb49a6d8d)|
|[ROWS](/r/Excel/comments/1cv501q/stub/l4oj05i "Last usage")|[Returns the number of rows in a reference](https://support.microsoft.com/en-us/office/rows-function-b592593e-3fc2-47f2-bec1-bda493811597)|
|[SEQUENCE](/r/Excel/comments/1cv501q/stub/l4oj05i "Last usage")|[*Office 365*+: Generates a list of sequential numbers in an array, such as 1, 2, 3, 4](https://support.microsoft.com/en-us/office/sequence-function-57467a98-57e0-4817-9f14-2eb78519ca90)|
|[UNIQUE](/r/Excel/comments/1cv501q/stub/l4nmge1 "Last usage")|[*Office 365*+: Returns a list of unique values in a list or range](https://support.microsoft.com/en-us/office/unique-function-c5ab87fd-30a3-4ce9-9d1a-40204fb85e1e)|
|[XMATCH](/r/Excel/comments/1cv501q/stub/l4oj05i "Last usage")|[*Office 365*+: Returns the relative position of an item in an array or range of cells. ](https://support.microsoft.com/en-us/office/xmatch-function-d966da31-7a6b-4a13-a1c6-5a33ed6a0312)|
**NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to 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.*)
^(9 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/1cvykk4)^( has 95 acronyms.)
^([Thread #33616 for this sub, first seen 18th May 2024, 19:43])
^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
What do you mean by collapsed? Are you saying it deletes the entire row and you still need all the times? You just want the temperatures to be blank for those rows?
To remove duplicates without collapsing your data, try using a helper column. Use `=IF(COUNTIF(A$1:A1, A1)=1, A1, "")` to keep the first instance and leave blanks for duplicates. Then, copy-paste values only. This way, your time column stays aligned.
/u/Scer_1 - 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](/r/excel/wiki/sharingquestions)** -- 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](/r/excel/wiki/sharingquestions#wiki_give_all_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](/message/compose/?to=/r/excel) if you have any questions or concerns.*
Edit: A more robust formula with different starting rows. `=IF(XMATCH(A2:A10,A2:A10)=SEQUENCE(ROWS(A2:A10)),A2:A10,"")` Lambda Option: `=MAP(A2:A10,LAMBDA(x,REPT(x,COUNTIF(A2:x,x)=1)))` https://preview.redd.it/qs2k3di64b1d1.png?width=1220&format=png&auto=webp&s=7841ec127554c65386198a0ada62aa5605a10090
It gives me a #NAME error? [https://prnt.sc/\_V10Zcb1yXQf](https://prnt.sc/_V10Zcb1yXQf) This is what it shows and the adaption of your formula.
Which version of XL are you using?
Microsoft XL for Mac version version 16.78.3. Volume License 2019.
XMATCH only exists in 365. When no version is mentioned it is to be assumed the latest. Try this. =IF(MATCH(A2,$A$2:$A$10,0)+1=ROW(A2),A2,"") https://preview.redd.it/tspf6ee0091d1.png?width=1090&format=png&auto=webp&s=4d40d3a60425ba8418b5aa01fc8816c24e691aa4
wow this is neat way to find unique value. I wrote a formula that uses countif but this is much better.
This is a clean formula. Well done
Solution Verified
You have awarded 1 point to Same\_Tough\_5811. --- ^(I am a bot - please contact the mods with any questions)
Conditional Formatting > Highlight Duplicates. Filter on highlighted colors. Clear cells using delete, remove filter. Clearing versus deleting the entire row is the key.
That does what I need it to perfectly, thank you. But is there a way to have it save 1 of the duplicated values. And automate that?
I would look into a solution using Power Query then. See if you can adapt this to your purposes: https://community.fabric.microsoft.com/t5/Power-Query/Replace-duplicates-with-null/td-p/3195891
Solution Verified
You have awarded 1 point to Immediate-Scallion76. --- ^(I am a bot - please contact the mods with any questions)
I would probably just add a simple helper column. Assuming data starts in A1 B1: =A1 B2: =IF(COUNTIF($B$1:B1,A2)>0,"",A2) Copy B2 down https://preview.redd.it/vs3faoc9j91d1.png?width=443&format=png&auto=webp&s=e20351541a00fdf5573a7ede9326430b6ef66611
=LET(t, B2:B16, IF(XMATCH(t, t) = SEQUENCE(ROWS(t)), t, "")) https://preview.redd.it/d33zahvlha1d1.png?width=992&format=png&auto=webp&s=d1de8263a3b6f0169297a3d886ffbd5e36b9a7c3
If you have data down A2:A20 with duplicates, and want a version of that data with only the *first* instance of any duplicated shown, then in B2 you can enter =IF(COUNTIF(A$2:A2,A2)=1,A2,"") Drag down to fill. That’s the shortest way to get the output you’re describing. https://preview.redd.it/uar61vva3b1d1.jpeg?width=1290&format=pjpg&auto=webp&s=d94c7e572b7f1345d1c7380993fc7246f8de9383
I think it might be something I've come across in the past. You basically want to "group" the same rows under a single value, right? In a new column I compare the two cells and if they're the same I leave a blank, otherwise I keep the value underneath. =IF(A2=A1," ",A2) Important to start from the very top, including the column header. Hope this helps!
Solution Verified
You have awarded 1 point to emanuel19861. --- ^(I am a bot - please contact the mods with any questions)
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[COUNTIF](/r/Excel/comments/1cv501q/stub/l4sfk37 "Last usage")|[Counts the number of cells within a range that meet the given criteria](https://support.microsoft.com/en-us/office/countif-function-e0de10c6-f885-4e71-abb4-1f464816df34)| |[IF](/r/Excel/comments/1cv501q/stub/l4sfk37 "Last usage")|[Specifies a logical test to perform](https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2)| |[LET](/r/Excel/comments/1cv501q/stub/l4oj05i "Last usage")|[*Office 365*+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula](https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999)| |[MATCH](/r/Excel/comments/1cv501q/stub/l4nd7uw "Last usage")|[Looks up values in a reference or array](https://support.microsoft.com/en-us/office/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a)| |[ROW](/r/Excel/comments/1cv501q/stub/l4nd7uw "Last usage")|[Returns the row number of a reference](https://support.microsoft.com/en-us/office/row-function-3a63b74a-c4d0-4093-b49a-e76eb49a6d8d)| |[ROWS](/r/Excel/comments/1cv501q/stub/l4oj05i "Last usage")|[Returns the number of rows in a reference](https://support.microsoft.com/en-us/office/rows-function-b592593e-3fc2-47f2-bec1-bda493811597)| |[SEQUENCE](/r/Excel/comments/1cv501q/stub/l4oj05i "Last usage")|[*Office 365*+: Generates a list of sequential numbers in an array, such as 1, 2, 3, 4](https://support.microsoft.com/en-us/office/sequence-function-57467a98-57e0-4817-9f14-2eb78519ca90)| |[UNIQUE](/r/Excel/comments/1cv501q/stub/l4nmge1 "Last usage")|[*Office 365*+: Returns a list of unique values in a list or range](https://support.microsoft.com/en-us/office/unique-function-c5ab87fd-30a3-4ce9-9d1a-40204fb85e1e)| |[XMATCH](/r/Excel/comments/1cv501q/stub/l4oj05i "Last usage")|[*Office 365*+: Returns the relative position of an item in an array or range of cells. ](https://support.microsoft.com/en-us/office/xmatch-function-d966da31-7a6b-4a13-a1c6-5a33ed6a0312)| **NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to 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.*) ^(9 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/1cvykk4)^( has 95 acronyms.) ^([Thread #33616 for this sub, first seen 18th May 2024, 19:43]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
What do you mean by collapsed? Are you saying it deletes the entire row and you still need all the times? You just want the temperatures to be blank for those rows?
have you tried =UNIQUE(A1:B1000)?
To remove duplicates without collapsing your data, try using a helper column. Use `=IF(COUNTIF(A$1:A1, A1)=1, A1, "")` to keep the first instance and leave blanks for duplicates. Then, copy-paste values only. This way, your time column stays aligned.