Forum Discussion
Listing and counting unique values that match criteria
I want to create a list off another list but i'm having some issues getting things the way I want.
The "All Task" is the list i'm pulling data from. I want to grab each unique Wave Number and then show each unique task along with type and an overall count per type.
You have a few options here. I'd go with GROUPBY because it will allow you to apply multiple functions to the data. You may have to change the arrangement, but it handles this task well.
=LET( row_field, Tasks[Wave '#], f, row_field = rpt_filter, values, HSTACK(Tasks[Task '#], Tasks[Task '#], Tasks[Task Status]), fn, HSTACK(COUNTA, ARRAYTOTEXT, ARRAYTOTEXT), agg, GROUPBY(row_field, values, fn, , 0, , f), DROP(agg, 1) )
16 Replies
SQL:
select * from AllTasks limit 5;
select f02 as 'Insert Wave Number Here
',group_concat(distinct(f04)),count(distinct(f04)),f05,f07 from AllTasks group by f02;
f01 f02 f03 f04 f05 f06 f07 f08 f09
070 20241004038 70 WMAL WALMART 1116144168 Replen CASE-ROP 40 00-Available 1 20241004 070 20241004039 70 WMAL WALMART 1116144178 Replen CASE-ROP 40 00-Available 1 20241004 070 20241004065 70 WMAL WALMART 1116144371 Case Pick 40 00-Available 1 20241004 070 20241004052 70 WMAL WALMART 1116144293 Replen CASE-ROP 40 00-Available 1 20241004 070 20241004052 70 WMAL WALMART 1116144294 Replen CASE-ROP 50 00-Available 1 20241004 Insert Wave Number Here group_concat(distinct(f04)) count(distinct(f04)) f05 f07
20241004032 1116144124 1 Replen CASE-ROP 15-Held 20241004033 1116144139 1 Case Pick 15-Held 20241004034 1116144143 1 Replen CASE-ROP 20-Being Pulled 20241004038 1116144168 1 Replen CASE-ROP 00-Available 20241004039 1116144178 1 Replen CASE-ROP 00-Available 20241004040 1116144183 1 Less Than Full Pallet Replen CASE - RRC 20-Being Pulled 20241004041 1116144199 1 Less Than Full Pallet Replen CASE - RRC 15-Held 20241004042 1116144208,1116144213 2 Replen CASE-ROP 20-Being Pulled 20241004043 1116144220 1 Replen CASE-ROP 15-Held 20241004044 1116144251,1116144248 2 Replen CASE-ROP 10-Prt/Assigned 20241004046 1116144263 1 Case Pick 10-Prt/Assigned 20241004047 1116144265 1 Less Than Full Pallet Replen CASE - RRC 15-Held 20241004048 1116144269 1 Replen CASE-ROP 20-Being Pulled 20241004052 1116144293,1116144294,1116144295,1116144289 4 Replen CASE-ROP 00-Available 20241004053 1116144301 1 Case Pick 20-Being Pulled 20241004056 1116144310,1116144315 2 Less Than Full Pallet Replen CASE - RRC 10-Prt/Assigned 20241004058 1116144325 1 Replen CASE-ROP 15-Held 20241004059 1116144335,1116144336 2 Replen CASE-ROP 20-Being Pulled 20241004060 1116144339 1 Case Pick 15-Held 20241004061 1116144341 1 Less Than Full Pallet Replen CASE - RRC 00-Available 20241004062 1116144350,1116144349 2 Replen CASE-ROP 10-Prt/Assigned 20241004064 1116144363 1 Case Pick 15-Held 20241004065 1116144371 1 Case Pick 00-Available 20241004066 1116144396,1116144395,1116144398 3 Replen CASE-ROP 00-Available 20241004067 1116144406 1 Case Pick 15-Held 20241004070 1116144421 1 Less Than Full Pallet Replen CASE - RRC 20-Being Pulled 20241004072 1116144438,1116144437 2 Case Pick 10-Prt/Assigned - Can't run SQL
- Why not.
You have a few options here. I'd go with GROUPBY because it will allow you to apply multiple functions to the data. You may have to change the arrangement, but it handles this task well.
=LET( row_field, Tasks[Wave '#], f, row_field = rpt_filter, values, HSTACK(Tasks[Task '#], Tasks[Task '#], Tasks[Task Status]), fn, HSTACK(COUNTA, ARRAYTOTEXT, ARRAYTOTEXT), agg, GROUPBY(row_field, values, fn, , 0, , f), DROP(agg, 1) )- When I click into the formula it stops working, and honestly my excel knowledge is too limited to figure out how to duplicate this.
#1 When I click into the formula it stops working
What does this actually mean/What happens?
#2 and honestly my excel knowledge is too limited to figure out how to duplicate this
- Fair enough. Looking at your previous posts you seem to run Excel 365. Please confirm or tell us which version you run + Windows or Mac?
- Assuming you run 365, if in a blank sheet you start typing: =gr do you see?:
the problem might be that you don't have the GROUPBY function (365 only and not Generally Available yet)
