Excel How to Delete Rows with Duplicates in a Specific Column
Problem description & analysis
Below is the data of Excel:
BL/AWB/PRO |
Container |
Booking # |
S318902903 |
1010097800 FRAME |
S318902903 |
S318902903 |
1010097798 PC BLADE |
S318902903 |
S318902903 |
1010097799 BEAM |
S318902903 |
S318902903 |
1010097800 FRAME |
S318902903 |
S318902903 |
1010097795 PC LINK |
S318902903 |
S318902903 |
1010097798 PC BLADE |
S318902903 |
S318902903 |
1010097798 PC BLADE |
S318902903 |
S318902903 |
1010097800 FRAME |
S318902903 |
S318902903 |
1010097798 PC BLADE |
S318902903 |
We are trying to perform distinct by column: find duplicate values in Container column and remove the rows where these values are located. Below is the expected result:
Solution & explanation
Use the SPL XLL plug-in
Write the formula in a blank cell:
=spl("=E(?).group(Container).select(~.len()==1).conj()",A1:C10)
As shown:
Explanation:
Group the table by Container column, get groups that contain only one record, and concatenate the eligible groups.
https://stackoverflow.com/questions/64219953/remove-duplicates-based-on-specific-column-name
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_Desktop/
SPL Learning Material 👉 https://c.scudata.com
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProcDesktop
Linkedin Group 👉 https://www.linkedin.com/groups/14419406/
