Search for multiple values w/ one criteria but show unique values only

[danhendo888]

	A	B	C	D	E

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #000000"]Invoice Number[/TD]
[TD="bgcolor: #000000"]Date of Payment[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1-Jul[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1-Jul[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4-Jul[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4-Jul[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

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

[TD="align: center"]8[/TD]
[TD="bgcolor: #000000"]Invoice Number[/TD]
[TD="bgcolor: #000000"]Date of Payment[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1/07/19[/TD]
[TD="align: right"]1/07/19[/TD]
[TD="align: right"]4/07/19[/TD]
[TD="align: right"]4/07/19[/TD]

</tbody>

Sheet1​

[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: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B9[/TH]
[TD="align: left"]{=INDEX($B$2:$B$5,SMALL(IF($A$2:$A$5=$A9,ROW($B$2:$B$5)-ROW($B$2)+1),COLUMNS($B9:B9)))}[/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]

Hello,

Invoice 1 was paid on two different dates.
How do I show only the unique dates instead of the way I'm currently doing (in B9)

FYI
Data is extracted data and always will show duplicate dates as shown above.

Thank you

 

[Fazza]

with a pivot table. assuming you have headers, pick a cell in the data, ALT-D-P to start the wizard, make the fields row fields in the same order as source table

another way would be a query. ALT-D-N-N & follow the wizard. I can be more specific if you need, or google for info

 

[AhoyNC]

Try

Excel Workbook
	A	B	C	D
1	Invoice Number	Date of Payment
2	1	7/1/2019
3	1	7/1/2019
4	1	7/4/2019
5	1	7/4/2019
6
7
8	Invoice Number	Date of Payment
9	1	7/1/2019	7/4/2019
Sheet

 

[sandy666]

or

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td]Source[/td][td][/td][td][/td][td]Pivot (Fazza)[/td][td][/td][td][/td][td]PowerQuery[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Invoice Number[/td][td=bgcolor:#5B9BD5]Date of Payment[/td][td][/td][td=bgcolor:#DDEBF7]Invoice Number[/td][td=bgcolor:#DDEBF7]Date of Payment[/td][td][/td][td=bgcolor:#70AD47]Invoice Number[/td][td=bgcolor:#70AD47]Date of Payment[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

1​

[/td][td=bgcolor:#DDEBF7]

01/07/2019​

[/td][td][/td][td]

1​

[/td][td]

01/07/2019​

[/td][td][/td][td=bgcolor:#E2EFDA]

1​

[/td][td=bgcolor:#E2EFDA]

01/07/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

1​

[/td][td]

01/07/2019​

[/td][td][/td][td]

1​

[/td][td]

04/07/2019​

[/td][td][/td][td]

1​

[/td][td]

04/07/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

1​

[/td][td=bgcolor:#DDEBF7]

04/07/2019​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

1​

[/td][td]

04/07/2019​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]
M-code for PowerQuery (Get&Transform)

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    RD = Table.Distinct(Source),
    Type = Table.TransformColumnTypes(RD,{{"Date of Payment", type date}})
in
    Type[/SIZE]

 

[danhendo888]

with a pivot table. assuming you have headers, pick a cell in the data, ALT-D-P to start the wizard, make the fields row fields in the same order as source table
another way would be a query. ALT-D-N-N & follow the wizard. I can be more specific if you need, or google for info

or
M-code for PowerQuery (Get&Transform)

Thank you, I will google/youtube PowerQuery for more info.

{=IFERROR(INDEX($B$2:$B$5,SMALL(IF(FREQUENCY(IF($A$2:$A$5=$A9,$B$2:$B$5),$B$2:$B$5),ROW($B$2:$B$5)-ROW($B$2)+1),COLUMNS($B$9:B9))),"")}

Thank you, works perfectly.

 

[sandy666]

You are welcome

Have a nice day

---

Horizontal option:

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Invoice Number[/td][td=bgcolor:#70AD47]Date of Payment.1[/td][td=bgcolor:#70AD47]Date of Payment.2[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

1​

[/td][td=bgcolor:#E2EFDA]

01/07/2019​

[/td][td=bgcolor:#E2EFDA]

04/07/2019​

[/td][/tr]
[/table]

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"Invoice Number"}, {{"Count", each _, type table}}),
    Extract = Table.TransformColumns(Table.AddColumn(Group, "Date of Payment", each List.Distinct(Table.Column([Count],"Date of Payment"))), {"Date of Payment", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    Parse = Table.TransformColumns(Table.SplitColumn(Extract, "Date of Payment", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Date of Payment.1", "Date of Payment.2"}),{{"Date of Payment.1", each Date.From(DateTimeZone.From(_)), type date}, {"Date of Payment.2", each Date.From(DateTimeZone.From(_)), type date}})
in
    Parse[/SIZE]