Check incorrect fileds then CONCATENATE

[lanjapfag]

[TABLE="width: 200"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]John James [/TD]
[TD]2 [/TD]
[TD]2A [/TD]
[TD]MIPA[/TD]
[TD]8XXY [/TD]
[TD] =IF(B1,C1,D1 NOT EQUAL TO B6,C6,D6 THEN [CONCATENATE,A1,B1,C1,D1]) [/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]John James [/TD]
[TD]2[/TD]
[TD]2A[/TD]
[TD]MIPA[/TD]
[TD]8AMN[/TD]
[TD] =IF(B2,C2,D2 NOT EQUAL TO B6,C6,D6 THEN [CONCATENATE,A2,B2,C2,D2])[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]John James[/TD]
[TD]2[/TD]
[TD]2A[/TD]
[TD]MIPA[/TD]
[TD]8NOP[/TD]
[TD] =IF(B3,C3,D3 NOT EQUAL TO B6,C6,D6 THEN [CONCATENATE,A3,B3,C3,D3])[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]John James[/TD]
[TD]2[/TD]
[TD]2B[/TD]
[TD]MIPA[/TD]
[TD]8XOP[/TD]
[TD] =IF(B4,C4,D4 NOT EQUAL TO B6,C6,D6 THEN [CONCATENATE,A4,B4,C4,D4])[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]John James[/TD]
[TD]2[/TD]
[TD]2A[/TD]
[TD]MUNX[/TD]
[TD]8JLO[/TD]
[TD] =IF(B5,C5,D5 NOT EQUAL TO B6,C6,D6 THEN [CONCATENATE,A5,B5,C5,D5])[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]2A[/TD]
[TD]MIPA[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]John James[/TD]
[TD]2[/TD]
[TD]2A[/TD]
[TD]MIPA[/TD]
[TD]8NIU[/TD]
[TD]=IF(B7,C7,D7 NOT EQUAL TO B9,C9,D9 THEN [CONCATENATE,A7,B7,C7,D7])[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]John James[/TD]
[TD]2[/TD]
[TD]2A[/TD]
[TD]MIPA[/TD]
[TD]8XOS[/TD]
[TD]=IF(B8,C8,D8 NOT EQUAL TO B9,C9,D9 THEN [CONCATENATE,A8,B8,C8,D8])[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD]2
[/TD]
[TD]2A
[/TD]
[TD]MIPA
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I need formula to check if B1, C1, D1 dont match or are not equal to B6,C6,D6 then CONCATENATE them to Coulmn F.

Any Help please ?

 

[etaf]

=IF(B1,C1,D1 NOT EQUAL TO B6,C6,D6 THEN [CONCATENATE,A1,B1,C1,D1])

=IF(AND(B1<>$B$6, C1<>$C$6, D1<>$D$6), CONCATENATE(A1,B1,C1,D1) , "what if the do match ?" )
copy down

But i notice in row 7 you change the test ?

 

[skywriter]

Try this.


Sheet8

