Finding Duplicate Cell (values) within same row

I have searched for this answer but I haven't been able to find exactly what I'm looking for. I am trying to find and remove duplicates but only within the same row. Example,


8729 1245 9321 8729

4236 1829 8729 7845

3435 1171 3435 1171


There are two similar values of "8729" in Row 1, and one value of "8729" in row 2......I want to remove one of those values in Row 1 without removing the value in Row 2. Basically, remove a value if it's already present in a single row. While at the same time, that value can be present in any/every other row.

I hope that explanation was clear....thank you in advance.

Here is a formula based attempt in case you can't use the VBA solution for some reason.
Using your example, select F1:I1 then enter the following in the formula bar:

Formula:

Please Login or Register  to view this content.

This is an array formula and must be committed with CTRL-SHIFT-ENTER.
With F1:I1 still selected copy then paste to F2:I5. You should now have the following:

noDups.png

There's a couple of possible disadvantages relative to the VBA approach. Firstly the data is not adjusted "in place" and secondly there will be "gaps" in the row where duplicate values have been removed.

@jason.b75

Not adjusted in place? I would say that it is adjusted in place, where as a formula replicates it in another place.

Maybe I could have been clearer, but we're in violent agreement. I said: "There's a couple of possible disadvantages [of my formula based approach] relative to the VBA approach. Firstly the data is not adjusted "in place" [by my formula based approach] " . . .

the gaps produced by the vba are no different to those in the results of your formula.

That happens to be true for the OP's three examples as the duplicates occur in the last column. For my additional example in the 4th row:
| 2 | 2 | 2 | 1 |
You generate:
| 2 | 1 | | |
I generate
| 2 | | | 1 |

In summary, I was trying to say that your VBA solution is superior to my formula based solution I provided my attempt only in case the OP can't use a VBA approach.

frequency() returns a vertical array of values. transpose() converts that vertical array into a horizontal array as the requirement here is to put the result in a single row.

Edited Added data.

Another way. Array entered in F1 (Ctrl + Shift + Enter) filled across and down.

Formula:

Please Login or Register  to view this content.

	A	B	C	D	E	F	G	H	I
1	8729	1245	9321	8729		8729	1245	9321
2	4236	1829	8729	7845		4236	1829	8729	7845
3	3435	1171	3435	1171		3435	1171
4	2	2	2	1		2	1
5	1	2	2	3		2	1	3

@izzy-analytics:

"why does it return a frequency of 0 for the duplicate item?"

I suppose there is a degree of logic in that elements will be assigned to the first eligible bin that is encountered, but this and many other quirks of various functions are not documented by Microsoft and so it's either trial and error or learning from others on sites like this (I know I'm going to study FlameRetired's mode.mult solution!). Hope this helps. Thanks for the rep!

@afarmer83: Thanks for the feedback - glad it works for you!