VBA Excel 2000 - Merge cell contents

Dear Experts,

Could you please check the attached file, on Sheet1 it contains a simple table where for Items are entered Comments.

My target would be to merge those comments by items like on Merge sheet, so a kind of pivot where the comment cell values are concetanated.

In the Code section I have also attached an Access VBA function which is able to do this, but I would need this in Excel actually

Option Compare Database
Option Explicit

Function ConcatenateThem(vID As Long) As String
Dim rs As DAO.Recordset, sList As String
Set rs = CurrentDb.OpenRecordset("select * From Table1 Where [Item]=" & vID)
    Do Until rs.EOF
      sList = sList & "," & rs("comments")
    ConcatenateThem = Mid(sList, 2, Len(sList) - 1)
End Function

Open in new window

csehzIT consultantAsked:
Who is Participating?
StephenJRConnect With a Mentor Commented:
I don't know if this custom function works in Excel 2000, but enter, e.g. =Lookup2(A2,Sheet1!$A$2:$C$11) in B2 and copy down:
Function LookUp2(vItem, rData As Range) As String
Dim rFind As Range, s As String, sOut As String

With rData
    Set rFind = .Find(What:=vItem, After:=.Cells(.Cells.Count), LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
    If Not rFind Is Nothing Then
        s = rFind.Address
            sOut = sOut & "," & rFind.Offset(, 2)
            Set rFind = .Find(What:=vItem, After:=rFind, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
         Loop While rFind.Address <> s
    End If
End With
LookUp2 = Mid(sOut, 2)

End Function

Open in new window

csehzIT consultantAuthor Commented:
StephenJR thanks, do you mean the formula should be in B and not maybe in D?

It seems in Excel 2000 the "SearchFormat:=False" should be removed because brings error including that, so in the attached file tried but somehow does not bring value. I assume so probably some version issue
csehzIT consultantAuthor Commented:
Sorry anyway I am stupid because for sure to B2 has to put the formula and copy down on Merge sheet. But unfortunately it is still true that for me just it results a blank cell.
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

So, actually, you just want VBA code to perform merge from "fixed sheet fixed column" into "fixed sheet fixed column"
(from your given file  Sheet1 Column A and C -> Merge Column A and B)

or you want it in some kind of function?
csehzIT consultantAuthor Commented:
The target would be on Merge sheet in colum B a function, which gives such result based on Sheet1

Item      AllComments
100      Comment1,Comment2,Comment3,Comment4
101      Comment1,Comment2,Comment3
102      Comment1
103      Comment1,Comment2

But in Excel 2000, as I assume Stephen's function would work in new version.
Sorry, it must be an xl2000 issue, though I can't see anything obvious. Do you happen to know if you can use UDFs in that version - I've no reason to think you can't, but don't know.
csehzIT consultantAuthor Commented:
StephenJR, yes sure a version problem, and I think that reliable I could test your code on my machine only, when the company implement the new Excel here. Anyway it is in progress just not sure when, so I accept your solution
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.