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

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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...


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]
A​
[/td][td="bgcolor: #DCE6F1"]
B​
[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]
1​
[/td][td]
List​
[/td][td]
SortedUniqueList​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
2​
[/td][td]
17091780-02A​
[/td][td]
17091555-01A​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
3​
[/td][td]
17091555-01A​
[/td][td]
17091555-01ADUP​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
4​
[/td][td]
17091555-01A​
[/td][td]
17091555-02A​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
5​
[/td][td]
17091555-01A​
[/td][td]
17091559-01A​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
6​
[/td][td]
17091555-01ADUP​
[/td][td]
17091559-02A​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
7​
[/td][td]
17091555-01ADUP​
[/td][td]
17091780-02A​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
8​
[/td][td]
17091555-02A​
[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
9​
[/td][td]
17091555-02A​
[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
10​
[/td][td]
17091559-01A​
[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
11​
[/td][td]
17091559-01A​
[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
12​
[/td][td]
17091559-02A​
[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
13​
[/td][td]
17091559-02A​
[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
14​
[/td][td]
17091559-02A​
[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
15​
[/td][td][/td][td][/td][/tr]
[/table]

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
Column AColumn BColumn CColumn DColumn E
17091780-02A17091780-02A17091555-01A17091555-01A17091555-01A
17091555-01A17091555-01A17091555-01ADUP
17091555-01A17091555-01ADUP17091555-02A
17091555-01A17091555-02A17091559-01A17091555-01ADUP17091555-01ADUP
17091555-01ADUP17091559-01A17091559-02A
17091555-01ADUP17091559-02A17091780-02A
17091555-02A17091555-02A17091555-02A
17091555-02A
17091559-01A
17091559-01A17091559-01A17091559-01A
17091559-02A
17091559-02A
17091559-02A17091559-02A17091559-02A
17091780-02A17091780-02A

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]11[/TD]

[TD="align: center"]12[/TD]

[TD="align: center"]13[/TD]

[TD="align: center"]14[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

</tbody>
Sheet5​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]=IF(MOD(ROW(),3)=2,INDEX(C:C,(ROW()+4)/3),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]{=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)),""))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]{=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)),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]{=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)),""),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]




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


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]
A​
[/td][td="bgcolor: #DCE6F1"]
B​
[/td][td="bgcolor: #DCE6F1"]
C​
[/td][td="bgcolor: #DCE6F1"]
D​
[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]
1​
[/td][td]
List​
[/td][td]
SortedUniqueList​
[/td][td][/td][td]
FinalColumn​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
2​
[/td][td]
17091780-02A​
[/td][td]
17091555-01A​
[/td][td][/td][td]
17091555-01A​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
3​
[/td][td]
17091555-01A​
[/td][td]
17091555-01ADUP​
[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
4​
[/td][td]
17091555-01A​
[/td][td]
17091555-02A​
[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
5​
[/td][td]
17091555-01A​
[/td][td]
17091559-01A​
[/td][td][/td][td]
17091555-01ADUP​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
6​
[/td][td]
17091555-01ADUP​
[/td][td]
17091559-02A​
[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
7​
[/td][td]
17091555-01ADUP​
[/td][td]
17091780-02A​
[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
8​
[/td][td]
17091555-02A​
[/td][td][/td][td][/td][td]
17091555-02A​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
9​
[/td][td]
17091555-02A​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
10​
[/td][td]
17091559-01A​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
11​
[/td][td]
17091559-01A​
[/td][td][/td][td][/td][td]
17091559-01A​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
12​
[/td][td]
17091559-02A​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
13​
[/td][td]
17091559-02A​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
14​
[/td][td]
17091559-02A​
[/td][td][/td][td][/td][td]
17091559-02A​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
15​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
16​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
17​
[/td][td][/td][td][/td][td][/td][td]
17091780-02A​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
18​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
19​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
20​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
21​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

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

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]
A​
[/td][td="bgcolor: #DCE6F1"]
B​
[/td][td="bgcolor: #DCE6F1"]
C​
[/td][td="bgcolor: #DCE6F1"]
D​
[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]
1​
[/td][td]
List​
[/td][td][/td][td][/td][td]
FinalColumn​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
2​
[/td][td]
17091780-02A​
[/td][td][/td][td][/td][td]
17091555-01A​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
3​
[/td][td]
17091555-01A​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
4​
[/td][td]
17091555-01A​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
5​
[/td][td]
17091555-01A​
[/td][td][/td][td][/td][td]
17091555-01ADUP​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
6​
[/td][td]
17091555-01ADUP​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
7​
[/td][td]
17091555-01ADUP​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
8​
[/td][td]
17091555-02A​
[/td][td][/td][td][/td][td]
17091555-02A​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
9​
[/td][td]
17091555-02A​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
10​
[/td][td]
17091559-01A​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
11​
[/td][td]
17091559-01A​
[/td][td][/td][td][/td][td]
17091559-01A​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
12​
[/td][td]
17091559-02A​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
13​
[/td][td]
17091559-02A​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
14​
[/td][td]
17091559-02A​
[/td][td][/td][td][/td][td]
17091559-02A​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
15​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
16​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
17​
[/td][td][/td][td][/td][td][/td][td]
17091780-02A​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
18​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
19​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
20​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
21​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

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:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[/TR]
[TR]
[TD]a
[/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]b[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]b[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]c[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]c[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Sure, consider:

AB
Column AColumn B
aa
a
b
bb
c
c
cd
d
d

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]

</tbody>
Sheet7​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=INDEX(A:A,3*INT(ROWS($B$1:$B2)/2)-1)&""[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



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

L
  • Solved
Lookup and retrieve value based on table code (2)
Replies
3
Views
428
Peter_SSs
Peter_SSs
M
  • Solved
Sum only the unique values from one column within a range of duplicate values from another column
Replies
7
Views
238
MARZIOTULLIO
M
A
  • Solved
Count unique IDs with a wildcard criteria for another column
Replies
2
Views
161
ausswe
A
K
  • Solved
Count number of max values per unique value and column heading?
Replies
5
Views
285
RobP
R
PhBarreto
  • Solved
Help WITH SUMPRODUCT
2
Replies
10
Views
110
Fluff
Fluff

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

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