Mach one value from one colum with multiple values in second column

[vesnabukleskamk]

Hi, honestly i have no idea wich formula to use in excel2010 - to solve this problem.
I need to check if deposit 1 is equal with deposit 2. it is easy when there is only one value in both columns (row 1) simple deposit 1 =deposit 2 to give me true or fale value in the check column.
However the second exmle the value in deposit 1 is one and i need to check if it appears in deposit 2 column. from the example it is the third value - so i can conclude that deposit 1 can be found in deposit 2 column. If i can perform this chech my result is valid and should get OK comment. (table A)

Table B - shows example where the value in deposit 1 can not be found in column deposit 2 - this data set is not valid and should get NOTOK comment.

The original data set is a lot of rows - i have created fields Accout, deposit 1 and deposit 2 with pivot . then i copy the reslult in excel and add the check coulumn with deposit1=deposit2. This is a very long proces to see if the value (always one) can be found in column 2 - and is done manualy

What can i use in excel , pivot or additional formula to solve this problem in shorter time and more advaced way ???
A)
[TABLE="width: 360"]
<tbody>[TR]
[TD="width: 99, bgcolor: transparent"]Account
[/TD]
[TD="width: 113, bgcolor: transparent"]depozit1
[/TD]
[TD="width: 113, bgcolor: transparent"]depozit2
[/TD]
[TD="width: 155, bgcolor: transparent"]check depozit1=depozit2
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]41002003111
[/TD]
[TD="bgcolor: transparent"]210501683413000
[/TD]
[TD="bgcolor: transparent"]210501683413000
[/TD]
[TD="bgcolor: transparent, align: center"]TRUE
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Account
[/TD]
[TD="bgcolor: transparent"]depozit
[/TD]
[TD="bgcolor: transparent"]deposit2
[/TD]
[TD="bgcolor: transparent"]proverka 1
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]41003053222
[/TD]
[TD="bgcolor: transparent, align: right"]42104000888
[/TD]
[TD="bgcolor: transparent, align: right"]42104000666
[/TD]
[TD="bgcolor: transparent, align: center"]FALSE
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent, align: right"]42102001555
[/TD]
[TD="bgcolor: transparent, align: center"]FALSE
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent, align: right"]42104000888
[/TD]
[TD="bgcolor: transparent, align: center"]TRUE
[/TD]
[/TR]
</tbody>[/TABLE]

b)
[TABLE="width: 360"]
<tbody>[TR]
[TD="width: 99, bgcolor: transparent"]Account
[/TD]
[TD="width: 113, bgcolor: transparent"]depozit
[/TD]
[TD="width: 113, bgcolor: transparent"]deposit_account
[/TD]
[TD="width: 155, bgcolor: transparent"]proverka 1
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]41003053555
[/TD]
[TD="bgcolor: transparent, align: right"]42104000123
[/TD]
[TD="bgcolor: transparent, align: right"]42104000999
[/TD]
[TD="bgcolor: transparent, align: center"]FALSE
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]42102001888
[/TD]
[TD="bgcolor: transparent, align: center"]FALSE
[/TD]
[/TR]
</tbody>[/TABLE]

 

[DanteAmor]

How about

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:134.02px;" /><col style="width:165.39px;" /><col style="width:152.08px;" /><col style="width:150.18px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; text-align:center; ">Account</td><td style="background-color:#ffff00; text-align:center; ">depozit</td><td style="background-color:#ffff00; text-align:center; ">deposit2</td><td style="background-color:#ffff00; text-align:center; ">proverka 1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">41003053222</td><td style="text-align:right; ">42104000888</td><td style="text-align:right; ">42104000666</td><td style="text-align:right; ">FALSO</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td > </td><td style="text-align:right; ">42102001555</td><td style="text-align:right; ">FALSO</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td > </td><td style="text-align:right; ">42104000888</td><td style="text-align:right; ">VERDADERO</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >D2</td><td >=IF(ISNA(MATCH(C2,B:B,0)),FALSE,TRUE)</td></tr></table></td></tr></table>

 

[vesnabukleskamk]

