Check Consistency

[ekhawaja]

Hello,

I am looking for a formula in col G that checks for consistency by looking up the value from col F , and matching in col C with criteria in col B. If for all the repeated SS number in col c, i get the same repeated value in col B, i would like col G to return yes, otherwise No.

Qty	SS NUMBER			SS NUMBER	Qty Consistency (this is where I need the formula)
SS Exists	1222706715			1222706715	no
SS Exists	1222706715			1222534595	no
SS Exists	1222706715			1222638861	yes
SS Exists	1222706715			1222705940	yes
SS Required	1222706715			1222705941	yes
SS Required	1222706715
1	1222534595
0	1222534595
1	1222534595
0	1222534595
0	1222534595
	1222638861
4	1222705940
4	1222705940
	1222638861
SS needed	1222705941
SS needed	1222705941

 

[Fluff]

How about

=IF(ROWS(UNIQUE(FILTER($B$2:$B$100,$C$2:$C$100=F2)))=1,"yes","No")

 

[ekhawaja]

Hello Fluff, thanks so much for the reply, I noticed for some of them the formula returned "no" when the values were consistent........

 

[Fluff]

In that case can you post some sample data that shows the problem.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[ekhawaja]

This

How about

=IF(ROWS(UNIQUE(FILTER($B$2:$B$100,$C$2:$C$100=F2)))=1,"yes","No")

this formula is working now!! I had extra spaces in some cells which resulted in inconsistent result. Thanks Fluff for your expertise, always!

If i have 3 sheets to run this formula across, how can i modify this? I have data in 3 different sheets, i have to do it one by one for each sheet, wondering if i can incorporate across the 3 sheets at once?

 

[Fluff]

How about

=IF(ROWS(UNIQUE(FILTER(VSTACK(Sheet1!$B$2:$B$100,Sheet2!$B$2:$B$100,Sheet3!$B$2:$B$100),VSTACK(Sheet1!$C$2:$C$100,Sheet2!$C$2:$C$100,Sheet3!$C$2:$C$100)=F2)))=1,"yes","No")