Help comparing data within a table with duplicates in multiple columns

[khtt2003]

I know the title sounds like a lot but I'm sure there's a way to do this, I just can't figure it out.  Here's what I'm working with:

I have a table with 14 columns and I need to look at the data in 5 of them.  There are duplicates in all columns by design (I've created a concatenated column to manage that portion).  Here's what I need to do using the example below:

1.  Look at all of the rows with the same File Number

2. From those rows, look at rows with the same L-Name

3. From those rows, look at rows with the same R-Name

4. From those rows, look at rows with the same R-Type

5. From the remaining selection, return whether or not a row has an L-Type of "All"

The idea is to do this for each file number in a report with thousands.  Any help is greatly appreciated!

File Number	L-Name	L-Type	R-Name	R-Type
33	Left	One	In	3rd
33	Left	All	In	3rd
19	Left	One	In	CD
19	Left	All	In	CD
19	Left	One	DC	CD
3	Left	One	In	CD
4	Right	One	In	3p
4	Right	All	In	3p
4	Right	All	DC	3p
4	Right	One	IA	3p
4	Left	All	IA	3p

[Anonymous]

Hi @khtt2003 ,

I'm not quite sure if I understand your needs accurately.
Sample data without sensitive information and expected output would help tremendously.
Please see this post regarding:  How to Get Your Question Answered Quickly 

Hope it helps,

Community Support Team _ Caitlyn

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[khtt2003]

Sorry I wasn't more clear, what I need to do is complicated and difficult to articulate.  Please see the below example (my security is such that I cannot upload a file).

What I'm looking to determine is that for every FileNumber-Color-Tree-Element combination, there is a row where Type=All (return Yes/No)

For 42360-White-Cedar-Caesium there are two entries and one has Type=All, so that would be a Yes for all matching rows.

For 29090-Brown-Chestnut-Gadolinium there are two entries and neither has Type=All, so that would be a No for all matching rows.

On my file, I started by concatenating FileNumber-Color-Tree-Element data, but I can't figure out the next step.

I hope that makes more sense.  Thank you for your help!

File Number	Color	Tree	Element	Type	Expected Outcome
42360	White	Cedar	Caesium	One	Yes
42360	White	Cedar	Caesium	All	Yes
42360	Black	Cedar	Caesium	One	Yes
42360	Black	Cedar	Caesium	All	Yes
42360	Purple	Cedar	Caesium	One	Yes
42360	Purple	Cedar	Caesium	All	Yes
42360	Green	Cedar	Caesium	One	Yes
42360	Green	Cedar	Caesium	All	Yes
42360	Orange	Cedar	Caesium	One	No
43110	Green	Chestnut	Radon	One	Yes
43110	Green	Chestnut	Radon	All	Yes
42350	Black	Ash	Gadolinium	One	No
34570	Green	Chestnut	Einsteinium	One	Yes
34570	Green	Chestnut	Einsteinium	All	Yes
29090	Brown	Chestnut	Gadolinium	One	No
29090	Brown	Chestnut	Gadolinium	One	No