Solved

VBA Excel 2000 - Merge cell contents

Posted on 2011-03-18
7
523 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
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 
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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
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…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

808 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