List unique values from two lists

W

William Daniels

New Member
Joined
Aug 23, 2011
Messages
8
I am trying to find a way to compare two rows of data and list the values in one row that do not appear in the other row. Below shows the data I am working on. I want to compare the values in Row 18, Columns D thru S with values in Row 25, Columns D thru S. I want the resulting numbers to appear in a single cell separated by commas. For the data shown the results in the cell would be 10,15,16.


There are some blanks in the rows indicated as b.



Columns D E F G H I J K L M N O P Q R S
Row 18 1 2 3 4 b b b[FONT=&quot][/FONT] b 9 10 11 12 13 14 15 16
There are rows between the two.
Row 25 1 9 12 b 2 7 11 b 3 8 13 b 4 9 14 b

I am using Excel 2007 and have tried to use the INDEX and MATCH functions, but I can’t get the solution. I would appreciate any help you can provide.

William Daniels
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
welcome to MrExcel Board

i wish if this help you
<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th><th>Q</th><th>R</th><th>S</th><th>T</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">10</td><td style="text-align: right;;">15</td><td style="text-align: right;;">16</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td><td style=";">b</td><td style=";">b</td><td style=";">b</td><td style=";">b</td><td style="text-align: right;;">9</td><td style="text-align: right;;">10</td><td style="text-align: right;;">11</td><td style="text-align: right;;">12</td><td style="text-align: right;;">13</td><td style="text-align: right;;">14</td><td style="text-align: right;;">15</td><td style="text-align: right;;">16</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style="text-align: right;;">25</td><td style="text-align: right;;">1</td><td style="text-align: right;;">9</td><td style="text-align: right;;">12</td><td style=";">b</td><td style="text-align: right;;">2</td><td style="text-align: right;;">7</td><td style="text-align: right;;">11</td><td style=";">b</td><td style="text-align: right;;">3</td><td style="text-align: right;;">8</td><td style="text-align: right;;">13</td><td style=";">b</td><td style="text-align: right;;">4</td><td style="text-align: right;;">9</td><td style="text-align: right;;">14</td><td style=";">b</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D15</th><td style="text-align:left">{=IFERROR(<font color="Blue">INDEX(<font color="Red">$D$18:$S$18,SMALL(<font color="Green">IF(<font color="Purple">1-ISNUMBER(<font color="Teal">MATCH(<font color="#FF00FF">$D$18:$S$18,$D$25:$T$25,0</font>)</font>),COLUMN(<font color="Teal">$D$18:$S$18</font>)-COLUMN(<font color="Teal">$D$18</font>)+1</font>),COLUMNS(<font color="Purple">$D$15:D15</font>)</font>)</font>),""</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 
Upvote 0
To return the resulting numbers in a single cell separated by a comma, try the following custom function...

1) First place the following code in a regular module (Alt+F11 > Insert > Module > Copy/paste the code > Alt+Q)...

Code: 
[font=Verdana][color=darkblue]Function[/color] AConcat(a [color=darkblue]As[/color] [color=darkblue]Variant[/color], [color=darkblue]Optional[/color] Sep [color=darkblue]As[/color] [color=darkblue]String[/color] = "") [color=darkblue]As[/color] [color=darkblue]String[/color]
[color=green]' Harlan Grove, Mar 2002[/color]

    [color=darkblue]Dim[/color] Y [color=darkblue]As[/color] [color=darkblue]Variant[/color]

    [color=darkblue]If[/color] [color=darkblue]TypeOf[/color] a [color=darkblue]Is[/color] Range [color=darkblue]Then[/color]
    
        [color=darkblue]For[/color] [color=darkblue]Each[/color] Y [color=darkblue]In[/color] a.Cells
            AConcat = AConcat & Y.Value & Sep
        [color=darkblue]Next[/color] Y
    
    [color=darkblue]ElseIf[/color] IsArray(a) [color=darkblue]Then[/color]
    
        [color=darkblue]For[/color] [color=darkblue]Each[/color] Y [color=darkblue]In[/color] a
            AConcat = AConcat & Y & Sep
        [color=darkblue]Next[/color] Y
        
    [color=darkblue]Else[/color]
    
        AConcat = AConcat & a & Sep
    
    [color=darkblue]End[/color] [color=darkblue]If[/color]
    
    AConcat = Left(AConcat, Len(AConcat) - Len(Sep))
    
[color=darkblue]End[/color] [color=darkblue]Function[/color]
[/font]

2) Then try the following worksheet function, which needs to be confirmed with CONTROL+SHIFT+ENTER...

