Looking to find prices based on unique customer ID

[saltoftheearth]

Hello all!

I am working with some salesforce data and am looking to find specific information for customers that have made multiple purchases.

I have attached a sample worksheet that has random data but it should portray the issue I'm trying to solve! A few things to note:

• Each customer has customer ID (in theory one customer could have more than one, but for the most part it will just be one)
• One customer ID can have multiple product line IDs
• Each product line ID will have a sale price
• On the summary tab in the attached excel file, I am looking to reference the account IDs on the second tab and match up the sales price to the customer ID.
  • If one of the customer IDs has multiple product lines with the same product, the summary tab should pull the lowest of those values
• Ignore the formulas, I just generated random numbers

Please let me know what other information I can provide. Really do appreciate all the help you all provide!

Summary tab

Sample Sheet.xlsx
	A	B	C	D	E	F
1	Customer ID	Product 1	Product 2	Product 3	Product 4	Product 5
2	67
3	382
4	124
5	177
6	213
7	7
8	17
9	304
10	112
11	208
12	54
13	82
14	344
15	347
16	388
17	13
18	173
19	358
20	84
21	305
Summary

Cell Formulas
Range	Formula
A2	A2	=IFERROR(__xludf.DUMMYFUNCTION("unique(Sheet1!A2:A1000)"),67)
A3	A3	=IFERROR(__xludf.DUMMYFUNCTION("""COMPUTED_VALUE"""),382)
A4	A4	=IFERROR(__xludf.DUMMYFUNCTION("""COMPUTED_VALUE"""),124)
A5	A5	=IFERROR(__xludf.DUMMYFUNCTION("""COMPUTED_VALUE"""),177)
A6	A6	=IFERROR(__xludf.DUMMYFUNCTION("""COMPUTED_VALUE"""),213)
A7	A7	=IFERROR(__xludf.DUMMYFUNCTION("""COMPUTED_VALUE"""),7)
A8	A8	=IFERROR(__xludf.DUMMYFUNCTION("""COMPUTED_VALUE"""),17)
A9	A9	=IFERROR(__xludf.DUMMYFUNCTION("""COMPUTED_VALUE"""),304)
A10	A10	=IFERROR(__xludf.DUMMYFUNCTION("""COMPUTED_VALUE"""),112)
A11	A11	=IFERROR(__xludf.DUMMYFUNCTION("""COMPUTED_VALUE"""),208)
A12	A12	=IFERROR(__xludf.DUMMYFUNCTION("""COMPUTED_VALUE"""),54)
A13	A13	=IFERROR(__xludf.DUMMYFUNCTION("""COMPUTED_VALUE"""),82)
A14	A14	=IFERROR(__xludf.DUMMYFUNCTION("""COMPUTED_VALUE"""),344)
A15	A15	=IFERROR(__xludf.DUMMYFUNCTION("""COMPUTED_VALUE"""),347)
A16	A16	=IFERROR(__xludf.DUMMYFUNCTION("""COMPUTED_VALUE"""),388)
A17	A17	=IFERROR(__xludf.DUMMYFUNCTION("""COMPUTED_VALUE"""),13)
A18	A18	=IFERROR(__xludf.DUMMYFUNCTION("""COMPUTED_VALUE"""),173)
A19	A19	=IFERROR(__xludf.DUMMYFUNCTION("""COMPUTED_VALUE"""),358)
A20	A20	=IFERROR(__xludf.DUMMYFUNCTION("""COMPUTED_VALUE"""),84)
A21	A21	=IFERROR(__xludf.DUMMYFUNCTION("""COMPUTED_VALUE"""),305)

Raw Data

Sample Sheet.xlsx
	A	B	C	D
1	Customer ID	Product line ID	Product Name	Sale Price
2	309	1234	Product 1	$85.00
3	137	1234	Product 2	$96.00
4	368	1234	Product 3	$105.00
5	302	1234	Product 2	$85.00
6	29	123	Product 1	$69.00
7	200	123	Product 2	$96.00
8	257	123	Product 3	$73.00
9	60	123	Product 2	$77.00
10	389	123	Product 2	$72.00
11	16	456	Product 2	$108.00
12	90	456	Product 2	$63.00
13	142	456	Product 4	$101.00
14	177	456	Product 4	$78.00
15	247	456	Product 4	$63.00
16	225	789	Product 1	$79.00
17	118	789	Product 1	$61.00
18	153	789	Product 2	$66.00
19	363	789	Product 2	$68.00
20	309	102	Product 3	$50.00
21	295	102	Product 3	$82.00
Raw Data

Cell Formulas
Range	Formula
A2:A21	A2	=RANDBETWEEN(1,400)
D2:D21	D2	=RANDBETWEEN(50,110)

 

[mamady]

Hi Dear,

Please check the below:

Book1
	A	B	C	D	E	F
1	Customer ID	Product 1	Product 2	Product 3	Product 4	Product 5
2	398	59
3	316		75
4	67			65
5	16		102
6	352	89
7	291		107
8	3			70
9	293		64
10	127		93
11	14		109
12	333		70
13	292				76
14	75				92
15	15				60
16	173	103
17	319	97
18	56		102
19	148		86
20	220			65
21	73			69
Summary

Cell Formulas
Range	Formula
A2:A21	A2	=UNIQUE('Raw Data'!A2:A21)
B2:F21	B2	=IFERROR(MIN(FILTER('Raw Data'!$D$2:$D$21,(Summary!$A$2#='Raw Data'!$A2)*('Raw Data'!$C$2:$C$21=Summary!B$1))),"")
Dynamic array formulas.

Book1
	A	B	C	D
1	Customer ID	Product line ID	Product Name	Sale Price
2	398	1234	Product 1	59
3	316	1234	Product 2	75
4	67	1234	Product 3	65
5	16	1234	Product 2	102
6	352	123	Product 1	89
7	291	123	Product 2	107
8	3	123	Product 3	70
9	293	123	Product 2	64
10	127	123	Product 2	93
11	14	456	Product 2	109
12	333	456	Product 2	70
13	292	456	Product 4	76
14	75	456	Product 4	92
15	15	456	Product 4	60
16	173	789	Product 1	103
17	319	789	Product 1	97
18	56	789	Product 2	102
19	148	789	Product 2	86
20	220	102	Product 3	65
21	73	102	Product 3	69
Raw Data

Regards
M.Yusuf

 

[saltoftheearth]

Hello there!

Appreciate you following up on my question. I tried the formula you referenced, but it did not seem to work. Were you able to have the values pull over correctly when you ran it?

 

[mamady]

Hello there!

Appreciate you following up on my question. I tried the formula you referenced, but it did not seem to work. Were you able to have the values pull over correctly when you ran it?

Yes, indeed. The formulas work with Office 365, which I assume you have on your computer as indicated in your profile.

I have uploaded a file on my Google Drive, you can download it and check the workings and adjust accordingly.

Best Regards
M.Yusuf

 

[saltoftheearth]

Thank you! I am taking a look now

So in the second row on the summary tab, The customer ID shows 398 but the product 1 column is blank. If I look at the raw data tab, customer ID 398 has product 1 with a sale price of 59 but it is not showing in the summary sheet for some reason

 

[mamady]

If pasting the formula doesn't load the sale price of 59 (cell remains blank), open the formula edit bar and click enter. It should show the result and you can drag it to the rest of the cells.