Create Unique List from array with criteria

B

brentbush

New Member
Joined
Mar 10, 2024
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Hello,

I have 2 different questions I need some help on and from my searching it is getting to complex for me.
1. Create a unique list from 2 different columns (successfully done using Unique & VStack) but limit the list to only items that have quantity >0.

2. Create a count for each of the unique items listed.

Thank you very much for your help.

Brent

Unique Items in list with criteria.xlsx
BCDEFGH
2G4=UNIQUE(VSTACK(B4:B29,D4:D29))
3Primary#PSecondary#SUnique ListTotal Count Ea
43" Sch 40 Carbon BW10.002" Sch 40 Carbon BW5.003" Sch 40 Carbon BW
52" Sch 40 Carbon BW1.001" Sch 40 Carbon BW0.502" Sch 40 Carbon BW
64" Sch 40 Carbon Soc1.50" Sch 40 Carbon Soc0.004" Sch 40 Carbon Soc
72" Sch 40 Carbon BW1.50" Sch 40 Carbon BW0.00Size" Sch Carbon 0
8Size" Sch Carbon 00.00" Sch Carbon 00.001" Sch 40 Carbon BW
9Size" Sch Carbon 00.00" Sch Carbon 00.00" Sch 40 Carbon Soc
10Size" Sch Carbon 00.00" Sch Carbon 00.00" Sch 40 Carbon BW
11Size" Sch Carbon 00.00" Sch Carbon 00.00" Sch Carbon 0
12Size" Sch Carbon 00.00" Sch Carbon 00.00
13Size" Sch Carbon 00.00" Sch Carbon 00.00
14Size" Sch Carbon 00.00" Sch Carbon 00.00
15Size" Sch Carbon 00.00" Sch Carbon 00.00
16Size" Sch Carbon 00.00" Sch Carbon 00.00
17Size" Sch Carbon 00.00" Sch Carbon 00.00
18Size" Sch Carbon 00.00" Sch Carbon 00.00
19Size" Sch Carbon 00.00" Sch Carbon 00.00
20Size" Sch Carbon 00.00" Sch Carbon 00.00
21Size" Sch Carbon 00.00" Sch Carbon 00.00
22Size" Sch Carbon 00.00" Sch Carbon 00.00
23Size" Sch Carbon 00.00" Sch Carbon 00.00
24Size" Sch Carbon 00.00" Sch Carbon 00.00
25Size" Sch Carbon 00.00" Sch Carbon 00.00
26Size" Sch Carbon 00.00" Sch Carbon 00.00
27Size" Sch Carbon 00.00" Sch Carbon 00.00
28Size" Sch Carbon 00.00" Sch Carbon 00.00
29Size" Sch Carbon 00.00" Sch Carbon 00.00
Sheet1
Cell Formulas
RangeFormula
G4:G11G4=UNIQUE(VSTACK(B4:B29,D4:D29))
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E4:E29Expression=ISBLANK(E4)textNO
 

Attachments

  • Unique list with criteria.JPG
    Unique list with criteria.JPG
    132.7 KB · Views: 16

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Sort by date Sort by votes
Clarification: For count I want to sum all qty for each unique item.
Ignore conditional formatting, was unaware that was there.

Thanks for your help. Brent
 
