Solved

VBA Excel 2000 - Merge cell contents

Posted on 2011-03-18
7
525 Views
Last Modified: 2012-05-11
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

thanks,
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)
    rs.MoveFirst
    Do Until rs.EOF
      sList = sList & "," & rs("comments")
      rs.MoveNext
    Loop
    ConcatenateThem = Mid(sList, 2, Len(sList) - 1)
End Function

Open in new window

MergeComments.xls
0
Comment
Question by:csehz
  • 4
  • 2
7 Comments
 
LVL 24

Accepted Solution

by:
StephenJR earned 500 total points
ID: 35165932
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
        Do
            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

0
 
LVL 1

Author Comment

by:csehz
ID: 35171038
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
MergeComments.xls
0
 
LVL 1

Author Comment

by:csehz
ID: 35171098
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.
MergeComments.xls
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 11

Expert Comment

by:JoeNuvo
ID: 35171213
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?
0
 
LVL 1

Author Comment

by:csehz
ID: 35171228
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.
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 35179536
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.
0
 
LVL 1

Author Closing Comment

by:csehz
ID: 35189561
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
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question