Compare rows and display duplicates in a separate column

**sans** · 11-03-2011, 10:45 AM

Hello,

I have a list of 7 numbers C4:I4, and I'd like to compare each row with its previous row and display the duplicates in another column separated by a dash.

So, C4:I4, will be compared to C3:I3
C3:I3 will be compared with C2:I2...

Duplicates
08 09 29 40 45 58 59
01 15 21 29 32 67 63 - 29
12 13 27 31 38 54 55 - 0
04 21 33 38 45 68 69 - 38
21 04 33 37 40 67 70 - 21-33
08 20 27 32 41 54 63 etc
04 21 19 20 22 39 70
01 15 21 29 32 67 63

Thank you in advance

Sans

**Andrew-R** · 11-03-2011, 10:59 AM

A quick bit of VB needed for this one, I think, in the form of a UDF.

Please Login or Register  to view this content.

Put this in a module and you can then, say, use the function =ListDuplicates(C4:I4,C3:I3) to compare rows 3 and 4.

**NBVC** · 11-03-2011, 11:01 AM

At J2:

Please Login or Register  to view this content.

copied down. That leaves blank cells where no matches found.

If you have to have a zero, then:

Please Login or Register  to view this content.

**sans** · 11-03-2011, 11:23 AM

Thank you very much both for your help!!! NBVC, I don't know how you find me every time.

Is it possible to get a zero instead of a blank whenever there is no match?

Again, thank you!

**snb** · 11-03-2011, 11:29 AM

Please Login or Register  to view this content.

**NBVC** · 11-03-2011, 11:31 AM

Originally Posted by sans

Thank you very much both for your help!!! NBVC, I don't know how you find me every time.

Is it possible to get a zero instead of a blank whenever there is no match?

Again, thank you!

I added that formula to my last thread above....

**sans** · 11-03-2011, 12:12 PM

Exactly what I was looking for!!! Thank you all for your replies help!!

**NBVC** · 11-03-2011, 12:13 PM

You are welcome.

**snb** · 11-03-2011, 12:15 PM

or

Please Login or Register  to view this content.

**sans** · 11-04-2011, 07:13 AM

Hello,

I don't know if this requires a separate thread but is it possible to do a small variation of the above? To also be able to display any duplicates (two or more) in a range? For instance,

01 02 29 32 63 63 - get 63
01 01 63 63 20 15 - get 1-63
01 01 01 63 63 15 - get 1 -63

So essentially display any numbers that appear twice or more in a range.

Thank you in advance,
Sans

**snb** · 11-04-2011, 07:19 AM

If you try to understand the code you can easily do that yourself:

Please Login or Register  to view this content.

**NBVC** · 11-04-2011, 08:18 AM

Or with formula... (not as intuitive a change) assuming first row is in C2:H2:

Please Login or Register  to view this content.

copied down.

**sans** · 11-04-2011, 08:26 AM

Thank you for your reply. I am only just starting to understand formulas, so at the moment trying to create a code to do a particular function is extremely confusing. I have only been using excel since I became a member to the forum.

About my previous post, the duplicates I'd lke to display do not rely on whether they matched any numbers from the previous row. So in a range of C4:I4 there are 7 numbers and only if in this range there are any duplicates display them.

For example:

20-21-22-24-24-25-25 - display 24-25
10-12-13-14-15-16-16 - display 16
33-33-33-34-34-35-35 - display 33-34-35

I see that in the code two ranges are used that's why I am asking.

Thanks again
Sans

**NBVC** · 11-04-2011, 08:27 AM

See my formula above.

**sans** · 11-04-2011, 10:51 AM

Thank you for your replies. Exactly what I was looking for. NVBC, I modified your formula in post number 3# before you replied and attempted to do it myself and got somewhat similar results

Please Login or Register  to view this content.

The only difference is if in a range of numbers there is two of the same i.e. 23-24-25-26-26-27 I get 26 but if there are three of the same numbers i.e. 23-24-25-26-26-26 I get 26-26-26. Can't really understand why

Thank you all for your help!

**NBVC** · 11-04-2011, 12:17 PM

I you sure you are using my last formula:

Please Login or Register  to view this content.

With that formula and your values: 23-24-25-26-26-26 separately in C2:H2, I get just 26 (once)

**sans** · 11-06-2011, 05:30 PM

No,your formula works perfectly! What I meant was that I tried to do it myself before you replied but I kept getting that error.

Compare rows and display duplicates in a separate column

LinkBack

Thread Tools

Rate This Thread

Display

Compare rows and display duplicates in a separate column

Re: Compare rows and display duplicates in a separate column

Re: Compare rows and display duplicates in a separate column

Re: Compare rows and display duplicates in a separate column

Re: Compare rows and display duplicates in a separate column

Re: Compare rows and display duplicates in a separate column

Re: Compare rows and display duplicates in a separate column

Re: Compare rows and display duplicates in a separate column

Re: Compare rows and display duplicates in a separate column

Re: Compare rows and display duplicates in a separate column

Re: Compare rows and display duplicates in a separate column

Re: Compare rows and display duplicates in a separate column

Re: Compare rows and display duplicates in a separate column

Re: Compare rows and display duplicates in a separate column

Re: Compare rows and display duplicates in a separate column

Re: Compare rows and display duplicates in a separate column

Re: Compare rows and display duplicates in a separate column

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions