Find unique and missing

K

Kishan

Well-known Member
Joined
Mar 15, 2011
Messages
1,648
Office Version
  1. 2010
Platform
  1. Windows
Using Excel 2000
Hi,

I want to find among each row in C:F column what is unique or missing, results in the coloums H:I
Example data

[TABLE="width: 724"]
<colgroup><col><col span="2"><col span="4"><col><col span="2"><col></colgroup><tbody>[TR]
[TD] [/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]P1[/TD]
[TD]P2[/TD]
[TD]P3[/TD]
[TD]P4[/TD]
[TD] [/TD]
[TD]Unique[/TD]
[TD]Missing[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD]12[/TD]
[TD]X[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1[/TD]
[TD]X[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD] [/TD]
[TD]1X2[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1[/TD]
[TD]X[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD]1X[/TD]
[TD]2[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1[/TD]
[TD]X[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD] [/TD]
[TD]1X2[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]X[/TD]
[TD]1[/TD]
[TD]X[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD]1X[/TD]
[TD]2[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD]1X[/TD]
[TD]2[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]2[/TD]
[TD]X[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD]1X2[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]X[/TD]
[TD]1[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD] [/TD]
[TD]1X[/TD]
[TD]2[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD]1X[/TD]
[TD]2[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]X[/TD]
[TD]2[/TD]
[TD]X[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD]1X2[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD]1[/TD]
[TD]X2[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD] [/TD]
[TD]12[/TD]
[TD]X[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD]1X[/TD]
[TD]2[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD]1X[/TD]
[TD]2[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

Thank you in advance

Regards,
Kishan
 
Last edited:

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Sort by date Sort by votes
Probably neater ways, but how about


Excel 2013 32 bit
CDEFGHI
5P1P2P3P4
6112112X
71X221X2
81X111X2
91X121X2
10X1X11X2
11XXX11X2
122X111X2
13X1XX1X2
141XX11X2
15X2X11X2
1611111X2
17121212X
181XX11X2
191XX11X2
Sheet2
Cell Formulas
RangeFormula
H6=SUBSTITUTE("1X2",I6,"")
I6=IF(ISNA(MATCH(1,C6:F6,0)),1,"")&IF(ISNA(MATCH("X",C6:F6,0)),"X","")&IF(ISNA(MATCH(2,C6:F6,0)),2,"")
 
Upvote 0
Fluff said:
Probably neater ways, but how about


[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F0E0E0]#F0E0E0[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F0E0E0]#F0E0E0[/URL] "]H6[/TH]
[TD="align: left"]=SUBSTITUTE([COLOR=rgb(255]"1X2",I6,""[/COLOR])[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F0E0E0]#F0E0E0[/URL] "]I6[/TH]
[TD="align: left"]=IF([COLOR=rgb(255]ISNA(MATCH([COLOR=0)]1,C6:F6,0[/COLOR])),1,""[/COLOR])&IF([COLOR=rgb(255]ISNA(MATCH([COLOR=0)]"X",C6:F6,0[/COLOR])),"X",""[/COLOR])&IF([COLOR=rgb(255]ISNA(MATCH([COLOR=0)]2,C6:F6,0[/COLOR])),2,""[/COLOR])[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
Hi Fluff, it is a working as request perfect!!

Thank you for help

Kind Regards,
Kishan :)
 
Upvote 0
Glad to help & thanks for the feedback
 
Upvote 0
Hi Fluff, sorry to disturb you again, please could you check row 6 & row 20 where are, 4 X’S in the missing side gives the correct result "12", but Unique it should return "X"

[TABLE="width: 705"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD]P1[/TD]
[TD]P2[/TD]
[TD]P3[/TD]
[TD]P4[/TD]
[TD][/TD]
[TD]Unique[/TD]
[TD]Missing[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[TD]1X2[/TD]
[TD]12[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD]1[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[TD]1X[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD]2[/TD]
[TD]X[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1X2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]X2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]12[/TD]
[TD]X[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1X[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1X[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[TD]1X2[/TD]
[TD]12[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thank you

Regards,
Kishan
 
Last edited:
Upvote 0
Replace the Col H formula with
=IF(ISNA(MATCH(1,C6:F6,0)),"",1)&IF(ISNA(MATCH("X",C6:F6,0)),"","X")&IF(ISNA(MATCH(2,C6:F6,0)),"",2)
 
Upvote 0
Fluff said:
Replace the Col H formula with
=IF(ISNA(MATCH(1,C6:F6,0)),"",1)&IF(ISNA(MATCH("X",C6:F6,0)),"","X")&IF(ISNA(MATCH(2,C6:F6,0)),"",2)
Click to expand...
Thank you Fluff, yes this formula did the trick, all is resulting very perfect!!

Kind Regards,
Kishan :)
 
Last edited:
Upvote 0
Glad to help & thanks for the feedback
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Make a new VBA faster
2
Replies
17
Views
1K
Alex Blakenburg
A
Z
  • Question Question
Count Distinct Values in Un-Filtered Table across Multiple Columns with 1 Criteria
Replies
0
Views
396
zero269
Z
T
  • Question Question
SUMPRODUCT Over Multiple Columns
Replies
2
Views
269
thewiseguy
T
A
  • Solved
Finding a unique value in a range and returning the value in another column of the same row, without offset / slow formulae.
Replies
8
Views
2K
AaronPMH
A
K
  • Solved
Find match in the same columns
Replies
5
Views
414
Kishan
K

Forum statistics

Threads
1,223,889
Messages
6,175,226
Members
452,620
Latest member
dsubash

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