Results 1 to 6 of 6

20080613, 15:34 #1
 Join Date
 Oct 2003
 Posts
 82
 Thanks
 0
 Thanked 0 Times in 0 Posts
Excel Data Manipulation (Excel 2003)
Hi attached is a small sample of what I am trying to do (see original data > desired output). 1. Products are grouped 2. And then Reporting lines are grouped 3. and here's where it gets messy, several or one account gets grouped on one line (using unique Product & Reporting line combo) and then seperated by a comma.
I don't know this is easier to do using Excel or Access either is fine (path of least resistance is preferred). At first I thought it would be easy to import the data into Access and then create a query for the desired output (column c), but I am not sure if this is possible, so maybe Excel can do it better.
Thank you.

20080613, 15:39 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Excel Data Manipulation (Excel 2003)
If you import or link the original data into Access, you can create a totals query that groups by Product and Reporting Line and that uses the Concat function from the attachment to <post#=301,393>post 301,393</post#> to concatenate the corresponding Account values.

20080613, 16:22 #3
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Excel Data Manipulation (Excel 2003)
You may also want to check out my UDF VLOOKUPALL in <post:=395,235>post 395,235</post:>. You could use something like:
=vlookupall(A3,$A$3:$A$33,2)
Copy it down the rows and use datafilter  advanced filter to extract out the unique items...
Steve

20080613, 19:53 #4
 Join Date
 Oct 2003
 Posts
 82
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Excel Data Manipulation (Excel 2003)
hi yes this sort of works see attached, but instead of vlookupall("PR.VP.PTT",A3:B33,2),
I need "PR.VP.PTT" AND " RL.IS.REVOTH.0002". Is this possible?

20080613, 20:12 #5
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Excel Data Manipulation (Excel 2003)
1) to use the VlookupAll as written you could:
In D3 add the formula:
=A3&""&B3
In E3:
=vlookupall(D3,$D$3:$D$33,1)
Copy down the rows.
2) Or you could modify the function to :
<pre>Option Explicit
Function VLookupAll2(vValueA, vValueB, rngAB As Range, iCol As Integer, Optional sSep As String = ", ")
Dim rCell As Range
Dim rng As Range
On Error GoTo ErrHandler
Set rng = Intersect(rngAB, rngAB.Columns(1))
For Each rCell In rng
If rCell.Value = vValueA And _
rCell.Offset(0, 1) = vValueB Then
VLookupAll2 = VLookupAll2 & sSep & _
rCell.Offset(0, iCol).Value
End If
Next rCell
If VLookupAll2 = "" Then
VLookupAll2 = CVErr(xlErrNA)
Else
VLookupAll2 = Right(VLookupAll2, Len(VLookupAll2)  Len(sSep))
End If
ErrHandler:
If Err.Number <> 0 Then VLookupAll2 = CVErr(xlErrValue)
End Function</pre>
And then use:
=vlookupall2(A3,B3,$A$3:$B$33,2)
[If the 2 columns to compare are not always going to be next to each other, you could add another range parameter and use A and B separately]
Steve

20080614, 04:01 #6
 Join Date
 Sep 2002
 Location
 Honolulu, Hawaii, USA
 Posts
 63
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Excel Data Manipulation (Excel 2003)
Aloha,
This looked really similar to a macro I already have so I modified it for you.Try the following code:
Sub GroupAccts()
' Lists all ACCCOUNTs grouped by PRODUCT and REPORTING LINE.
' Assumes data is sorted by PRODUCT and REPORTING LINE.
' Select first PRODUCT, then run macro.
Dim rCell As Range, rOutput As Range, tProd As String, tRept As String, tAcct As String
Set rCell = ActiveCell
If rCell.Offset(1, 0) <> "PRODUCT" Then
MsgBox "Select first PRODUCT, then run macro."
Exit Sub
End If
' set up output location. Currently set to put output titles three rows below end of data.
Set rOutput = Selection.End(xlDown).Offset(3, 0)
rOutput.Formula = "PRODUCT"
rOutput.Offset(0, 1).Formula = "REPORTING LINE"
rOutput.Offset(0, 2).Formula = "ACCCOUNTs"
' loop through PRODUCT and REPORTING LINE
counter = 1
Do While rCell <> ""
tProd = rCell.Formula
tRept = rCell.Offset(0, 1).Formula
tAcct = rCell.Offset(0, 2).Formula
Set rCell = rCell.Offset(1, 0)
Do While tProd = rCell.Formula And tRept = rCell.Offset(0, 1).Formula
tAcct = tAcct & "," & rCell.Offset(0, 2).Formula
Set rCell = rCell.Offset(1, 0)
Loop
rOutput.Offset(counter, 0).Formula = tProd
rOutput.Offset(counter, 1).Formula = tRept
rOutput.Offset(counter, 2).Formula = tAcct
counter = counter + 1
Loop
End Sub
The macro assumes that (1) the data is sorted by PRODUCT and REPORTING LINE, and (2) the active cell is the first PRODUCT.
HTH,
John Jacobson