=SUBSTITUTE(AConcat(IF(D18:S18<>"",IF(ISNA(MATCH(D18:S18,D25:S25,0)),", "&D18:S18,""),"")),", ","",1)
 
Upvote 0
brettdj said:
You use my Duplicate Master addin from http://www.experts-exchange.com/A_2123.html

- download and install the addin
- Search Option ..... Cell Search
- Application Scope .... Range D18:S18, D25:S25
- Unique List (occur once)

Cheers

Dave
Click to expand...

Dave,

I tried your solution and it worked, but it highlighted the values rather than listing them in a separate cell. Thank you for your reply. I will keep your site address for future problems.

William Daniels
 
Upvote 0
Yahya said:
welcome to MrExcel Board

i wish if this help you
Excel 2007<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6" cellpadding="2.5px"><colgroup><col style="background-color: #E0E0F0" width="25px"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th>
</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th><th>Q</th><th>R</th><th>S</th><th>T</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">10</td><td style="text-align: right;;">15</td><td style="text-align: right;;">16</td><td style=";">
</td><td style=";">
</td><td style=";">
</td><td style=";">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td><td style=";">b</td><td style=";">b</td><td style=";">b</td><td style=";">b</td><td style="text-align: right;;">9</td><td style="text-align: right;;">10</td><td style="text-align: right;;">11</td><td style="text-align: right;;">12</td><td style="text-align: right;;">13</td><td style="text-align: right;;">14</td><td style="text-align: right;;">15</td><td style="text-align: right;;">16</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">21</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">22</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">23</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">24</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">25</td><td style="text-align: right;;">25</td><td style="text-align: right;;">1</td><td style="text-align: right;;">9</td><td style="text-align: right;;">12</td><td style=";">b</td><td style="text-align: right;;">2</td><td style="text-align: right;;">7</td><td style="text-align: right;;">11</td><td style=";">b</td><td style="text-align: right;;">3</td><td style="text-align: right;;">8</td><td style="text-align: right;;">13</td><td style=";">b</td><td style="text-align: right;;">4</td><td style="text-align: right;;">9</td><td style="text-align: right;;">14</td><td style=";">b</td></tr></tbody></table>
Sheet1​


<table rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" cellpadding="2.5px" width="85%"><tbody><tr><td style="padding:6px">Array Formulas<table rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6" cellpadding="2.5px" width="100%"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">D15</th><td style="text-align:left">{=IFERROR(INDEX($D$18:$S$18,SMALL(IF(1-ISNUMBER(MATCH($D$18:$S$18,$D$25:$T$25,0)),COLUMN($D$18:$S$18)-COLUMN($D$18)+1),COLUMNS($D$15:D15))),"")}</td></tr></tbody></table>Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself</td></tr></tbody></table>
Click to expand...

Dear Yahya,

Thank you for your reply. I used your formula and it worked. I am now trying to get all unique values to appear in one cell rather than in separate cells. Can you tell me if this can be done. I listed the formula three times with &", "& between them and the result was 10,10,10 rather than 10,15,16.

William Daniels
 
Upvote 0
Domenic said:
To return the resulting numbers in a single cell separated by a comma, try the following custom function...

1) First place the following code in a regular module (Alt+F11 > Insert > Module > Copy/paste the code > Alt+Q)...

Code: 
[FONT=Verdana][COLOR=darkblue]Function[/COLOR] AConcat(a [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR], [COLOR=darkblue]Optional[/COLOR] Sep [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR] = "") [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
[COLOR=green]' Harlan Grove, Mar 2002[/COLOR]

    [COLOR=darkblue]Dim[/COLOR] Y [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]

    [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]TypeOf[/COLOR] a [COLOR=darkblue]Is[/COLOR] Range [COLOR=darkblue]Then[/COLOR]
    
        [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] Y [COLOR=darkblue]In[/COLOR] a.Cells
            AConcat = AConcat & Y.Value & Sep
        [COLOR=darkblue]Next[/COLOR] Y
    
    [COLOR=darkblue]ElseIf[/COLOR] IsArray(a) [COLOR=darkblue]Then[/COLOR]
    
        [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] Y [COLOR=darkblue]In[/COLOR] a
            AConcat = AConcat & Y & Sep
        [COLOR=darkblue]Next[/COLOR] Y
        
    [COLOR=darkblue]Else[/COLOR]
    
        AConcat = AConcat & a & Sep
    
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    
    AConcat = Left(AConcat, Len(AConcat) - Len(Sep))
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Function[/COLOR]
[/FONT]
2) Then try the following worksheet function, which needs to be confirmed with CONTROL+SHIFT+ENTER...

