How to delete duplicates in two columns

[TheWeetek]

Hey, may you suggest, how to delete duplicates

so, there will stay just number 4 in A column? thanks

[#2]

@TheWeetek 

In Cell C1, enter the following formula and drag it down-

=COUNTIF(A:A, A1) + COUNTIF(B:B, A1)

1. Select the entire range of data (including the C column).

2. Go to the "Data" tab in the Excel ribbon.

3. Click on the "Remove Duplicates" button in the "Data Tools" group.

4. In the "Remove Duplicates" dialog box, make sure only the Column C is checked. Uncheck other columns.

5. Click "OK."

Now, Excel will remove duplicates based on the values in the C column.

[#3]

@tanayprasadhey, thanks for respond, but unfortunately in this case it does this.

(if there was needed to have the numbers as before, then its not important, its for a situation, where are not predictable, what values will be in each cell.

result

[#4]

@TheWeetek 

There are a few different ways to do this depending on if you go the text-manipulation route or not.  Ideally, BYROW would be able to handle this, but it's limited to returning scalars.

This is my solution:

=LET(
    rows, ROWS(data),
    columns, COLUMNS(data),
    shuffle, LAMBDA(r, c, SMALL(INDEX(data, r, {1, 2}), c)),
    ordered, MAKEARRAY(rows, columns, shuffle),
    UNIQUE(ordered)
)