1. 

   xcruc1at3r
   

   Member
   Joined: Aug '11
   Posts: 174

   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

   Posted 3 months ago #
2. 

   slaya_cz
   

   Member
   Joined: Mar '11
   Posts: 6

   For sure it can be done through macro, but what about use of VLOOKUP?
   Just use one additional column with CONCATENATE function, so if the columns are
   997 90 Rupa
   the used function is CONCATENATE(A1;"x";B1;"x";C1)
   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

   Posted 3 months ago #
3. 

   xcruc1at3r
   

   Member
   Joined: Aug '11
   Posts: 174

   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...

   Posted 3 months ago #
4. 

   SirJB7
   

   Excel Ninja
   Joined: Dec '11
   Posts: 1,413

   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!

   Posted 3 months ago #
5. 

   xcruc1at3r
   

   Member
   Joined: Aug '11
   Posts: 174

   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

   Posted 3 months ago #
6. 

   SirJB7
   

   Excel Ninja
   Joined: Dec '11
   Posts: 1,413

   Hi, xcruc1at3r!
   Haven't access to the uploaded file, just asked for permission.
   Regards!

   Posted 3 months ago #
7. 

   xcruc1at3r
   

   Member
   Joined: Aug '11
   Posts: 174

   well we cannot get access because of our office policies

   Posted 3 months ago #
8. 

   srinidhi
   

   Member
   Joined: Jun '10
   Posts: 118

   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))

   Posted 3 months ago #
9. 

   SirJB7
   

   Excel Ninja
   Joined: Dec '11
   Posts: 1,413

   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!

   Posted 3 months ago #
10. 

    xcruc1at3r
    

    Member
    Joined: Aug '11
    Posts: 174

    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..

    Posted 3 months ago #
11. 

    rsk
    

    Member
    Joined: Dec '11
    Posts: 28

    @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

    Posted 3 months ago #

RSS feed for this topic

Reply

You must log in to post.