Thank you for the answer- however this doesn’t solve myproblem –
For every account there is deposit 1(which is only one value)and a group of deposits listed in deposit 2 column( one or more- not all thetable ) – the job is to check if deposit1 can be found in deposit 2. Not alldeposits. I get this from a pivot – so the true/false needs to be on Account level.
This is a full list – the last column is what I need to get/orsimilar

[TABLE="width: 474"]
<tbody>[TR]
[TD="width: 96, bgcolor: transparent"] Account
[/TD]
[TD="width: 126, bgcolor: transparent"] deposit1
[/TD]
[TD="width: 126, bgcolor: transparent"] deposit2
[/TD]
[TD="width: 155, bgcolor: transparent"] check depozit1=depozit2
[/TD]
[TD="width: 130, bgcolor: transparent"] Comment
[/TD]
[/TR]
[TR]
[TD="width: 96, bgcolor: transparent"]

41002003111​

[/TD]
[TD="width: 126, bgcolor: transparent"] 210501683413000
[/TD]
[TD="width: 126, bgcolor: transparent"] 210501683413000
[/TD]
[TD="width: 155, bgcolor: transparent"]

TRUE​

[/TD]
[TD="width: 130, bgcolor: transparent"]

ok​

[/TD]
[/TR]
[TR]
[TD="width: 96, bgcolor: transparent"] Account
[/TD]
[TD="width: 126, bgcolor: transparent"] deposit1
[/TD]
[TD="width: 126, bgcolor: transparent"] deposit2
[/TD]
[TD="width: 155, bgcolor: transparent"] check
[/TD]
[TD="width: 130, bgcolor: transparent"]
[/TD]
[/TR]
[TR]
[TD="width: 96, bgcolor: transparent"]

41003053222​

[/TD]
[TD="width: 126, bgcolor: transparent"]

42104000888​

[/TD]
[TD="width: 126, bgcolor: transparent"]

42104000666​

[/TD]
[TD="width: 155, bgcolor: transparent"]

FALSE​

[/TD]
[TD="width: 130, bgcolor: transparent"]

Ok ( for this account -dep1 can be found in deposit2​

[/TD]
[/TR]
[TR]
[TD="width: 96, bgcolor: transparent"]
[/TD]
[TD="width: 126, bgcolor: transparent"]

​

[/TD]
[TD="width: 126, bgcolor: transparent"]

42102001555​

[/TD]
[TD="width: 155, bgcolor: transparent"]

FALSE​

[/TD]
[/TR]
[TR]
[TD="width: 96, bgcolor: transparent"]
[/TD]
[TD="width: 126, bgcolor: transparent"]

​

[/TD]
[TD="width: 126, bgcolor: transparent"]

42104000888​

[/TD]
[TD="width: 155, bgcolor: transparent"]

TRUE​

[/TD]
[/TR]
[TR]
[TD="width: 96, bgcolor: transparent"] Account
[/TD]
[TD="width: 126, bgcolor: transparent"] deposit1
[/TD]
[TD="width: 126, bgcolor: transparent"] deposit2
[/TD]
[TD="width: 155, bgcolor: transparent"] check
[/TD]
[TD="width: 130, bgcolor: transparent"] Not ok ( for this account deposit 1 can not be found in deposit2
[/TD]
[/TR]
[TR]
[TD="width: 96, bgcolor: transparent"]

41003053555​

[/TD]
[TD="width: 126, bgcolor: transparent"]

42104000123​

[/TD]
[TD="width: 126, bgcolor: transparent"]

42104000999​

[/TD]
[TD="width: 155, bgcolor: transparent"]

FALSE​

[/TD]
[/TR]
[TR]
[TD="width: 96, bgcolor: transparent"]
[/TD]
[TD="width: 126, bgcolor: transparent"]
[/TD]
[TD="width: 126, bgcolor: transparent"]

42102001888​

[/TD]
[TD="width: 155, bgcolor: transparent"]

FALSE​

[/TD]
[/TR]
</tbody>[/TABLE]

 

[DanteAmor]

Sorry, but now I don't understand.
You will have a file that you can share.
On sheet1 you put the current data and on sheet2 the expected result.

You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.