Unique Value Nightmare!

M

MDX2013

New Member
Joined
Jan 2, 2018
Messages
8
Hi All! I am having trouble with formulas. One formula i have wont work, and I need a creative formula.

Columns A and B: I have a long list of values, some will be repeated up to three times, some wont be repeated at all. The number of values in the list will vary every tie I use the spreadsheet, so I have chosen formula to look up to cell A99. I need to create a new column of just the unique values. This formula I am having trouble with. See below.

Column C: After I get the unique values, then I need to put them in ascending order. (I have this figured out)

Column D: NEED FORMULA: I need a new column with two rows in between the ascending values. I have no clue how to do this part.

Column A:
17091780-02A
17091555-01A
17091555-01A
17091555-01A
17091555-01ADUP
17091555-01ADUP
17091555-02A
17091555-02A
17091559-01A
17091559-01A
17091559-02A
17091559-02A
17091559-02A

Column B:
17091780-02A
17091555-01A
17091555-01ADUP
17091555-02A
17091559-01A
17091559-02A
Formula used as an array: B2=
=IFERROR(INDEX($A$2:$A$99,MATCH(SMALL(NOT($A$2:$A$99="")*IF(ISNUMBER($A$2:$A$99),COUNTIF($A$2:$A$99,"<="&$A$2:$A$99),COUNTIF($A$2:$A$99,"<="&$A$2:$A$99)+SUM(--ISNUMBER($A$2:$A$99))),ROWS($A$2:A2)+SUM(--ISBLANK($A$2:$A$99))),NOT($A$2:$A$99="")*IF(ISNUMBER($A$2:$A$99),COUNTIF($A$2:$A$99,"<="&$A$2:$A$99),COUNTIF($A$2:$A$99,"<="&$A$2:$A$99)+SUM(--ISNUMBER($A$2:$A$99))),0)),"")Trouble with the formula: it is not returning the first value from column a "17091780-01A". I am not sure if this because it is already a unique value or if there is a error in the formula.




Column C:
17091555-01A
17091555-01ADUP
17091555-02A
17091559-01A
17091559-02A
17091780-02A

Final Column:
17091555-01A


17091555-01ADUP


17091555-02A


17091559-01A
(And so on...)

I know this is a lengthy question, but I hope someone will be able to step to the challenge ;)
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Sort by date Sort by votes
(With some help from Eric W at this link: https://www.mrexcel.com/forum/excel...nique-values-multiple-columns-one-column.html)
Try this macro:
Code: 
Sub MDX2013()
    Dim MyDict As Object, MyCols As Variant, OutCol As String, LastRow As Long
    Dim InputSh As Worksheet, OutputSh As Worksheet
    Dim x As Variant, i As Long, MyData As Variant
    Dim rng As Range
    Dim y As Long
    y = 1
    Set MyDict = CreateObject("Scripting.Dictionary")
    Set InputSh = Sheets("Sheet1")
    MyCols = Array("A", "B")
    Set OutputSh = Sheets("Sheet1")
    OutCol = "C"
    For Each x In MyCols
        LastRow = InputSh.Cells(Rows.Count, x).End(xlUp).Row
        MyData = InputSh.Range(x & "1:" & x & LastRow).Value
        For i = 1 To UBound(MyData)
            If MyData(i, 1) <> "" Then MyDict(MyData(i, 1)) = 1
        Next i
    Next x
    OutputSh.Range(OutCol & "1").Resize(MyDict.Count, 1).Value = WorksheetFunction.Transpose(MyDict.keys)
    Range("C1:C" & Range("C" & Rows.Count).End(xlUp).Row).Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("C1:C" & Range("C" & Rows.Count).End(xlUp).Row), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("C1:C" & Range("C" & Rows.Count).End(xlUp).Row)
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    For Each rng In Range("C1:C" & Range("C" & Rows.Count).End(xlUp).Row)
        Range("D" & y) = rng
        y = y + 2
    Next rng
End Sub
 
Upvote 0
I am creating a template for others to use. I am trying to make it as simple as possible (just copying in the raw data), without the use of Macros. Is this possible in a formula?
 
Upvote 0
Maybe...


A​
B​
1​
List​
SortedUniqueList​
2​
17091780-02A​
17091555-01A​
3​
17091555-01A​
17091555-01ADUP​
4​
17091555-01A​
17091555-02A​
5​
17091555-01A​
17091559-01A​
6​
17091555-01ADUP​
17091559-02A​
7​
17091555-01ADUP​
17091780-02A​
8​
17091555-02A​
9​
17091555-02A​
10​
17091559-01A​
11​
17091559-01A​
12​
17091559-02A​
13​
17091559-02A​
14​
17091559-02A​
15​

Array formula in B2 copied down
=IFERROR(INDEX(A$2:A$99,MATCH(0,(A$2:A$99="")+COUNTIF(A$2:A$99,"<"&A$2:A$99)-SUM(COUNTIF(B$1:B1,A$2:A$99)),0)),"")
Ctrl+Shift+Enter, not just Enter

M.
 
Upvote 0
Hi,

I agree with Mumps that a VBA solution is probably better (and nice to see some of my code is still being referenced! :)). But yes, you can do this with just formulas. For example:

