Retrieving unique records from a list

[bearcub]

I have a table that has all the details for a commissions paid to our sales teams (~ 14 Columns).

I would like to create a unique dynamic list on another sheet that is based on the this source but I only want to extract 3 columns of data (Customer Name, Earning Group and USD amount) from the source data.

How would I create a unique list by using the Unique array function?

 

[Fluff]

Can you post some sample data, including expected result.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[bearcub]

Sure, Here is a snippet of the table. I would like to create a formula that will create a smaller table from the Credit Type, Customer and Earnings group fields. I normally take this table and compare it against a sales report.

We have 5 or 6 different earnings groups (new business, renewal, multi year renewals. Professional Services, etc). A customer can have an order that will include new business, professional services, renewal, etc. This is why I have to use these 3 columns to create a new unique list.

The credit amount field is in column F in the original table table

8. Septemberr 2023.xlsx
	F	G	H	I	J	K	L	M
1
2	Credit Amount	Customer Name	Order Item Code	Order Code	Incentive Date	Earning Group	Geography Name	Period
4	74,715.14	ADP, Inc.	NARR_2022-09-13_From_Connect	0063t000011dtQ6AAI	9/13/22	NARR	East	September
9	18,400.39	Factory Mutual Insurance Company	NARR_2022-09-20_From_Connect	0063t000011dfxSAAQ	9/20/22	NARR	East	September
11	191,452.80	VeriSign, Inc.	NARR_2022-09-26_From_Connect	0063t000011am5FAAQ	9/26/22	NARR	East	September
14	161,114.41	National Grid USA Service Company Inc,	NARR_2022-09-29_From_Connect	0063t000011cJy1AAE	9/29/22	NARR	East	September
15	8,576.48	Neuberger Berman Group LLC	NARR_2022-09-29_From_Connect	0063t0000127SffAAE	9/29/22	NARR	East	September
23	350,000.00	Blackstone Administrative Services Partnership L.P.	NARR_2022-09-30_From_Connect	0063t000011duipAAA	9/30/22	NARR	East	September
24	2,080.80	ChenMed, LLC	NARR_2022-09-30_From_Connect	0063t0000127SJUAA2	9/30/22	NARR	East	September
25	7,427.30	Genworth North America Corporation	NARR_2022-09-30_From_Connect	0063t000011dv2KAAQ	9/30/22	NARR	East	September
26	9,878.28	Liberty Mutual Insurance Company	NARR_2022-09-30_From_Connect	0063t0000127a7cAAA	9/30/22	NARR	East	September
27	10,101.25	Rite Aid Hdqtrs. Corp	NARR_2022-09-30_From_Connect	0063t000011duf1AAA	9/30/22	NARR	East	September
28	143,995.40	Truist Bank	NARR_2022-09-30_From_Connect	0063t0000101BgwAAE	9/30/22	NARR	East	September
29	303,750.00	Truist Bank	NARR_2022-09-30_From_Connect	0064X00002Bs6nvQAB	9/30/22	NARR	East	September
38	524,465.00	The Public Joint-Stock Company “State Savings Bank	NARR_2022-09-30_From_Connect	0063t0000117vwbAAA	9/30/22	NARR	Eastern Europe	September
47	16,821.00	CareSource Management Group Company	NARR_2022-09-02_From_Connect	0063t000011dueIAAQ	9/2/22	NARR	Central	September
51	130,703.46	ANZ Bank New Zealand Limited	NARR_2022-09-06_From_Connect	0064X00002AUI6hQAH	9/6/22	NARR	ANZ	September
53	495,000.00	Zions Bancorporation, N.A.	NARR_2022-09-07_From_Connect	0063t0000119BIwAAM	9/7/22	NARR	West	September
64	74,715.14	ADP, Inc.	NARR_2022-09-13_From_Connect	0063t000011dtQ6AAI	9/13/22	NARR	East	September
65	1,200.00	RedShelf, Inc	NARR_2022-09-13_From_Connect	0063t0000127VTdAAM	9/13/22	NARR	Central	September
72	20,000.00	Grupo Financiero Afirme	NARR_2022-09-15_From_Connect	0063t000012ucCBAAY	9/15/22	NARR	LATAM	September
79	24,000.00	Amerisure Insurance	NARR_2022-09-20_From_Connect	0063t000012wFKNAA2	9/20/22	NARR	Central	September
80	18,400.39	Factory Mutual Insurance Company	NARR_2022-09-20_From_Connect	0063t000011dfxSAAQ	9/20/22	NARR	East	September
85	28,000.00	Guidewire Software, Inc.	NARR_2022-09-21_From_Connect	0063t000012APIqAAO	9/21/22	NARR	West	September
94	150,000.00	California State University	NARR_2022-09-26_From_Connect	0064X00002A97SMQAZ	9/26/22	NARR	West	September
95	40,320.00	Classy	NARR_2022-09-26_From_Connect	0064X000029mrdmQAA	9/26/22	NARR	West	September
96	22,000.00	Raia Drogasil S/A	NARR_2022-09-26_From_Connect	0063t00000ymj3qAAA	9/26/22	NARR	LATAM	September
97	191,452.80	VeriSign, Inc.	NARR_2022-09-26_From_Connect	0063t000011am5FAAQ	9/26/22	NARR	East	September
Table

 

[Fluff]

Thanks for that, how about

=UNIQUE(CHOOSECOLS(F3:M28,1,2,6))

 

[bearcub]

Yes, that works, thank you! Then, if I wanted to do a custom sort I would have to define the sort a "custom sort"?

 

[Fluff]

How would you want to sort it?

 

[bearcub]

If I didn't want an alphabetical sort. Sort by earnings group - NARR, MultiYear, Professional Services, Renewal, MultiYear Renewal, for example. I would be sorting by New Business first then by Renewal

 

[Fluff]

That's not very clear, but maybe

=LET(u,UNIQUE(CHOOSECOLS(F3:M28,1,2,6)),SORTBY(u,MATCH(INDEX(u,,3),{"NARR","MultiYear","Professional Services","Renewal","MultiYear Renewal"},0)))

 

[bearcub]

Wow, thank you that is quite a formula.

 

[Fluff]

You're welcome & thanks for the feedback.