Results 1 to 11 of 11

Thread: formating

  1. #1
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts
    hi all,

    i have included a sample of the data initial format and what the new format should looks like.


    dubdub
    Attached Files Attached Files
    TIA
    dubdub

  2. #2
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='dubdub' post='792657' date='09-Sep-2009 13:32']hi all,

    i have included a sample of the data initial format and what the new format should looks like.


    dubdub[/quote]
    Can you explain what is the criteria to get output. It is hard to understand the requirement.
    Regards
    Prasad

  3. #3
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='prasad' post='792660' date='09-Sep-2009 09:53']Can you explain what is the criteria to get output. It is hard to understand the requirement.[/quote]

    Thanks Prasad,

    i wan have one record for each well(column a) and concatenate the rows data if they have different values for the same well.

    dubdub
    TIA
    dubdub

  4. #4
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Would a pivot table suit your needs?

    see attached in sheet1
    Attached Files Attached Files

  5. #5
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='stans' post='792670' date='09-Sep-2009 11:20']Would a pivot table suit your needs?

    see attached in sheet1[/quote]

    Thanks Stans,

    i have tried the pivot table but the data i deal with is very huge and reformatin using concatenate seems to cut the number of lines termendously and help printing the table.
    TIA
    dubdub

  6. #6
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts
    hi all,

    any help i can get from the code experts is highly appreciated.

    dubdub,
    TIA
    dubdub

  7. #7
    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
    Check out my My VlookupAll Function.

    Add the function to a general module in the workbook.

    Then you can fill in the unique items in A14:awhatever (you can get this from a pivot table or a using adv filtering),
    then you can put in B14:
    =vlookupall($A14,$A$3:$A$10,COLUMN()-1)

    Then copy B14 to B14:E whatever

    Steve

  8. #8
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thank you very much Steve.It works perfect.

    dubdub
    TIA
    dubdub

  9. #9
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='sdckapr' post='792996' date='11-Sep-2009 15:40']Check out my My VlookupAll Function.

    Add the function to a general module in the workbook.

    Then you can fill in the unique items in A14:awhatever (you can get this from a pivot table or a using adv filtering),
    then you can put in B14:
    =vlookupall($A14,$A$3:$A$10,COLUMN()-1)

    Then copy B14 to B14:E whatever

    Steve[/quote]

    Hi Steve,

    but i have encounterd a problem, if the unique value has same multiple values, it still concatenate them, is there a way to have put a one reading or value if the data is the sme and conactenate only if the data is not. for example if for a unique value say 111 the look up range has 3 values column b 211,211,211 and column c it has abc, aaa,dcb, currently the results are 111 211,211,211 abc,aaa,dcb and what i need is 111 211 abc,aaa,dcb.

    dubdub
    TIA
    dubdub

  10. #10
    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
    Try this function which works like the other but only displays the unique items...

    [codebox]
    Option Explicit
    Function VLookupUnique(vValue, rngAll As Range, _
    iCol As Integer, Optional sSep As String = ", ")
    Dim rCell As Range
    Dim rng As Range
    On Error GoTo ErrHandler

    Set rng = Intersect(rngAll, rngAll.Columns(1))
    For Each rCell In rng
    If rCell.Value = vValue Then
    If InStr(VLookupUnique, _
    rCell.Offset(0, iCol).Value) = 0 Then
    VLookupUnique = VLookupUnique & sSep & _
    rCell.Offset(0, iCol).Value
    End If
    End If
    Next rCell
    If VLookupUnique = "" Then
    VLookupUnique = CVErr(xlErrNA)
    Else
    VLookupUnique = Right(VLookupUnique, _
    Len(VLookupUnique) - Len(sSep))
    End If
    ErrHandler:
    If Err.Number <> 0 Then VLookupUnique = CVErr(xlErrValue)
    End Function
    [/codebox]

    Steve

  11. #11
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='sdckapr' post='793453' date='15-Sep-2009 22:51']Try this function which works like the other but only displays the unique items...

    [codebox]
    Option Explicit
    Function VLookupUnique(vValue, rngAll As Range, _
    iCol As Integer, Optional sSep As String = ", ")
    Dim rCell As Range
    Dim rng As Range
    On Error GoTo ErrHandler

    Set rng = Intersect(rngAll, rngAll.Columns(1))
    For Each rCell In rng
    If rCell.Value = vValue Then
    If InStr(VLookupUnique, _
    rCell.Offset(0, iCol).Value) = 0 Then
    VLookupUnique = VLookupUnique & sSep & _
    rCell.Offset(0, iCol).Value
    End If
    End If
    Next rCell
    If VLookupUnique = "" Then
    VLookupUnique = CVErr(xlErrNA)
    Else
    VLookupUnique = Right(VLookupUnique, _
    Len(VLookupUnique) - Len(sSep))
    End If
    ErrHandler:
    If Err.Number <> 0 Then VLookupUnique = CVErr(xlErrValue)
    End Function
    [/codebox]

    Steve[/quote]


    Thanks Steve, with all the love to all members, this forum is unique and more unique by you.


    dubdub
    TIA
    dubdub

Posting Permissions

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