Return as many Y as the number of occurrence of values in another column

Marcia · #1 11-27-2020, 03:32 AM

Hi. Wi fi and hotspot are not possible so I hope that without a sample attachment I could still get a solution to this problem.
Column A consists of values that are duplicated more than once, but the duplicates are contiguous to each other.
Column B consists of other sets of values.
I would like a formula in column C that returns Y if Column B has 1 or 2 . The Y should start from the first row of the sets of duplicated values in Col A.
Example:
A B C

C Y
C 2 Y
C Y
D
D
G Y
G 1 Y
G 1 Y
G Y
Thank you. I cannot properly align the values, all Y should be in Col C.

ArviLaanemets · #2 11-27-2020, 05:19 AM

Into C2 enter formula like

Code:

=IF(AND(OR($A2=$A1,$A2=$A3),OR(COUNTIFS($A$2:$A$10,$A2,$B$2:$B$10,1)>0,COUNTIFS($A$2:$A$10,$A2,$B$2:$B$10,2)>0)),"Y","")

, and copy down.

Marcia · #3 11-27-2020, 04:19 PM

Quote:

Originally Posted by ArviLaanemets

Into C2 enter formula like

Code:

=IF(AND(OR($A2=$A1,$A2=$A3),OR(COUNTIFS($A$2:$A$10,$A2,$B$2:$B$10,1)>0,COUNTIFS($A$2:$A$10,$A2,$B$2:$B$10,2)>0)),"Y","")

, and copy down.

Thank you Arvi. In my several attempts to write the code before I threw down the towel and sought this forum's help, I used the IF, AND, OR and COUNTIFS but combining them into one logical formula was really beyond me.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Picture attached. Need one column to return sum of another column.	IRHSAM	Excel	3	11-18-2017 01:21 AM
Excel / Mail Merge - Return Column Headers for All and Any Columns with Values	eduams	Mail Merge	1	09-26-2016 07:02 PM
Formula/function to return column names for matching values	GiJoe81	Excel	1	06-27-2015 12:13 AM
Return Sum value of one column from cells not blank in another column	zulugandalf	Excel	3	08-14-2014 03:37 AM
Need to search a column for a macth and return a result from a third column	pdfaust	Excel	2	02-03-2011 03:02 PM