Results 1 to 6 of 6
  1. #1
    Star Lounger
    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.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    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 data-filter - advanced filter to extract out the unique items...

    Steve

  4. #4
    Star Lounger
    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?
    Attached Files Attached Files

  5. #5
    WS Lounge VIP sdckapr's Avatar
    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

  6. #6
    Star Lounger
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •