Compare columns

[sharshra]

I have to compare 2 columns & get the result in another column. I´m using FILTER function but not getting the correct results. I'm sure I must have done some mistake. Can the experts help please?

Scenario - I have a table (source table) with 3 columns - name, C, L. Columns C & L are not filled for all names. In the column 'both´, I want to get the names where both columns C & L are not empty. If one either C or L is empty, both column will be blank. The results should be as shown in the desired output table.

I have used FILTER function, but not getting the required output. Is there a better way to get the desired output?

2nd part is to use dynamic array formula to get the same result.

Source table:

excel problems.xlsx
	O	P	Q
2	name	C	L
3	ffafds	ffafds	ffafds
4	qewq		qewq
5	tyery	tyery	tyery
6	ryut	ryut
7	hjg	hjg	hjg
8	zvzvc	zvzvc
9	vnc	vnc
10	gsdg		gsdg
11	fhd	fhd	fhd
12	fjgg	fjgg	fjgg
13	lghl		lghl
Sheet2

Desired output:

excel problems.xlsx
	O	P	Q	R
2	name	C	L	both
3	ffafds	ffafds	ffafds	ffafds
4	qewq		qewq
5	tyery	tyery	tyery	tyery
6	ryut	ryut
7	hjg	hjg	hjg	hjg
8	zvzvc	zvzvc
9	vnc	vnc
10	gsdg		gsdg
11	fhd	fhd	fhd	fhd
12	fjgg	fjgg	fjgg	fjgg
13	lghl		lghl
Sheet2

Incorrect result with FILTER formula:

excel problems.xlsx
	U	V	W	X
2	name	C	L	both
3	ffafds	ffafds	ffafds	ffafds
4	qewq		qewq	qewq
5	tyery	tyery	tyery	tyery
6	ryut	ryut		ryut
7	hjg	hjg	hjg	hjg
8	zvzvc	zvzvc		zvzvc
9	vnc	vnc		vnc
10	gsdg		gsdg	gsdg
11	fhd	fhd	fhd	fhd
12	fjgg	fjgg	fjgg	fjgg
13	lghl		lghl	lghl
Sheet2

Cell Formulas
Range	Formula
X3:X13	X3	=FILTER($O$3:$O$13,(P3<>"")*(Q3<>""))
Dynamic array formulas.

 

[hagia_sofia]

Hello, you could e.g. try:

=IF(ISBLANK(P2:P12)+ISBLANK(Q2:Q12)=0,O2:O12,"")

 

[sharshra]

Thanks, @hagia_sofia. In the actual table, even when there is blank cell, ISBLANK returns FALSE. Not sure why it happens. It works well in the sample table shared in this post

I thought it should be possible with FILTER function, but it is not working. When I filter on blank, it recognized the blank cells as blank, but not in the formula. Is there any other ways other than checking for BLANK?

Or any suggestions from experts why ISBLANK returns FALSE when it is actually blank?

 

[hagia_sofia]

It is possible that it is not blank: does CODE(P3:Q3) return error for every blank cell?

 

[sharshra]

Yes. When I run CODE, I get #VALUE! error.

 

[hagia_sofia]

Alright, how about this:

=IF(LEN(P2:P12)*LEN(Q2:Q12)>0,O2:O12,"")

 

[alansidman]

an option with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"name", type text}, {"C", type text}, {"L", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if([C]=[L]) then [C] else null)
in
    #"Added Custom"

 

[sharshra]

That´s perfect! Thanks a lot @hagia_sofia
Thanks to @alansidman for providing an alternate solution