how to assemble product list with multiple units of measure

M

mediumrare

New Member
Joined
Apr 7, 2021
Messages
31
Office Version
  1. 365
Platform
  1. Windows
Hi there.

I am looking to work with product data, focusing and analysing it to show the unique products. The UNIQUE function works well (many thanks to @Fluff !) for products that do not have more than one unit of measurement (e.g., each, case). Is there a modifier or similar approach to return unique data but also let it display each UOM as well? I am, understandably, getting #SPILL! errors for these products, but do not know my way around this impasse.

dbg test.xls
ABCDEFG
1testprodtestqtytestuom
29602424EA9602460EA
3960241CS9602411CS
4960241CS11054219EA
59602436EA15032517EA
6960241CS1720101EA
7960242CS1721411EA
8960246CS22020023CS
911054215EA
101105424EA
111503256EA
121503255EA
131503256EA
141720101EA
151721411EA
162202008CS
1722020015CS
Sheet1
Cell Formulas
RangeFormula
F4:F8F4=SUM(FILTER(testqty,testprod=E4))
G4:G8G4=UNIQUE(FILTER(testuom,testprod=E4))
Named Ranges
NameRefers ToCells
testprod=Sheet1!$A$2:$A$17F4:G8
testqty=Sheet1!$B$2:$B$17F4:F8
testuom=Sheet1!$C$2:$C$17G4:G8


The goal here being to apply these results, per customer, to help give them a unique, updated price list. It will take their previous margin and apply it to the new cost to give them their new price.

