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

1. ## 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

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. ## 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

3. ## 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. ## 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

5. ## 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. ## 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

7. ## 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 .

Thank You

8. ## 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

9. ## 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

## User Tag List

#### Posting Permissions

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