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: 0

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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
232
Fluff
Fluff
A
  • Question
Combine text from different cells but apply different formatting
Replies
1
Views
980
attikuz
A
Z
  • Question
Count Distinct Values in Un-Filtered Table across Multiple Columns with 1 Criteria
Replies
0
Views
187
zero269
Z
B
  • Solved
Create distinct list with criteria
Replies
3
Views
132
Fluff
Fluff
P
  • Question
Dropdown list not working
Replies
0
Views
179
pyclen
P

Forum statistics

Threads
1,215,135
Messages
6,123,241
Members
449,093
Latest member
Vincent Khandagale

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