Making unique values and reducing into one cell

[mistereri]

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/1hs3cp0mptf85k8/reducing-ranges.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

 

[XOR LX]

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

 

[mistereri]

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?

 

[XOR LX]

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

 

[mistereri]

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,

 

[XOR LX]

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

 

[mistereri]

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

 

[XOR LX]

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:

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

 

[mistereri]

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/p2x2znas0psv1hb/reducing-ranges.xlsx?dl=0

Many Thanks