=SUBSTITUTE(AConcat(IF(D18:S18<>"",IF(ISNA(MATCH(D18:S18,D25:S25,0)),", "&D18:S18,""),"")),", ","",1)
Click to expand...

Dear Domenic,

Thank you for your reply to my question. I'll let you know if I'm able to get it work. It looks like a good solution.

William Daniels
 
Upvote 0
the result show in cells(1,1)
Sub Macro1()
Dim i As Integer
Dim j As Integer
Dim k As Integer

Dim s As String
s = ""
For i = 4 To 19
k = 0
For j = 4 To 19
If Cells(18, i) = Cells(25, j) Then
k = k + 1
End If
Next
If k = 0 Then
If s = "" Then
s = Cells(18, i)
Else
s = s & "," & Cells(18, i)
End If

End If
Next
Cells(1, 1) = s
End Sub
 
Upvote 0
Domenic said:
To return the resulting numbers in a single cell separated by a comma, try the following custom function...

1) First place the following code in a regular module (Alt+F11 > Insert > Module > Copy/paste the code > Alt+Q)...

Code: 
[FONT=Verdana][COLOR=darkblue]Function[/COLOR] AConcat(a [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR], [COLOR=darkblue]Optional[/COLOR] Sep [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR] = "") [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR][/FONT]
[FONT=Verdana][COLOR=green]' Harlan Grove, Mar 2002[/COLOR][/FONT]
 
[FONT=Verdana]   [COLOR=darkblue]Dim[/COLOR] Y [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR][/FONT]
 
[FONT=Verdana]   [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]TypeOf[/COLOR] a [COLOR=darkblue]Is[/COLOR] Range [COLOR=darkblue]Then[/COLOR][/FONT]
 
[FONT=Verdana]       [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] Y [COLOR=darkblue]In[/COLOR] a.Cells[/FONT]
[FONT=Verdana]           AConcat = AConcat & Y.Value & Sep[/FONT]
[FONT=Verdana]       [COLOR=darkblue]Next[/COLOR] Y[/FONT]
 
[FONT=Verdana]   [COLOR=darkblue]ElseIf[/COLOR] IsArray(a) [COLOR=darkblue]Then[/COLOR][/FONT]
 
[FONT=Verdana]       [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] Y [COLOR=darkblue]In[/COLOR] a[/FONT]
[FONT=Verdana]           AConcat = AConcat & Y & Sep[/FONT]
[FONT=Verdana]       [COLOR=darkblue]Next[/COLOR] Y[/FONT]
 
[FONT=Verdana]   [COLOR=darkblue]Else[/COLOR][/FONT]
 
[FONT=Verdana]       AConcat = AConcat & a & Sep[/FONT]
 
[FONT=Verdana]   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR][/FONT]
 
[FONT=Verdana]   AConcat = Left(AConcat, Len(AConcat) - Len(Sep))[/FONT]
 
[FONT=Verdana][COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Function[/COLOR][/FONT]

2) Then try the following worksheet function, which needs to be confirmed with CONTROL+SHIFT+ENTER...

=SUBSTITUTE(AConcat(IF(D18:S18<>"",IF(ISNA(MATCH(D18:S18,D25:S25,0)),", "&D18:S18,""),"")),", ","",1)
Click to expand...

Thank you for your reply. I will give this a try.

William Daniels
 
Upvote 0
suxiaoli said:
the result show in cells(1,1)
Sub Macro1()
Dim i As Integer
Dim j As Integer
Dim k As Integer

Dim s As String
s = ""
For i = 4 To 19
k = 0
For j = 4 To 19
If Cells(18, i) = Cells(25, j) Then
k = k + 1
End If
Next
If k = 0 Then
If s = "" Then
s = Cells(18, i)
Else
s = s & "," & Cells(18, i)
End If

End If
Next
Cells(1, 1) = s
End Sub
Click to expand...

Thank you for your reply. I will give this a try.

William Daniels
 
Upvote 0
You must log in or register to reply here.

Similar threads

bksbksbks
  • Solved
Exclude values from delineated list
Replies
7
Views
81
Fluff
Fluff
T
  • Question Question
Moving address list to columns from rows
Replies
3
Views
87
Sufiyan97
Sufiyan97
D
  • Solved
List of items within a specific month
Replies
4
Views
227
Fluff
Fluff
R
  • Solved
Large function
Replies
7
Views
324
Eric W
Eric W
C
  • Solved
List Results from All Sheets Search
2
Replies
16
Views
269
MikeVol
MikeVol

Forum statistics

Threads
1,224,521
Messages
6,179,277
Members
452,902
Latest member
Knuddeluff

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top