ABCDE
1Column AColumn BColumn CColumn DColumn E
217091780-02A17091780-02A17091555-01A17091555-01A17091555-01A
317091555-01A17091555-01A17091555-01ADUP
417091555-01A17091555-01ADUP17091555-02A
517091555-01A17091555-02A17091559-01A17091555-01ADUP17091555-01ADUP
617091555-01ADUP17091559-01A17091559-02A
717091555-01ADUP17091559-02A17091780-02A
817091555-02A17091555-02A17091555-02A
917091555-02A
1017091559-01A
1117091559-01A17091559-01A17091559-01A
1217091559-02A
1317091559-02A
1417091559-02A17091559-02A17091559-02A
15
16
1717091780-02A17091780-02A
18
19
20

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet5​

Worksheet Formulas
CellFormula
D2=IF(MOD(ROW(),3)=2,INDEX(C:C,(ROW()+4)/3),"")

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
B2{=IF(B1="","",IFERROR(INDEX($A$2:$A$99,SMALL(IF($A$2:$A$99<>"",IF(COUNTIF($B$1:$B1,$A$2:$A$99)=0,ROW($A$2:$A$99)-ROW($A$2)+1)),1)),""))}
C2{=IFERROR(INDEX($A$2:$A$99,MATCH(0,COUNTIF($A$2:$A$99,"<"&$A$2:$A$99)-SUM(COUNTIF($A$2:$A$99,$C$1:$C1)),0)),"")}
E2{=IF(MOD(ROW(),3)=2,IFERROR(INDEX($A$2:$A$99,MATCH(0,COUNTIF($A$2:$A$99,"<"&$A$2:$A$99)-SUM(COUNTIF($A$2:$A$99,$E$1:$E1)*($E$1:$E1<>"")),0)),""),"")}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>




I didn't really try to decipher your B2 formula, instead I used one of the several other versions I know. In C2, I used another formula to create a sorted unique list. It doesn't use column B at all, so you could remove that if you want. Then in D2 is your list with the blank rows inserted. Or if you prefer, you can even use the formula in E2 that only needs column A to work. I assume that you left the blank rows to put something else in there. The formulas should still work as long as whatever you put in the blank rows aren't anything from column A.

Hope there's something useful in there for you!
 
Upvote 0
Sorry i missed you do want column D (Final column)

Maybe...


A​
B​
C​
D​
1​
List​
SortedUniqueList​
FinalColumn​
2​
17091780-02A​
17091555-01A​
17091555-01A​
3​
17091555-01A​
17091555-01ADUP​
4​
17091555-01A​
17091555-02A​
5​
17091555-01A​
17091559-01A​
17091555-01ADUP​
6​
17091555-01ADUP​
17091559-02A​
7​
17091555-01ADUP​
17091780-02A​
8​
17091555-02A​
17091555-02A​
9​
17091555-02A​
10​
17091559-01A​
11​
17091559-01A​
17091559-01A​
12​
17091559-02A​
13​
17091559-02A​
14​
17091559-02A​
17091559-02A​
15​
16​
17​
17091780-02A​
18​
19​
20​
21​

Array formula in B2 copied down (see post 4)
=IFERROR(INDEX(A$2:A$99,MATCH(0,(A$2:A$99="")+COUNTIF(A$2:A$99,"<"&A$2:A$99)-SUM(COUNTIF(B$1:B1,A$2:A$99)),0)),"")
Ctrl+Shift+Enter, not just Enter

