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

[danhendo888]

	A	B	C	D	E
1	Invoice Number	Date of Payment
2	1	1-Jul
3	1	1-Jul
4	1	4-Jul
5	1	4-Jul
6
7
8	Invoice Number	Date of Payment
9	1	1/07/19	1/07/19	4/07/19	4/07/19

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

Sheet1​

Array Formulas Cell Formula B9 {=INDEX($B$2:$B$5,SMALL(IF($A$2:$A$5=$A9,ROW($B$2:$B$5)-ROW($B$2)+1),COLUMNS($B9:B9)))} <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>

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

Source			Pivot (Fazza)			PowerQuery
Invoice Number	Date of Payment		Invoice Number	Date of Payment		Invoice Number	Date of Payment
1​	01/07/2019​		1​	01/07/2019​		1​	01/07/2019​
1​	01/07/2019​		1​	04/07/2019​		1​	04/07/2019​
1​	04/07/2019​
1​	04/07/2019​

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:

Invoice Number	Date of Payment.1	Date of Payment.2
1​	01/07/2019​	04/07/2019​

[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]