[Solved] Split values in column into «uniques» and «duplicates»

[rkwynn.73]

Hello, so I have column A with a list of names, and need to output duplicates into column C and non-duplicates into column D

example22.png (24.62 KiB) Viewed 1034 times

Edit: Changed subject, was Compare values in one columns and output value into other columns  Make your post understandable by others  -- MrProgrammer, forum moderator

[Zizi64]

The Filter feature has an option: "No duplications". You can filter your data such way: every existing value will be appeared once only in the result cell range.

[MrProgrammer]

need to output duplicates into column C and non-duplicates into column D

This attachment provides formulas to accomplish separation of data into two lists:
• Unique values
• Duplicate values

202212241005.ods

(12.85 KiB) Downloaded 98 times

Cells marked in yellow contain formulas which do not match those in the remainder of the column. If you need any additional assistance attach a spreadsheet demonstrating the difficulty (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the spreadsheet itself). I will not help further unless you attach.

[Tutorial] The SUMPRODUCT function
[Tutorial] Ten concepts that every Calc user should know

If this solved your problem please go to your first post use the Edit ☐ button and add [Solved] to the start of the Subject field. Select the green checkmark icon at the same time.

[Villeroy]

Apply filter Duplicate = True or False
Copy filtered range.
Paste filtered range.

[rkwynn.73]

202212241005v2.ods

(11.92 KiB) Downloaded 92 times

In this one, Mom, Dad don't show in the other columns Jo shows in both columns

[MrProgrammer]

In this one, Mom, Dad don't show in the other columns Jo shows in both columns

You have two errors in 202212241005v2.ods.
• In column B your formulas reference range A$2:A$10 but your data extends to cell A13.
• In column C your formulas reference range B$2:B$10 but your data extends to cell B13.
Dad, Mom, and Jo are in rows 11, 12, and 13.

Fix those problems and see if the spreadsheet works better for you. Either set the end of the ranges to row 13 or, if you don't know how many rows you'll have, pick a number like 9999. Read the Ten Concepts tutorial if you don't understand why the formulas use $ in some places. It will difficult to use spreadsheets effectively until you understand the $ syntax.

If this solved your problem please go to your first post use the Edit ☐ button and add [Solved] to the start of the Subject field. Select the green checkmark icon at the same time.