Need help in MS Excel

[lmak]

Hello everyone,

I'm a beginner in MS Excel & searching a solution to my problem. Yr help is highly appreciated!

I have attached this simplified picture to help me explain my situation...

I have two worksheets in the same workbook.

Sheet#1 'Data!' consist of raw data (where some records have a comment)
Sheet#2 'Calculation!' consist of a lookup table I created by Excel functions

Initially, I was using =VLOOKUP("apple",A3:B9,2,FALSE)

As you can see on the spreadsheet that there are 3 records of "apple" but VLOOKUP can only show the first record. So, I changed the formula into this
=IF(ISERROR(INDEX($A$1:$B$9,SMALL(IF($A$1:$A$9=$A$15,ROW($A$1:$A$9)),ROW(1:1)),2))," ",INDEX($A$1:$B$9,SMALL(IF($A$1:$A$9=$A$15,ROW($A$1:$A$9)),ROW(1:1)),2))

Now, it shows all 3 records that link to "apple"

But the lookup only bring the cell value without original formatting. I would like to find a method to carry over the comment (red arrow at the corner) to the destination cell.

Even if I cannot carry over the comment, I'm happy if I can change the color or font size in the target cell, where the destination cell will do the same formatting. (at least I can tell which record is "special" after I do the lookup) Becoz right now, the destination cell will not show any format of target cell...

I put my request on Microsoft.com community & they provided a very good User Defined Function to me if I use VLOOKUP. Since I'm now using the INDEX, this UDF does not work for me. I'll copy the code in here & maybe can give you some ideas on how to write a suitable one?

Thank you so much for yr help in advance!!! I've been working on this for 2 days but won't be able to figure it out...:wall

- - -
Option Explicit
Function VlookupComment(myVal As Variant, myTable As Range, _
myColumn As Long, myBoolean As Boolean) As Variant

Application.Volatile True

Dim res As Variant 'could be an error
Dim myLookupCell As Range

res = Application.Match(myVal, myTable.Columns(1), myBoolean)
If IsError(res) Then
VlookupComment = "Not Found"
Else
Set myLookupCell = myTable.Columns(myColumn).Cells(1)(res)
VlookupComment = myLookupCell.Value
With Application.Caller
If .Comment Is Nothing Then
'do nothing
Else
.Comment.Delete
End If

If myLookupCell.Comment Is Nothing Then
'no comment, do nothing
Else
.AddComment Text:=myLookupCell.Comment.Text
End If
End With
End If

End Function

 

[alain]

are you looking to group the values for apple? grouping doesn't work for you?

 

[lmak]

Hmm...not really grouping the values of apple

I actually have to first select the apples, then user can choose one from the results (i.e. choose from 234, 534, 834). Excel will need to use the selected result (let's say 534) to do further calculations.

I know how to do the selection part. Instead of using Macros, I'll just use IF function because in the data, the result will not be greater than 5 items. (i.e. apple will generate max 5 numbers)

 

[Andy Nguyen]

Change the target cells using different colors. This modified version of the function you post will copy the color over to the new cell as well as the comment part. To show all 3 values, use a combination of OFFSET,MATCH,INDEX and VLOOKUP

[FONT=Arial,sans-serif][SIZE=2]Option Explicit
Function VlookupComment(myVal As Variant, myTable As Range, _
myColumn As Long, myBoolean As Boolean) As Variant
 
Application.Volatile True

Dim res As Variant 'could be an error
Dim myLookupCell As Range

Dim rngCaller As Excel.Range
 
On Error GoTo errhandler

res = Application.Match(myVal, myTable.Columns(1), myBoolean)
If IsError(res) Then
VlookupComment = "Not Found"
Else
Set myLookupCell = myTable.Columns(myColumn).Cells(1)(res)
VlookupComment = myLookupCell.Value 

 With Application.Caller

  If .Comment Is Nothing Then
   'do nothing
   Else
   .Comment.Delete
    End If

     If myLookupCell.Comment Is Nothing Then
    'no comment, do nothing
     Else
    .AddComment Text:=myLookupCell.Comment.Text

    End If
[COLOR=Red]
     Set rngCaller = Application.Caller
     rngCaller.Font.ColorIndex = myLookupCell.Font.ColorIndex[/COLOR]
 
  End With
  End If
 
  Exit Function
 
  errhandler:
  'Do what you want
 
  End Function[/SIZE][/FONT]

 

[lmak]

Thanks Andy, but the color does not show...
I'll keep trying!