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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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

V
  • Solved
Show duplicates from across multiple ranges in one cell with a single nested formula?
Replies
9
Views
175
VikingLink
V
P
  • Question Question
Dropdown list not working
Replies
0
Views
299
pyclen
P
T
  • Question Question
SUMPRODUCT Over Multiple Columns
Replies
2
Views
269
thewiseguy
T
N
  • Question Question
Is there a tidier way to create this menu?
Replies
1
Views
403
sijpie
sijpie
M
  • Question Question
Running unique count of occurrence on table based on date
Replies
0
Views
351
Minx_Minxy
M

Forum statistics

Threads
1,223,911
Messages
6,175,323
Members
452,635
Latest member
laura12345

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