Looking up whether one item has a certain matching item

[Thocow]

Hi, say if I had a spreadsheet as follows:
A B C D
1 E 3
2 E 6
3 E 6
4 C 6
5 C 6
6 C 4
7 D 4
8 D 3

I want to run a function that will return a single item from column A, i.e
my new column will contain:
E
C
D

And I want the function to tell me if this letter had a 3 next to it, so it
would return:
E Yes
C No
D Yes

Obviously I'm applying this to a much longer list or I'd manually look it
up, I'm sure there must be a simple way of doing this which I'm overlooking?

 

[Ashish Mathur]

Hi,

1. Select column B2:B500 (I have assumed that row 2 is the header row)
2. Go to Data > Filter > Advanced Filter > Copy to another location
3. In the list range, select B2:B500
4. Leave the criteria range blank
5. In the copy to box, select any blank cell on the existing sheet
6. When you click on OK, you will get all the unique names from column B.
Let's say the unique items get listed in range D210
7. In E3, enter if(countif($B$3:$B$500,D3)=3,"Yes","No") and copy down

Hope this helps.

 

[Thocow]

Just to check, in my example the ABCD along the top and 1-8 down the side are
meant to be the column and row designators that are in excel when you open
the sheet, thus items under the first A (E, C and D) are my unique names and
the numbers are the qualitative characteristics these names can have
assigned.

Will the countif work if the numbers in column B of the example aren't
numbers but qualitative data, ie each 3 is actually the word small, 4 medium
and 6 large? (that's actually what I'm dealing with, I used numbers for the
example)

Thanks a lot for your help,

Tom

 

[Ashish Mathur]

Hi

Please try out my solution. Also, I am confused about your last para -
column B already has text values, column C has numbers. Please clarify

 

[Roger Govier]

Hi

On your summary sheet, with E in A2, enter in B2
=SUMPRODUCT((Sheet1!$A$2:$A$1000=$A2)*(Sheet1!($B$2:$B$1000="Small"))

Change the ranges to suit, but ensure that they are of equal size.

 

[Thocow]

Hi Roger

I think you've understood my sample sheet better then Ashish, (ashish in the
sample sheet cell A1 contains E, thus column A has all letters in, column B
all numbers, columns C&D are both empty).

Roger, your solution tells me which items have small written next to them
but only before i've pulled them into single lines, i.e only before the
spreadsheet has become
E
C
D

It's after I've got it into this format that I want to know if each letter
ever had the word small by it? Thanks,

Tom

 

[Roger Govier]

Hi

Sorry my original formula has an extraneous ( inserted.
Anyway, to get the answer you want.

=IF(SUMPRODUCT((Sheet1!$A$2:$A$1000=$A2)*(Sheet1!($B$2:$B$1000="Small")),"Yes","No")

For future posting, it is always better to post exactly what you are
looking for, with an example.
It was misleading in your first post by saying you were looking for
Numbers, when in fact you were looking for text.