Upvote 0
How do you have UNIQUE and VSTACK when your version of Excel (according to your profile) is XL2016?
Assuming you actually have xl365 (you should change your profile so we do not have to guess), put this formula in cell H4...
Excel Formula: 
=MAP(G4#,LAMBDA(x,COUNTIF(B4:B29,x)))
 
Upvote 0
As Rick said, assuming you do have 365, here's another option:
EDITED
Book1
BCDEFGHI
2
3Primary#PSecondary#SUnique ListTotal Count EaOr?
43" Sch 40 Carbon BW102" Sch 40 Carbon BW53" Sch 40 Carbon BW41
52" Sch 40 Carbon BW11" Sch 40 Carbon BW0.52" Sch 40 Carbon BW2
64" Sch 40 Carbon Soc1.5" Sch 40 Carbon Soc04" Sch 40 Carbon Soc1
72" Sch 40 Carbon BW1.5" Sch 40 Carbon BW0Size" Sch Carbon 022
8Size" Sch Carbon 00" Sch Carbon 00 
9Size" Sch Carbon 00" Sch Carbon 00 
10Size" Sch Carbon 00" Sch Carbon 00 
11Size" Sch Carbon 00" Sch Carbon 00 
12Size" Sch Carbon 00" Sch Carbon 00 
13Size" Sch Carbon 00" Sch Carbon 00 
14Size" Sch Carbon 00" Sch Carbon 00 
15Size" Sch Carbon 00" Sch Carbon 00 
16Size" Sch Carbon 00" Sch Carbon 00 
17Size" Sch Carbon 00" Sch Carbon 00 
18Size" Sch Carbon 00" Sch Carbon 00 
19Size" Sch Carbon 00" Sch Carbon 00 
20Size" Sch Carbon 00" Sch Carbon 00 
21Size" Sch Carbon 00" Sch Carbon 00 
22Size" Sch Carbon 00" Sch Carbon 00 
23Size" Sch Carbon 00" Sch Carbon 00 
24Size" Sch Carbon 00" Sch Carbon 00 
25Size" Sch Carbon 00" Sch Carbon 00 
26Size" Sch Carbon 00" Sch Carbon 00 
27Size" Sch Carbon 00" Sch Carbon 00 
28Size" Sch Carbon 00" Sch Carbon 00 
29Size" Sch Carbon 00" Sch Carbon 00 
Sheet1
Cell Formulas
RangeFormula
G4:G7G4=UNIQUE(FILTER(B4:B29,B4:B29>0,""))
H4H4=SUM(COUNTIFS(B4:B29,UNIQUE(B4:B29),C4:C29,">0"))
I4:I29I4=IF(G4<>"",COUNTIF(B4:B29,G4),"")
Dynamic array formulas.
 
Upvote 0
Hello Rick, well I got tired of not having some of the new formulas and had IT upgrade me today. I should have the latest and greatest through Office 365 instead of the old perpetual license. Haven’t updated my profile yet but will certainly do that.

Thank you for your help, much appreciated!

Regards,

Brent
 
Upvote 0
Another option that gets the distinct values & counts from both columns.
Fluff.xlsm
ABCDEFGH
1
2
3Primary#PSecondary#SUnique ListTotal Count Ea
43" Sch 40 Carbon BW102" Sch 40 Carbon BW53" Sch 40 Carbon BW10
52" Sch 40 Carbon BW11" Sch 40 Carbon BW0.52" Sch 40 Carbon BW7.5
64" Sch 40 Carbon Soc1.5" Sch 40 Carbon Soc01" Sch 40 Carbon BW0.5
72" Sch 40 Carbon BW1.5" Sch 40 Carbon BW04" Sch 40 Carbon Soc1.5
8Size" Sch Carbon 00" Sch Carbon 00
9Size" Sch Carbon 00" Sch Carbon 00
10Size" Sch Carbon 00" Sch Carbon 00
11Size" Sch Carbon 00" Sch Carbon 00
12Size" Sch Carbon 00" Sch Carbon 00
13Size" Sch Carbon 00" Sch Carbon 00
14Size" Sch Carbon 00" Sch Carbon 00
15Size" Sch Carbon 00" Sch Carbon 00
16Size" Sch Carbon 00" Sch Carbon 00
17Size" Sch Carbon 00" Sch Carbon 00
18Size" Sch Carbon 00" Sch Carbon 00
19Size" Sch Carbon 00" Sch Carbon 00
20Size" Sch Carbon 00" Sch Carbon 00
21Size" Sch Carbon 00" Sch Carbon 00
22Size" Sch Carbon 00" Sch Carbon 00
23Size" Sch Carbon 00" Sch Carbon 00
24Size" Sch Carbon 00" Sch Carbon 00
25Size" Sch Carbon 00" Sch Carbon 00
26Size" Sch Carbon 00" Sch Carbon 00
27Size" Sch Carbon 00" Sch Carbon 00
28Size" Sch Carbon 00" Sch Carbon 00
29Size" Sch Carbon 00" Sch Carbon 00
Sheet5
Cell Formulas
RangeFormula
G4:H7G4=LET(d,WRAPROWS(TOCOL(B4:E100,1),2),u,UNIQUE(FILTER(INDEX(d,,1),INDEX(d,,2)>0)),HSTACK(u,MAP(u,LAMBDA(m,SUM(FILTER(INDEX(d,,2),INDEX(d,,1)=m))))))
Dynamic array formulas.
 
Upvote 0
Solution
Thank you Fluff that does do exactly what is needed. Appreciate the help.
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

JoshLyman
  • Solved
COUNT specific text within cells split by delimiter
Replies
3
Views
425
Fluff
Fluff
S
  • Solved
Few problems with dynamic arrays
2
Replies
15
Views
296
djclements
djclements
S
  • Question Question
Creating a random sequence of items from a weighted list with no duplicates
Replies
6
Views
238
shg
shg
B
  • Solved
Creating Unique list from Customer Order Amount, Customer Name and Order Identifier using the Unique function
2
Replies
11
Views
341
etaf
etaf
X
  • Question Question
How To Create Multiple Sheets(With Criteria) From One Sheet?
Replies
1
Views
316
Kerryx
K

Forum statistics

Threads
1,223,981
Messages
6,175,773
Members
452,668
Latest member
mrider123

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