comparing rows with duplicate values

[gmoorthy]

I have an 2 excel worksheet as follows :

Worksheet 1 has only one column with rows as follows( This is the master worksheet):

1
2
3
4
5

Worksheet 2 has two columns as follows:
ColumnA ColumnB
1 XYZ
2 ABC
1 MEJ

1. How can i compare column A with Worksheet1 and highlight rows in column B with one colour where a match is found.

 

[SkipVought]

hi,

It is not at all clear from your example, what you want. ALL the rows in Worksheet 2 match a value in Worksheet 1!

Skip,

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

 

[gmoorthy]

Sorry i made a mistake

here is how it is

Worksheet 1 has only one column with rows as follows( This is the master worksheet):

1
2
3
4
5

Worksheet 2 has two columns as follows:
ColumnA ColumnB
1 XYZ
2 XYZ
1 ABC

1. How can i compare column A with Worksheet1 and create data as follows


1 XYZ ABC
2 XYZ
3
4
5

 

[SkipVought]

1. sort the data on sheet 2 on column A, so your example should appear...
[tt]
1 XYZ
1 ABC
2 XYZ
[/tt]

2. The formula for sheet 1 columns a:??? on each row
[tt]
=INDEX(OFFSET(Sheet2!$A$1,MATCH($A1,Sheet2!$A:$A,0)-1,1,COUNTIF(Sheet2!$A:$A,$A1),1),COLUMN()-1,1)
[/tt]

The reason for a:[highlight]???[/highlight] is that you initially don;t know how many column B values there might be for any column A value. So you have to COPY/PASTE that formula ACROSS several rows, until you get a #REF! in EVERY ROW n a column. THEN you can delete THAT column and everything to the RIGHT.

Use Condirional Foramatting to make the #REF! invisible be shading the FONT the same as the cell interior.

My results based on the data in your example
[tt]
1 XYZ ABC #REF!
2 XYZ #REF! #REF!
3 #N/A #N/A #N/A
4 #N/A #N/A #N/A
5 #N/A #N/A #N/A
[/tt]
the #N/A is because you provided no data for these values in your example

Skip,

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