*	A	B	C	D	E	F
John James	MIPA	8XXY	*
John James	MIPA	8AMN	*
John James	MIPA	8NOP	*
John James	2B	MIPA	8XOP	John James22BMIPA
John James	MUNX	8JLO	John James22AMUNX
*	MIPA	*	*
John James	MIPA	8NIU	*
John James	MIPA	8XOS	*
*	MIPA	*	*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:82px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:531px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]2A[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]2A[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]2A[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]2A[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]2A[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]2A[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]2A[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]2A[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula F1 =IF(CONCATENATE(B1,C1,D1)<>CONCATENATE($B$6,$C$6,$D$6),CONCATENATE(A1,B1,C1,D1),"") F2 =IF(CONCATENATE(B2,C2,D2)<>CONCATENATE($B$6,$C$6,$D$6),CONCATENATE(A2,B2,C2,D2),"") F3 =IF(CONCATENATE(B3,C3,D3)<>CONCATENATE($B$6,$C$6,$D$6),CONCATENATE(A3,B3,C3,D3),"") F4 =IF(CONCATENATE(B4,C4,D4)<>CONCATENATE($B$6,$C$6,$D$6),CONCATENATE(A4,B4,C4,D4),"") F5 =IF(CONCATENATE(B5,C5,D5)<>CONCATENATE($B$6,$C$6,$D$6),CONCATENATE(A5,B5,C5,D5),"") F7 =IF(CONCATENATE(B7,C7,D7)<>CONCATENATE($B$9,$C$9,$D$9),CONCATENATE(A7,B7,C7,D7),"") F8 =IF(CONCATENATE(B8,C8,D8)<>CONCATENATE($B$9,$C$9,$D$9),CONCATENATE(A8,B8,C8,D8),"") <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[lanjapfag]

Hey etaf Thanks you for that.

I am very happy it WORKED!

But i notice in row 7 you change the test ?

Yes this changes because there are 26,000 rows and in between them there are I have to manually change the $B$ and $C$.

What if I can automate that process to check like IF font color blue DO the logic test.

what if the do match ?

they will be FALSE ?

 

[lanjapfag]

Try this.


Sheet8

*	A	B	C	D	E	F
John James	MIPA	8XXY	*
John James	MIPA	8AMN	*
John James	MIPA	8NOP	*
John James	2B	MIPA	8XOP	John James22BMIPA
John James	MUNX	8JLO	John James22AMUNX
*	MIPA	*	*
John James	MIPA	8NIU	*
John James	MIPA	8XOS	*
*	MIPA	*	*

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]2A[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]2A[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]2A[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]2A[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]2A[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]2A[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]2A[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]2A[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula F1 =IF(CONCATENATE(B1,C1,D1)<>CONCATENATE($B$6,$C$6,$D$6),CONCATENATE(A1,B1,C1,D1),"") F2 =IF(CONCATENATE(B2,C2,D2)<>CONCATENATE($B$6,$C$6,$D$6),CONCATENATE(A2,B2,C2,D2),"") F3 =IF(CONCATENATE(B3,C3,D3)<>CONCATENATE($B$6,$C$6,$D$6),CONCATENATE(A3,B3,C3,D3),"") F4 =IF(CONCATENATE(B4,C4,D4)<>CONCATENATE($B$6,$C$6,$D$6),CONCATENATE(A4,B4,C4,D4),"") F5 =IF(CONCATENATE(B5,C5,D5)<>CONCATENATE($B$6,$C$6,$D$6),CONCATENATE(A5,B5,C5,D5),"") F7 =IF(CONCATENATE(B7,C7,D7)<>CONCATENATE($B$9,$C$9,$D$9),CONCATENATE(A7,B7,C7,D7),"") F8 =IF(CONCATENATE(B8,C8,D8)<>CONCATENATE($B$9,$C$9,$D$9),CONCATENATE(A8,B8,C8,D8),"") <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

It doesnt work.

 

[lanjapfag]

=IF(B1,C1,D1 NOT EQUAL TO B6,C6,D6 THEN [CONCATENATE,A1,B1,C1,D1])

=IF(AND(B1<>$B$6, C1<>$C$6, D1<>$D$6), CONCATENATE(A1,B1,C1,D1) , "what if the do match ?" )
copy down

But i notice in row 7 you change the test ?

Etaf,

Can I rephrase my question.

How should I automate the logic test so that it searches font color and adds $ $ to the rows instead of me manually searching for the font.

Summary it the there will be three things:
1) Automatically search/look for cell $ $ and then (those cells are in blue color) is there a way to find it and add $ $ automatically instead
2) Compare differences of other cells with cell $ $
3) Then concatenate IF there is a difference

Something lilke:
=IF (AND(B1<> (look for color Blue) then assign$B$6, C1<>(look for color Blue) then assign$C$6, D1<>(look for color Blue) then assign$D$6), CONCATENATE(A1,B1,C1,D1) ,

 

[lanjapfag]

Or should I use VLookUP? if yes then How would I use VLOOKUP to tackle this ?

 

[skywriter]

It doesnt work.

In my post the result that looks like a spreadsheet is from a spreadsheet. I didn't type in the results those are the results of the formula in the bottom section of the post.

Which from what I can see is exactly what you said you wanted. If you are not getting the same results you are either typing the formula wrong or you pasted it into the wrong cell or your description of what you wanted is not the same as what you want now, but it works. Definitely works.

 

[lanjapfag]

In my post the result that looks like a spreadsheet is from a spreadsheet. I didn't type in the results those are the results of the formula in the bottom section of the post.

Which from what I can see is exactly what you said you wanted. If you are not getting the same results you are either typing the formula wrong or you pasted it into the wrong cell or your description of what you wanted is not the same as what you want now, but it works. Definitely works.

Hey Skywriter it finally worked, I must have copied/typed it wrong, now it works !

What about if we included it to search for font color like i and then applied IF(CONCATENATE(B1,C1,D1)<>CONCATENATE($B$6,$C$6,$D$6),CONCATENATE(A1,B1,C1,D1),"")

There are more than 26,000 rows and within these rows are some which are in Blue color.

The cell reference $ $ needed to be applied automatically in the formular.

 

[skywriter]

I don't think you can search for font color with a formula. It sounds like what you really need is some custom code to go through all the cells you are concerned with and do to them what you want. Custom code can do a lot. It can compare the values just like you want here and it can check font colors, etc etc.