dbg test.xls
ABCDEFGHIJKLMN
1testprodtestqtytestuomtestdatetestpricetestgpmtestoldcostproductqtyuomtestpricetestnewcosttestnewprice
29602424EA04/28/21$ 7.8426 %$ 3.719602471#SPILL!#SPILL!$ 4.04$ 5.46
3960241CS04/29/21$ 94.0826 %$ 3.719602471#SPILL!#SPILL!$ 4.04$ 5.46
4960241CS05/06/21$ 94.0826 %$ 3.7111054219EA$ 1.26$ 0.85$ 1.25
59602436EA05/14/21$ 7.8426 %$ 3.7115032517EA$ 5.50$ 4.23$ 5.79
6960241CS05/18/21$ 94.0826 %$ 3.711720101EA$ 30.79$ 24.86$ 33.60
7960242CS05/20/21$ 94.0826 %$ 3.711721411EA$ 37.42$ 30.21$ 40.83
8960246CS05/21/21$ 94.0826 %$ 3.7122020023CS$ 28.80$ 4.50$ 5.23
911054215EA05/10/21$ 1.2632 %$ 0.78
101105424EA05/12/21$ 1.2632 %$ 0.78
111503256EA02/10/21$ 5.5027 %$ 3.88
121503255EA03/24/21$ 5.5027 %$ 3.88
131503256EA04/09/21$ 5.5027 %$ 3.88
141720101EA05/13/21$ 30.7926 %$ 22.81
151721411EA01/21/21$ 37.4226 %$ 27.72
162202008CS06/17/20$ 28.8014 %$ 4.13
1722020015CS06/30/20$ 28.8014 %$ 4.13
Sheet1
Cell Formulas
RangeFormula
J2:J8J2=SUM(FILTER(testqty,testprod=I2))
K2:K8K2=UNIQUE(FILTER(testuom,testprod=I2))
L2:L8L2=UNIQUE(FILTER(testprice,testprod=I2))
N2:N8N2=M2/(1-(UNIQUE(FILTER(testgpm,testprod=I2))))
Named Ranges
NameRefers ToCells
testgpm=Sheet1!$F$2:$F$17N2:N8
testprice=Sheet1!$E$2:$E$17L2:L8
testprod=Sheet1!$A$2:$A$17N2:N8, J2:L8
testqty=Sheet1!$B$2:$B$17J2:J8
testuom=Sheet1!$C$2:$C$17K2:K8
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Sort by date Sort by votes
If you happy to switch your output slightly.
+Fluff 1.xlsm
ABCDEFGHIJKLMN
1testprodtestqtytestuomtestdatetestpricetestgpmtestoldcostproductuomQtytestpricetestnewcosttestnewprice
29602424EA443147.8426%3.7196024EA607.844.045.46
3960241CS4431594.0826%3.7196024CS1194.084.045.46
4960241CS4432294.0826%3.71110542EA191.260.851.25
59602436EA443307.8426%3.71150325EA175.504.235.79
6960241CS4433494.0826%3.71172010EA130.7924.8633.59
7960242CS4433694.0826%3.71172141EA137.4230.2140.82
8960246CS4433794.0826%3.71220200CS2328.804.505.23
911054215EA443261.2632%0.78
101105424EA443281.2632%0.78
111503256EA442375.527%3.88
121503255EA442795.527%3.88
131503256EA442955.527%3.88
141720101EA4432930.7926%22.81
151721411EA4421737.4226%27.72
162202008CS4399928.814%4.13
1722020015CS4401228.814%4.13
18
Lists
Cell Formulas
RangeFormula
I2:J8I2=UNIQUE(FILTER(A2:C17,COLUMN(A1:C1)<>2))
K2:K8K2=SUMIFS(B:B,A:A,INDEX(I2#,,1),C:C,INDEX(I2#,,2))
L2:L8L2=UNIQUE(FILTER($E$2:$E$17,($A$2:$A$17=I2)*($C$2:$C$17=J2)))
N2:N8N2=M2/(1-(UNIQUE(FILTER($F$2:$F$17,$A$2:$A$17=I2))))
Dynamic array formulas.
 
Upvote 0
Solution
Fluff said:
If you happy to switch your output slightly.
Click to expand...

To confirm that I understand the way you've done this(which I'm trying to wrap my head round):

I2 is returning the unique, filtered result of the array (A2:C17)? Or I2:J8? You've filtered the result from array/range A2:C17 to reference columns A-C. And what of the "<>2"?
 
Upvote 0
It filters A2:C17 to get rid of col B & then gets the unique values from A & C combined.
 
Upvote 0
Glad to help & thanks for the feedback.
 
Upvote 0
How about
+Fluff 1.xlsm
ABCDEFGHIJKLMN
1testprodtestuomtestqtytestdatetestpricetestgpmtestoldcostproductuomQtytestpricetestnewcosttestnewprice
296024EA24443147.840.263.7196024EA607.844.045.46
396024CS14431594.080.263.7196024CS1194.084.045.46
496024CS14432294.080.263.71110542EA191.260.851.25
596024EA36443307.840.263.71150325EA175.54.235.79
696024CS14433494.080.263.71172010EA130.7924.8633.59
796024CS24433694.080.263.71172141EA137.4230.2140.82
896024CS64433794.080.263.71220200CS2328.84.55.23
9110542EA15443261.260.320.78
10110542EA4443281.260.320.78
11150325EA6442375.50.273.88
12150325EA5442795.50.273.88
13150325EA6442955.50.273.88
14172010EA14432930.790.2622.81
15172141EA14421737.420.2627.72
16220200CS84399928.80.144.13
17220200CS154401228.80.144.13
18
19
Master
Cell Formulas
RangeFormula
I2:J8I2=UNIQUE(A2:B17)
K2:K8K2=SUMIFS(C:C,A:A,INDEX(I2#,,1),B:B,INDEX(I2#,,2))
L2:L8L2=UNIQUE(FILTER($E$2:$E$17,($A$2:$A$17=I2)*($B$2:$B$17=J2)))
N2:N8N2=M2/(1-(UNIQUE(FILTER($F$2:$F$17,$A$2:$A$17=I2))))
Dynamic array formulas.
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question
Dropdown list not working
Replies
0
Views
186
pyclen
P
N
  • Question
Is there a tidier way to create this menu?
Replies
1
Views
316
sijpie
sijpie
M
  • Question
Running unique count of occurrence on table based on date
Replies
0
Views
188
Minx_Minxy
M
1
  • Question
Sum total filtered data only, with multiple criteria
Replies
1
Views
323
1988craig
1
L
  • Question
How to filter function and SUM based on Partial Text Match (SUMIFS with wildcards)
2
Replies
17
Views
769
Lacan
L

Forum statistics

Threads
1,215,759
Messages
6,126,728
Members
449,332
Latest member
nokoloina

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top