Return each unique value for a single criteria

[Emma Eve]

Hello.

I have a spreadsheet that contains multiple documents some of which are duplicates. The vast majority of the time the duplicates have different dollar amounts. However, after applying an INDEX/MATCH, the formula returns the fist dollar value it reads and applies that amount to all duplicates. Is there a way to return each unique dollar value for the duplicated document number? For example:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Doc Number[/TD]
[TD]Dollar Amounts[/TD]
[TD]Amount - My results after INDEX/MATCH[/TD]
[TD]Amount - Desired Results[/TD]
[/TR]
[TR]
[TD]DOCX111[/TD]
[TD]1000[/TD]
[TD]1000[/TD]
[TD]1000
[/TD]
[/TR]
[TR]
[TD]DOCX111[/TD]
[TD]2000[/TD]
[TD]1000[/TD]
[TD]2000[/TD]
[/TR]
[TR]
[TD]DOCX111[/TD]
[TD]3000[/TD]
[TD]1000[/TD]
[TD]3000[/TD]
[/TR]
[TR]
[TD]DOCX111[/TD]
[TD]4000[/TD]
[TD]1000[/TD]
[TD]4000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thank you!

 

[AhoyNC]

Maybe something like this array formula.

This formula must be enter with

Excel Workbook
	A	B
1	Number	Dollar Amounts
2	DOCX111	1000
3	DOCX111	2000
4	DOCX112	1200
5	DOCX111	3000
6	DOCX111	4000
7	DOCX112	1500
8
9
10
11
12	Number:	DOCX111
13	Amounts	1000
14		2000
15		3000
16		4000
17
CTRL-SHIFT-ENTER.

 

[sgroath]

I was just ready to reply in a (much) more clunky manner. Thanks - I love learning better approaches!

 

[Emma Eve]

Thank you, AhoyNC. Is it possible for the unique results to appear next to each identical doc. number as they appear in column A?

 

[AhoyNC]

Yes, You could do something like this (not sure what your actual layout looks like).
Formula needs to be entered with CTRL-SHIFT-ENTER.
Then drag down column as needed.

Excel Workbook
	A	B
1	Number	Dollar Amounts
2	DOCX111	1000
3	DOCX111	2000
4	DOCX112	1200
5	DOCX111	3000
6	DOCX111	4000
7	DOCX112	1500
8
9
10
11	Number	Dollar Amounts
12	DOCX111	1000
13	DOCX111	2000
14	DOCX111	3000
15	DOCX111	4000
16	DOCX112	1200
17	DOCX112	1500
Sheet