2

I have a database formatting problem in which I am trying to concatenate column "B" rows based on column "A" rows. Like So:

https://docs.google.com/spreadsheet/ccc?key=0Am8J-Fv99YModE5Va3hLSFdnU0RibmQwNVFNelJCWHc

Sorry I couldn't post a picture. I don't have enough reputation points YET. I'LL Get them eventually though

So I'd like to solve this problem within Excel or Access. Its currently an access database, but I can export it to excel easily. As you can see, I want to find "userid" in column A and where there are multiple column A's such as "shawn" I'd like to combine the multiple instances of shawn and concatenate property num as such.

Even though there are multiple instances of column A still, I could just filter all unique instances of the table later. My concern is how to concatenate column B with a "|" in the middle if column A has multiple instances.

This is just a segment of my data (There is a lot more), so I would be very thankful for your help.

The pseudo code in my head so far is:

If( Column A has more than one instance) Then Concatenate(Column B with "#"+ "|" +"#")

I'm also wondering if there is a way to do this on access with grouping.

Well Anyways, PLEASE HELP.

jcayco
  • 41
  • 1
  • 5

2 Answers2

0

I believe you can solve this with an SQL GROUP BY function. At least, here's how I'd do it in MySQL or similar:

SELECT userid, GROUP_CONCAT(propertynum SEPARATOR '|') FROM Names GROUP BY userid

as described in this stack overflow post: How to use GROUP BY to concatenate strings in MySQL?

Here's a link on how to use SQL in MS Access: http://www.igetit.net/newsletters/Y03_10/SQLInAccess.aspx

Unfortunately there is not a GROUP_CONCAT function in MSAccess, but this other SO post explains some ways round that: is there a group_concat function in ms-access?

Community
  • 1
  • 1
Sam Joseph
  • 4,584
  • 4
  • 31
  • 47
0

In excel we can achieve it easily by custom function in vba module. Hopefully using vba(Macros) is not an issue for you.

Here is the code for the function which can be added in vba. (Press Alt+F11, this will take you to visual editor, right click the project and add a module. Add the below code in module)

Public Function ConcatenatePipe(ByVal lst As Range, ByVal values As Range, ByVal name As Range) As String
ConcatenatePipe = ""
Dim i As Integer

For i = 1 To lst.Count
    If name.Value = lst.Item(i).Value Then ConcatenatePipe = ConcatenatePipe & "|" & values.Item(i).Value
Next
ConcatenatePipe = Mid(ConcatenatePipe, 2)
End Function

This function you can use in excel in F Column of your example. Copy the below formulla in F2 and the copy paste the cell to rest of F column. =ConcatenatePipe($A$2:$A$20,$B$2:$B$20,E2)

Rohit Agrawal
  • 335
  • 1
  • 2
  • 9
  • Unfortunately, I have no idea how to use vba(Macros). I'm actually looking it up now. I appreciate your help. – jcayco Dec 23 '12 at 23:42