Making unique values and reducing into one cell
Results 1 to 9 of 9

Thread: Making unique values and reducing into one cell
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Apr 2015
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Making unique values and reducing into one cell

    Hello,

    Since a while I am trying to do an automatic way to make unique and merging from first to last value into one cell.
    Attached there is a sample data sheet.
    https://www.dropbox.com/s/1hs3cp0mpt...nges.xlsx?dl=0

    Please any help if possible.

    I have tried:
    - Pivot tables
    - Countif + concatenate
    - ASAP utilities with Transpose Columns/rows

    but all of these are still a lot of manual work to make.

    Thank You

    Jon

  2. #2
    Board Regular XOR LX's Avatar
    Join Date
    Jul 2012
    Location
    Turin, Italy
    Posts
    4,517
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Making unique values and reducing into one cell

    Hi.

    In G2, used to determine the expected number of returns:

    =SUMPRODUCT(N(FREQUENCY(MATCH(A4:A25,A4:A24,0),ROW(A4:A25)-MIN(ROW(A4:A25))+1)>0))

    In G4:

    =IF(ROWS($1:1)>G$2,"",INDEX(A:A,AGGREGATE(15,6,ROW(A$4:A$25)/(FREQUENCY(MATCH(A$4:A$25,A$4:A$25,0),ROW(A$4:A$25)-MIN(ROW(A$4:A$25))+1)>0),ROWS($1:1))))

    Copy down until you start to get blanks for the results.

    In H4:

    =IF(G4="","",INDEX(B:B,MATCH($G4,$A:$A,0)))

    Again, copy down and also right into column I.

    In J4:

    =IF(G4="","",TEXT(MMULT(AGGREGATE({14,15},6,D$4:D$25/(A$4:A$25=G4),1),10^{0;4}),"0-0000"))

    Again, copy down as required.

    Regards
    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    New Member
    Join Date
    Apr 2015
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Making unique values and reducing into one cell

    thank You for the formulas.
    I will give them a try.

    The idea is that are over 70.000 rows like the sample provided.

    Any other solution?

  4. #4
    Board Regular XOR LX's Avatar
    Join Date
    Jul 2012
    Location
    Turin, Italy
    Posts
    4,517
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Making unique values and reducing into one cell

    Why? Is there a problem with that set-up that you've noticed? Or were you wanting a VBA solution?

    Also, a Pivot Table may be ideal here. Have you looked into that as a possibility?

    Regards
    Advanced Excel Techniques: http://excelxor.com/

  5. #5
    New Member
    Join Date
    Apr 2015
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Making unique values and reducing into one cell

    Hi XOR LX,

    The method you provided has worked greatly since then.

    I would like to ask you if there is a reverse solution like:
    2005-2008 to convert into 2005-2006-2007-2008
    2001-2005 to convert into 2001-2002-2003-2004-2005
    2010-2013 to convert into 2010-2011-2012-2013
    1995-1997 to convert into 1995-1996-1997

    Thank You

    Regards,

  6. #6
    Board Regular XOR LX's Avatar
    Join Date
    Jul 2012
    Location
    Turin, Italy
    Posts
    4,517
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Making unique values and reducing into one cell

    Hi again. Can you add some cell references to your latest request? Are the returns to be placed into a single cell, e.g. in B1:

    2005-2006-2007-2008

    Or into separate cells, e.g.:

    B1: 2005
    C1: 2006
    D1: 2007
    E1: 2008

    ?

    If the former, you will require VBA. If the latter, we can do it with worksheet formulas alone.

    Regards
    Advanced Excel Techniques: http://excelxor.com/

  7. #7
    New Member
    Join Date
    Apr 2015
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Making unique values and reducing into one cell

    Hi XOR LX,

    Thank You for your fast reply and sorry for not explaining well.

    the cell A1: 2005-2008 and the result can be next to it like B1 and inside the cell: 2005-2006-2007-2008
    and so on for the others A2: 2001-2005 -> B2: 2001-2002-2003-2004-2005

    If there is no formula way to do it then I have to welcome VBA solution .

    I appreciate your help.

    Thank You

  8. #8
    Board Regular XOR LX's Avatar
    Join Date
    Jul 2012
    Location
    Turin, Italy
    Posts
    4,517
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Making unique values and reducing into one cell

    Based on a list beginning in A1 and assuming the entries in that column are either blank or of the precise format:

    ####-####

    where # are numeric:

    Code:
    Sub Dates()
    
    Dim LRow As Long, Gap As Long
    
    LRow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("B1:B" & LRow).ClearContents
    
    For i = 1 To LRow
        If Range("A" & i) <> "" Then
            Gap = Right(Range("A" & i).Value, 4) - Left(Range("A" & i).Value, 4)
            For j = 0 To Gap
                Range("B" & i).Value = Range("B" & i).Value & "-" & Left(Range("A" & i).Value, 4) + j
            Next j
            Range("B" & i).Value = Mid(Range("B" & i).Value, 2, Len(Range("B" & i).Value))
        End If
    Next i
    
    End Sub

    Regards
    Last edited by XOR LX; Jan 5th, 2016 at 06:47 AM.
    Advanced Excel Techniques: http://excelxor.com/

  9. #9
    New Member
    Join Date
    Apr 2015
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Making unique values and reducing into one cell

    The VBA code worked great. It is a great help and very clean.

    I would like to post also a solution from a guy of Asap Utilities which is also very handy.
    Also it doesn't need the VBA.

    You can see the excel file:
    https://www.dropbox.com/s/p2x2znas0p...nges.xlsx?dl=0

    Many Thanks

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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