Creating Unique list from Customer Order Amount, Customer Name and Order Identifier using the Unique function

[bearcub]

Is it possible to use to create a list of values based upon three different columns? I create a unique list of customer orders so I aggregate commissions paid against a customer order.

I calculated rep commissions and I get a data dump from our commission system.

Each commission paid llsts the customer name, the order amount , commissions paid etc for each order. If 50 people receiving payment on the same order then the customer order amount will appear 50 times in the list. The way the sheet is presently set up it is difficult to get a unique customer list.

Customers can have multiple orders during the year. We identify each order by "order Code".

I can create a unique list from the order using the Order Code Feld. But the order code is in the 4th column in the array.

If I include multiple items to create the list, how would I specify the column order? Or is this possible?

This is the data that I am trying to create a unique list from (the unique identified for the order is the Order Code field which I have highlighted in yellow)

The lines shown above are for 1 person's commission. Every person that is paid commissions will have the same structure but different payment orders. I would like to create a unique list from the above table for every month

 

[etaf]

thats already unique order list - so not sure what you want to see from that list
you haver a lot of excel verions in profile
which must it work with ??? version 365 i guess as you mention the unique function

 

[bearcub]

The list I am showing is unique to 1 rep but the file I have contains commissions for all the reps. Others will be paid on the same order so the data I am showing might be repeated 50 times in the file. This makes it difficult to determine what the original order amount was because adding up all the credits would be 50X larger than the original value.

 

[etaf]

ok, so how does excel know which set of orders belongs to which rep ?

 

[bearcub]

Each line has their name on each row and the % of the deal they are paid. The fact that I have a separate line item with a different rep name is why the order amounts are duplicated.

 

[etaf]

whats a deal consist of ?
can you use xl2bb to show the sample , i'm not following - sorry

you should be able to produce a unique list of persons name and then sum() using filter() by that name and so get a total value for each person
but not sure how the % works , as cannot see an example

Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet. Plus we cannot see any of the formulas used.

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

 

[bearcub]

Sorry, that program doesn't work on my computer I think because the company is blocking it on my computer. the mini range section is grayed out on my computer.

I guess the question I am is asking is can the unique function create a unique list based on different fields.

For example, if I were creating a unique list from 1 fieldI would use the Order code (the unique identifier for the order.

One order for the same customer might contain duplicates for the same order code (the snapshot below is for an order for Ingram. We are paying on new business plus a bonus for the same dollar amount):

Credit Amount	Customer Name	Order Item Code	Order Code	Incentive Date	Earning Group
224,371.00​	Ingram Micro Inc.	NARR_2024-02-29_From_Connect	0064X00002M7t6pQAB	2/29/24​	NARR
1.00​	Ingram Micro Inc.	NARR_2024-02-29_From_Connect	0064X00002M7t6pQAB	2/29/24​	New Logo
224,371.00​	Ingram Micro Inc.	NARR_2024-02-29_From_Connect	0064X00002M7t6pQAB	2/29/24​	New Logo NARR
105,550.00​	Ingram Micro Inc.	NARR_2024-02-29_From_Connect	0064X00002M7t6pQAB	2/29/24​	Professional Services

This is for one person but several others would have a separate row of data with the same information

I am trying to create a lists for customer name and order code from the snapshot above.

 

[bearcub]

Sorry, that program doesn't work on my computer I think because the company is blocking it on my computer. the mini range section is grayed out on my computer.

I guess the question I am is asking is can the unique function create a unique list based on different fields.

For example, if I were creating a unique list from 1 fieldI would use the Order code (the unique identifier for the order.

One order for the same customer might contain duplicates for the same order code (the snapshot below is for an order for Ingram. We are paying on new business plus a bonus for the same dollar amount):

Credit Amount	Customer Name	Order Item Code	Order Code	Incentive Date	Earning Group
224,371.00​	Ingram Micro Inc.	NARR_2024-02-29_From_Connect	0064X00002M7t6pQAB	2/29/24​	NARR
1.00​	Ingram Micro Inc.	NARR_2024-02-29_From_Connect	0064X00002M7t6pQAB	2/29/24​	New Logo
224,371.00​	Ingram Micro Inc.	NARR_2024-02-29_From_Connect	0064X00002M7t6pQAB	2/29/24​	New Logo NARR
105,550.00​	Ingram Micro Inc.	NARR_2024-02-29_From_Connect	0064X00002M7t6pQAB	2/29/24​	Professional Services

This is for one person but several others would have a separate row of data with the same information

I am trying to create a lists for customer name and order code from the snapshot above.

Sorry, the above got sent before I was finished with my explanation.

I have 365 and have access to the unique array function. I include Credit amount, Customer Name, Order Item Code & Order code (the 1st 4 fields above) would I get a result for 224,371, 1 & 105,550?

IfI the unique does that then I think I have solved my problem.

 

[etaf]

what result do you expct to see from that snapshot

 

[etaf]

unique will do that
if you ignor the earning group , as they are all unique

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Credit Amount	Customer Name	Order Item Code	Order Code	Incentive Date	Earning Group				Credit Amount	Customer Name	Order Item Code	Order Code	Incentive Date
2	224,371.00	Ingram Micro Inc.	NARR_2024-02-29_From_Connect	0064X00002M7t6pQAB	2/29/24	NARR				224,371.00	Ingram Micro Inc.	NARR_2024-02-29_From_Connect	0064X00002M7t6pQAB	2/29/24
3	1.00	Ingram Micro Inc.	NARR_2024-02-29_From_Connect	0064X00002M7t6pQAB	2/29/24	New Logo				1.00	Ingram Micro Inc.	NARR_2024-02-29_From_Connect	0064X00002M7t6pQAB	2/29/24
4	224,371.00	Ingram Micro Inc.	NARR_2024-02-29_From_Connect	0064X00002M7t6pQAB	2/29/24	New Logo NARR				105,550.00	Ingram Micro Inc.	NARR_2024-02-29_From_Connect	0064X00002M7t6pQAB	2/29/24
5	105,550.00	Ingram Micro Inc.	NARR_2024-02-29_From_Connect	0064X00002M7t6pQAB	2/29/24	Professional Services
Sheet1

Cell Formulas
Range	Formula
J1:N4	J1	=UNIQUE(A1:E5)
Dynamic array formulas.