SiHodgy007
asked on
Excel Formula
Hi,
Can anyone help on the following formula in Excel please? I have tried matched and indexing but can't seem to get the required result that can scale up to 100 user spread sheet with the same user having multiple rows.
A B C
Owns an Orange
Dan Apple Yes
Dan Banana Yes
Dan Orange Yes
Pam Apple No
Pam Pear No
Pam cherry No
Can anyone help on the following formula in Excel please? I have tried matched and indexing but can't seem to get the required result that can scale up to 100 user spread sheet with the same user having multiple rows.
A B C
Owns an Orange
Dan Apple Yes
Dan Banana Yes
Dan Orange Yes
Pam Apple No
Pam Pear No
Pam cherry No
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
As you are dealing with 100 plus users, then try below to expand your list to whole columns
=IF(COUNTIFS($A:$A,$A2,$B:$B,$C$1)>=1,"Yes","No")
How about a Pivot Table?
Users as rows, commodity as columns and values. matrix will then show value or blank at intersection of user and commodity; value where User has commodity and blank where they do not.
See attached. Use the drop-down in cell B3 to select commodity (Fruit) and the table will adjust to show those that have that commodity.
Pivot-fruit.xlsx
Users as rows, commodity as columns and values. matrix will then show value or blank at intersection of user and commodity; value where User has commodity and blank where they do not.
See attached. Use the drop-down in cell B3 to select commodity (Fruit) and the table will adjust to show those that have that commodity.
Pivot-fruit.xlsx
ASKER
Awesome thanks
pls try
Open in new window
RegardsEE20170207.xlsx