Formula in D2 copied down
=IF(3*COUNTIF(B$2:B$99,"?*")>=ROWS(D$2:D2),IF(MOD(ROWS(D$2:D2),3)=1,INDEX(B$2:B$99,INT((ROWS(D$2:D2)-1)/3)+1),""),"")

Hope this helps

M.
 
Upvote 0
Or without intermediary columns...

A​
B​
C​
D​
1​
List​
FinalColumn​
2​
17091780-02A​
17091555-01A​
3​
17091555-01A​
4​
17091555-01A​
5​
17091555-01A​
17091555-01ADUP​
6​
17091555-01ADUP​
7​
17091555-01ADUP​
8​
17091555-02A​
17091555-02A​
9​
17091555-02A​
10​
17091559-01A​
11​
17091559-01A​
17091559-01A​
12​
17091559-02A​
13​
17091559-02A​
14​
17091559-02A​
17091559-02A​
15​
16​
17​
17091780-02A​
18​
19​
20​
21​

Array formula in D2 copied down
=IF(MOD(ROWS(D$2:D2),3)=1,IFERROR(INDEX(A$2:A$99,MATCH(0,(A$2:A$99="")+COUNTIF(A$2:A$99,"<"&A$2:A$99)-SUM(COUNTIF(D$1:D1,A$2:A$99)),0)),""),"")
Ctrl+Shift+Enter

M.
 
Upvote 0
You have a bunch of formula solutions to sift through, but if you are up for a macro solution, here is another one that you can consider (this does the sorting as well as finding the unique entries)...
Code: 
[table="width: 500"]
[tr]
	[td]Sub Uniques()
  Dim R As Long, C As Long, X As Long, Data As Variant
  Data = Range("A1", Columns("B").Find("*", , xlValues, , xlRows, xlPrevious))
  Columns("C:D").Clear
  With CreateObject("Scripting.Dictionary")
    For R = 1 To UBound(Data, 1)
      For C = 1 To UBound(Data, 2)
        If Len(Data(R, 1)) Then .Item(Data(R, C)) = 1
      Next
    Next
    Data = .Keys
  End With
  With CreateObject("System.Collections.ArrayList")
    For R = 0 To UBound(Data)
      .Add Data(R)
    Next
   .Sort
    Range("C1").Resize(.Count) = Application.Transpose(.ToArray)
    Range("D1").Resize(3 * .Count - 2) = Application.Transpose(Split(Join(.ToArray, Space(3))))
  End With
End Sub[/td]
[/tr]
[/table]
 
Upvote 0
With what i am creating, I have to keep a template.
You seem very knowledgeable! Do you have a quick formula solution to go from Column A to Column B:
Column AColumn B
a
a
a
b
bb
c
c
b

<tbody>
</tbody>
 
Upvote 0
Sure, consider:

AB
1Column AColumn B
2aa
3a
4b
5bb
6c
7c
8cd
9d
10
11d
12
13

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet7​

Worksheet Formulas
CellFormula
B2=INDEX(A:A,3*INT(ROWS($B$1:$B2)/2)-1)&""

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>



Do you want to use this on column D of your previous request? If so, I could probably come up with a formula that creates this directly. But if you want column D, plus this one, it's probably best to use this formula. You don't really want 2 sets of array formulas if you can get by with just one set.

And of course if this is an unrelated question, then this should work fine by itself.
 
Upvote 0
You must log in or register to reply here.

Similar threads

drom
  • Solved
Count unique values for filtered column
Replies
1
Views
152
myall_blues
myall_blues
L
  • Solved
Sum values(column C) based on unique value(column A) after filtering(column B)
2
Replies
11
Views
322
Fluff
Fluff
S
  • Solved
Count Unique Values in a column based on two other columns
Replies
3
Views
186
Fluff
Fluff
P
  • Solved
How to count all unique values based on another column
Replies
6
Views
648
Fluff
Fluff
L
  • Solved
Count of Unique Values in a Column of data with Comma Delimitted, Multiple Cell Values
Replies
5
Views
105
Fluff
Fluff

Forum statistics

Threads
1,214,989
Messages
6,122,622
Members
449,093
Latest member
catterz66

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top