MACRO FOR compare 3 columns in Sheet-1 with Sheet2 then add Sheet1 Fourth colum

[xcruc1at3r]

I have one workbook (Excel 2007) with 2 sheets. Sheet-1 contains 4 columns like the following:


Excel Sheet 1

COLUMN 1 COLUMN 2 COLUMN 3 COLUMN 4

1005 10 Sam Royal Blue

1005 20 Peter Navy Blue

1006 50 Mary Rose

1100 40 Sonam Green

1156 80 Rashmi Pink


And the Sheet -2 also contains 4 columns. The result should be like the following:


RESULT

Excel Sheet 2

COLUMN 1 COLUMN 2 COLUMN 3 COLUMN 4

997 90 Rupa

1005 10 Sam Royal Blue

1100 40 Sonam Green

1100 40 Sonam Green

1156 80 Rashmi Pink

886 30 Vanaja

775 50 Vivek

1005 20 Peter Navy Blue

1006 50 Mary Rose

1006 60 Varun

1100 40 Sonam Green

1005 40 Priya

1156 80 Rashmi Pink


What I need now is, if the Sheet-2 (first 3 columns) data match with the Sheet-1 (first 3 columns) data, then the fourth column of Sheet-2 to be automatically filled with Sheet-1 fourth column data & HIGHLIGHT the Matching Data

Range should be based on dynamic search

 

[slaya_cz]

For sure it can be done through macro, but what about use of

VLOOKUP?

Just use one additional column with [code]CONCATENATE

function, so if the columns are

997 90 Rupa

the used function is CONCATENATE(A1;"x";B1;"x";C1)[/code]

You see as result "997x90xRupa".

You add this additional column with formula for both Shet1 and Sheet2.

Then simply VLOOKUP for the string you created, and just set conditional formatting for change of the cell's color. Much easier than macro (from my point of view).

Hope this helps.


Best regards

slaya

 

[xcruc1at3r]

in this case i wont be able to find out whether column a b or c has the typing mistake!..cause my data can more then 4000 entries...

 

[SirJB7]

Hi, xcruc1at3r!

Could you please explain how -considering what you stated in your first post- a macro would be able to determine which A-B-C column has a typing mistake? Macros are flexible but not magicians.

As far as I read, you only ask to fill sheet 2 D with sheet 1 D when matching A-B-C. What slaya_cz performs that simply and wisely.

Highlighting can be performed upon value or length of sheet 2 D column cell values.

Regards!

 

[xcruc1at3r]

i know what macro is..friend...let me upload the file..!! so u can look into it!...

am not that good with excel...

https://docs.google.com/spreadsheet/ccc?key=0Ah3qQ3ZqPOsPdDYxdG5GWkV6eE1nSnF3bHpsd3dDcWc#gid=0

 

[SirJB7]

Hi, xcruc1at3r!

Haven't access to the uploaded file, just asked for permission.

Regards!

 

[xcruc1at3r]

well we cannot get access because of our office policies

 

[srinidhi]

This can be achieved with Index & Match Formula.


Just change the values according to u r needs, since u said dynamic range use B:B, C:C

=INDEX($C$2:$C$5,MATCH(D2&E2,$A$2:$A$5&$B$2:$B$5,0))

Index(result u want to display,MATCH('value to be matched',Range to look up for ,0)


This is an array formula.


U said u want to highlight the matching data, with which columns all the 4 columns in sheet one to sheet two or how is it, this can be achieved with conditional formatting,

like ISNA(MATCH(B2,$D$2:$D$21,0))

 

[SirJB7]

Hi, xcruc1at3r!

Yesterday I intended to mean that I haven't access to the file you have uploaded and that I asked for your permission. I apologize for my english.

Regards!

 

[xcruc1at3r]

Its basically the highlight is the 1st step and 2nd part is that in case of match in the data from sheet 1 D column should also reflect in sheet 2 D column against the matching Cell

for EG.

sheet 1

COLUMN 1 COLUMN 2 COLUMN 3 COLUMN 4

1005 10 Sam Royal Blue

1005 20 Peter Navy Blue


if in Sheet two the data is found as in sheet 1 so it should place

column 4 data in sheet 2

like

sheet 2

1005 10 Sam Royal << found in sheet 1 so in it should add "BLUE" in Column D

cause sheet two is basically based on 3 columns...4th column would be taken from sheet1's data..

 

[rsk]

@srinidhi : below formula posted by you


=INDEX($C$2:$C$5,MATCH(D2&E2,$A$2:$A$5&$B$2:$B$5,0))


Its not working...

can we use multiple selection in (Lookup_array)

eg:MATCH(lookup_value,lookup_array, match_value)

MATCH(D2&E2, $A$2:$A$5 & $B$2:$B$5, 0 )


any one pls help on this