Comparing row with columns

[gmoorthy]

Worksheet 1 data is as follows

Column1 Column2 Column3
1 ABC XYZ
2 ABC DEF
3 DEF LMO
4 LMO ABC
In Worksheet 2 data is as follows
1
2
3
4
How can i get data as follows by comparing sheet 1 and Sheet 2:
ABC DEF LMO XYZ
1 Y Y
2 Y Y
3 Y Y
4 Y Y

 

[SkipVought]

Please explain in excruciatingly precise detail the steps that you expect to occur in the result, beginning with sheets 1 & 2, and referring to the source data as the steps unfold.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Andrzejek]

Please, use [ignore][pre]...[/pre][/ignore] tags to present your example in more 'readable' way:

[pre]
Column1 Column2 Column3
1 ABC XYZ
2 ABC DEF
3 DEF LMO
4 LMO ABC

ABC DEF LMO XYZ
1 Y Y
2 Y Y
3 Y Y
4 Y Y [/pre]


Have fun.

---- Andy

 

[SkipVought]

Here's what you have not explained to us.

1. Will there ever be more than 4 rows?
2. Will there ever be more than 4 values like ABC, XYZ?
3. What function does sheet 2 perform? It seems unnecessary!
4. Do you expect the solution to assemble all the values in your solution, or is there a starting structure?

For instance, using this as a starting structure, the formula to fill in the matrix is pretty simple.

[pre]
. ABC DEF LMO XYZ
1
2
3
4
[/pre]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[gmoorthy]

Yes there will be more than 4 rows.
Basically in sheet 1 for each row i would have multiple values in each cell which are in different columns.

So i want a consolidated sheet which will compare column 1 from sheet 1 and shee2 and put that in sheet3


so the columns in sheet 3 starting from the second column to n columns are unique values from sheet2. I wanted "ABC" is there in 1 and 2 which is represented by "Y" ( so ABC has values 1 and 2 , XYZ has only value 1 . The column 1 in worksheet 2 will have more rows .


Column1 ABC XYZ

1 Y
2 Y

 

[SkipVought]

Use something like this
[tt]
=SUMPRODUCT((Sheet1!$A:$A=$A2)*(Sheet1!$B:$B=B$1))+SUMPRODUCT((Sheet1!$A:$A=$A2)*(Sheet1!$C:$C=B$1))
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}