Filter Various Columns & Have Them Stacked

[srh5428]

Hello All,

This is my first post as I dive deeper into Excel and can not figure out how to get my data displayed correctly. I am hoping someone will be able to help me. I am using Version 2401 in MS 365.

Here is a picture of my Table, which is called WR_Detail

As you can see from this screenshot, the same WR # can have multiple different CU Category Codes. I am trying to find all the WR #s with the "RECLOSER" CU Category Code. This is simple enough using =FILTER(WR_Detail[WR '#], WR_Detail[CU Category Code]="RECLOSER"). However, I want to also return the other CU Category Codes for only those WR #'s that have a CU Category that contains "RECLOSER".

So my end goal would be a list of WR #s that contain all the parts and pieces, and also have a "RECLOSER" CU Category Code. So from this screenshot, my desired result would look like this:

Thank you for your help

 

[Fluff]

Can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[srh5428]

Hello All,

This is my first post as I dive deeper into Excel and can not figure out how to get my data displayed correctly. I am hoping someone will be able to help me. I am using Version 2401 in MS 365.

Here is a picture of my Table, which is called WR_Detail

View attachment 110097

As you can see from this screenshot, the same WR # can have multiple different CU Category Codes. I am trying to find all the WR #s with the "RECLOSER" CU Category Code. This is simple enough using =FILTER(WR_Detail[WR '#], WR_Detail[CU Category Code]="RECLOSER"). However, I want to also return the other CU Category Codes for only those WR #'s that have a CU Category that contains "RECLOSER".

So my end goal would be a list of WR #s that contain all the parts and pieces, and also have a "RECLOSER" CU Category Code. So from this screenshot, my desired result would look like this:

View attachment 110098

Thank you for your help

Designer Work Requests.xlsx
	A	B	C	D	E	F	G	H	I	J	K
32315	Columbus,Anthony J	62850323	1	1	P	4-122372	POLE & EQ HW-OH	POLE AND EQUIPMENT HARDWARE - OVERHEAD	DS-130300	EA	BRACKET ARRESTER OR CUTOUT- F/ CROSSARM MOUNTING
32316	Columbus,Anthony J	62850323	1	1	P	4-122372	CUTOUTS & FUSES	CUTOUTS AND FUSES	DS-320510-R	EA	CUTOUT-OPEN 15KV W/ 100A FUSEHOLDER - POLYMER
32317	Columbus,Anthony J	62850323	1	1	P	4-122372	CUTOUTS & FUSES	CUTOUTS AND FUSES	DS-320510-R	EA	CUTOUT-OPEN 15KV W/ 100A FUSEHOLDER - POLYMER
32318	Columbus,Anthony J	62850323	1	1	P	4-122372	CUTOUTS & FUSES	CUTOUTS AND FUSES	DS-320510-R	EA	CUTOUT-OPEN 15KV W/ 100A FUSEHOLDER - POLYMER
32319	Columbus,Anthony J	62850323	1	1	P	4-122372	CUTOUTS & FUSES	CUTOUTS AND FUSES	DS-320510-R	EA	CUTOUT-OPEN 15KV W/ 100A FUSEHOLDER - POLYMER
32320	Columbus,Anthony J	62850323	1	1	P	4-122372	CUTOUTS & FUSES	CUTOUTS AND FUSES	DS-320510-R	EA	CUTOUT-OPEN 15KV W/ 100A FUSEHOLDER - POLYMER
32321	Columbus,Anthony J	62850323	1	1	P	4-122372	CUTOUTS & FUSES	CUTOUTS AND FUSES	DS-325650	EA	FUSE-LINK TYPE T 80A
32322	Columbus,Anthony J	62850323	1	1	P	4-122372	RECLOSER	RECLOSERS	DS-347150	EA	RECLOSER 15 KV 6300 AMP TRIPSAVER (S&C) 200A CONTINUOUS CURRENT
32378	Columbus,Anthony J	62848530	1	1	P	4-129172	POLE & EQ HW-OH	POLE AND EQUIPMENT HARDWARE - OVERHEAD	DS-130300	EA	BRACKET ARRESTER OR CUTOUT- F/ CROSSARM MOUNTING
32379	Columbus,Anthony J	62848530	1	1	P	4-129172	CUTOUTS & FUSES	CUTOUTS AND FUSES	DS-320510-R	EA	CUTOUT-OPEN 15KV W/ 100A FUSEHOLDER - POLYMER
32380	Columbus,Anthony J	62848530	1	1	P	4-129172	CUTOUTS & FUSES	CUTOUTS AND FUSES	DS-320510-R	EA	CUTOUT-OPEN 15KV W/ 100A FUSEHOLDER - POLYMER
32381	Columbus,Anthony J	62848530	1	1	P	4-129172	CUTOUTS & FUSES	CUTOUTS AND FUSES	DS-320510-R	EA	CUTOUT-OPEN 15KV W/ 100A FUSEHOLDER - POLYMER
32382	Columbus,Anthony J	62848530	1	1	P	4-129172	CUTOUTS & FUSES	CUTOUTS AND FUSES	DS-320510-R	EA	CUTOUT-OPEN 15KV W/ 100A FUSEHOLDER - POLYMER
32383	Columbus,Anthony J	62848530	1	1	P	4-129172	CUTOUTS & FUSES	CUTOUTS AND FUSES	DS-320510-R	EA	CUTOUT-OPEN 15KV W/ 100A FUSEHOLDER - POLYMER
32384	Columbus,Anthony J	62848530	1	1	P	4-129172	CUTOUTS & FUSES	CUTOUTS AND FUSES	DS-325700	EA	FUSE-LINK TYPE T 100A
32385	Columbus,Anthony J	62848530	1	1	P	4-129172	RECLOSER	RECLOSERS	DS-347150	EA	RECLOSER 15 KV 6300 AMP TRIPSAVER (S&C) 200A CONTINUOUS CURRENT
Work Request Detail

 

[srh5428]

Can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Thank you for the instructions. I posted some sample data per your instructions. Thank you. I was not able to bring in the column headers due to the size of my table.

 

[Fluff]

Thanks for that, how about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1
2	Columbus,Anthony J	62850323	1	1	P	4-122372	POLE & EQ HW-OH	POLE AND EQUIPMENT HARDWARE - OVERHEAD	DS-130300	EA	BRACKET ARRESTER OR CUTOUT- F/ CROSSARM MOUNTING
3	Columbus,Anthony J	62850323	1	1	P	4-122372	CUTOUTS & FUSES	CUTOUTS AND FUSES	DS-320510-R	EA	CUTOUT-OPEN 15KV W/ 100A FUSEHOLDER - POLYMER
4	Columbus,Anthony J	62850323	1	1	P	4-122372	CUTOUTS & FUSES	CUTOUTS AND FUSES	DS-320510-R	EA	CUTOUT-OPEN 15KV W/ 100A FUSEHOLDER - POLYMER
5	Columbus,Anthony J	62850323	1	1	P	4-122372	CUTOUTS & FUSES	CUTOUTS AND FUSES	DS-320510-R	EA	CUTOUT-OPEN 15KV W/ 100A FUSEHOLDER - POLYMER
6	Columbus,Anthony J	62850323	1	1	P	4-122372	CUTOUTS & FUSES	CUTOUTS AND FUSES	DS-320510-R	EA	CUTOUT-OPEN 15KV W/ 100A FUSEHOLDER - POLYMER
7	Columbus,Anthony J	62850323	1	1	P	4-122372	CUTOUTS & FUSES	CUTOUTS AND FUSES	DS-320510-R	EA	CUTOUT-OPEN 15KV W/ 100A FUSEHOLDER - POLYMER
8	Columbus,Anthony J	62850323	1	1	P	4-122372	CUTOUTS & FUSES	CUTOUTS AND FUSES	DS-325650	EA	FUSE-LINK TYPE T 80A
9	Columbus,Anthony J	62850323	1	1	P	4-122372	RECLOSER	RECLOSERS	DS-347150	EA	RECLOSER 15 KV 6300 AMP TRIPSAVER (S&C) 200A CONTINUOUS CURRENT
10	Columbus,Anthony J	62848530	1	1	P	4-129172	POLE & EQ HW-OH	POLE AND EQUIPMENT HARDWARE - OVERHEAD	DS-130300	EA	BRACKET ARRESTER OR CUTOUT- F/ CROSSARM MOUNTING
11	Columbus,Anthony J	62848530	1	1	P	4-129172	CUTOUTS & FUSES	CUTOUTS AND FUSES	DS-320510-R	EA	CUTOUT-OPEN 15KV W/ 100A FUSEHOLDER - POLYMER
12	Columbus,Anthony J	62848530	1	1	P	4-129172	CUTOUTS & FUSES	CUTOUTS AND FUSES	DS-320510-R	EA	CUTOUT-OPEN 15KV W/ 100A FUSEHOLDER - POLYMER
13	Columbus,Anthony J	62848530	1	1	P	4-129172	CUTOUTS & FUSES	CUTOUTS AND FUSES	DS-320510-R	EA	CUTOUT-OPEN 15KV W/ 100A FUSEHOLDER - POLYMER
14	Columbus,Anthony J	62848530	1	1	P	4-129172	CUTOUTS & FUSES	CUTOUTS AND FUSES	DS-320510-R	EA	CUTOUT-OPEN 15KV W/ 100A FUSEHOLDER - POLYMER
15	Columbus,Anthony J	62848530	1	1	P	4-129172	CUTOUTS & FUSES	CUTOUTS AND FUSES	DS-320510-R	EA	CUTOUT-OPEN 15KV W/ 100A FUSEHOLDER - POLYMER
16	Columbus,Anthony J	62848530	1	1	P	4-129172	CUTOUTS & FUSES	CUTOUTS AND FUSES	DS-325700	EA	FUSE-LINK TYPE T 100A
17	Columbus,Anthony J	62848530	1	1	P	4-129172	RECLOSER	RECLOSERS	DS-347150	EA	RECLOSER 15 KV 6300 AMP TRIPSAVER (S&C) 200A CONTINUOUS CURRENT
18
19
20
21
22	Columbus,Anthony J	62850323	1	1	P	4-122372	POLE & EQ HW-OH	POLE AND EQUIPMENT HARDWARE - OVERHEAD	DS-130300	EA	BRACKET ARRESTER OR CUTOUT- F/ CROSSARM MOUNTING
23	Columbus,Anthony J	62850323	1	1	P	4-122372	CUTOUTS & FUSES	CUTOUTS AND FUSES	DS-320510-R	EA	CUTOUT-OPEN 15KV W/ 100A FUSEHOLDER - POLYMER
24	Columbus,Anthony J	62850323	1	1	P	4-122372	CUTOUTS & FUSES	CUTOUTS AND FUSES	DS-320510-R	EA	CUTOUT-OPEN 15KV W/ 100A FUSEHOLDER - POLYMER
25	Columbus,Anthony J	62850323	1	1	P	4-122372	CUTOUTS & FUSES	CUTOUTS AND FUSES	DS-320510-R	EA	CUTOUT-OPEN 15KV W/ 100A FUSEHOLDER - POLYMER
26	Columbus,Anthony J	62850323	1	1	P	4-122372	CUTOUTS & FUSES	CUTOUTS AND FUSES	DS-320510-R	EA	CUTOUT-OPEN 15KV W/ 100A FUSEHOLDER - POLYMER
27	Columbus,Anthony J	62850323	1	1	P	4-122372	CUTOUTS & FUSES	CUTOUTS AND FUSES	DS-320510-R	EA	CUTOUT-OPEN 15KV W/ 100A FUSEHOLDER - POLYMER
28	Columbus,Anthony J	62850323	1	1	P	4-122372	CUTOUTS & FUSES	CUTOUTS AND FUSES	DS-325650	EA	FUSE-LINK TYPE T 80A
29	Columbus,Anthony J	62850323	1	1	P	4-122372	RECLOSER	RECLOSERS	DS-347150	EA	RECLOSER 15 KV 6300 AMP TRIPSAVER (S&C) 200A CONTINUOUS CURRENT
30	Columbus,Anthony J	62848530	1	1	P	4-129172	POLE & EQ HW-OH	POLE AND EQUIPMENT HARDWARE - OVERHEAD	DS-130300	EA	BRACKET ARRESTER OR CUTOUT- F/ CROSSARM MOUNTING
31	Columbus,Anthony J	62848530	1	1	P	4-129172	CUTOUTS & FUSES	CUTOUTS AND FUSES	DS-320510-R	EA	CUTOUT-OPEN 15KV W/ 100A FUSEHOLDER - POLYMER
32	Columbus,Anthony J	62848530	1	1	P	4-129172	CUTOUTS & FUSES	CUTOUTS AND FUSES	DS-320510-R	EA	CUTOUT-OPEN 15KV W/ 100A FUSEHOLDER - POLYMER
33	Columbus,Anthony J	62848530	1	1	P	4-129172	CUTOUTS & FUSES	CUTOUTS AND FUSES	DS-320510-R	EA	CUTOUT-OPEN 15KV W/ 100A FUSEHOLDER - POLYMER
34	Columbus,Anthony J	62848530	1	1	P	4-129172	CUTOUTS & FUSES	CUTOUTS AND FUSES	DS-320510-R	EA	CUTOUT-OPEN 15KV W/ 100A FUSEHOLDER - POLYMER
35	Columbus,Anthony J	62848530	1	1	P	4-129172	CUTOUTS & FUSES	CUTOUTS AND FUSES	DS-320510-R	EA	CUTOUT-OPEN 15KV W/ 100A FUSEHOLDER - POLYMER
36	Columbus,Anthony J	62848530	1	1	P	4-129172	CUTOUTS & FUSES	CUTOUTS AND FUSES	DS-325700	EA	FUSE-LINK TYPE T 100A
37	Columbus,Anthony J	62848530	1	1	P	4-129172	RECLOSER	RECLOSERS	DS-347150	EA	RECLOSER 15 KV 6300 AMP TRIPSAVER (S&C) 200A CONTINUOUS CURRENT
Sheet5

Cell Formulas
Range	Formula
A22:K37	A22	=FILTER(A2:K20,COUNTIFS(B2:B20,B2:B20,G2:G20,"recloser"))
Dynamic array formulas.

 

[srh5428]

Thanks for that, how about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1
2	Columbus,Anthony J	62850323	1	1	P	4-122372	POLE & EQ HW-OH	POLE AND EQUIPMENT HARDWARE - OVERHEAD	DS-130300	EA	BRACKET ARRESTER OR CUTOUT- F/ CROSSARM MOUNTING
3	Columbus,Anthony J	62850323	1	1	P	4-122372	CUTOUTS & FUSES	CUTOUTS AND FUSES	DS-320510-R	EA	CUTOUT-OPEN 15KV W/ 100A FUSEHOLDER - POLYMER
4	Columbus,Anthony J	62850323	1	1	P	4-122372	CUTOUTS & FUSES	CUTOUTS AND FUSES	DS-320510-R	EA	CUTOUT-OPEN 15KV W/ 100A FUSEHOLDER - POLYMER
5	Columbus,Anthony J	62850323	1	1	P	4-122372	CUTOUTS & FUSES	CUTOUTS AND FUSES	DS-320510-R	EA	CUTOUT-OPEN 15KV W/ 100A FUSEHOLDER - POLYMER
6	Columbus,Anthony J	62850323	1	1	P	4-122372	CUTOUTS & FUSES	CUTOUTS AND FUSES	DS-320510-R	EA	CUTOUT-OPEN 15KV W/ 100A FUSEHOLDER - POLYMER
7	Columbus,Anthony J	62850323	1	1	P	4-122372	CUTOUTS & FUSES	CUTOUTS AND FUSES	DS-320510-R	EA	CUTOUT-OPEN 15KV W/ 100A FUSEHOLDER - POLYMER
8	Columbus,Anthony J	62850323	1	1	P	4-122372	CUTOUTS & FUSES	CUTOUTS AND FUSES	DS-325650	EA	FUSE-LINK TYPE T 80A
9	Columbus,Anthony J	62850323	1	1	P	4-122372	RECLOSER	RECLOSERS	DS-347150	EA	RECLOSER 15 KV 6300 AMP TRIPSAVER (S&C) 200A CONTINUOUS CURRENT
10	Columbus,Anthony J	62848530	1	1	P	4-129172	POLE & EQ HW-OH	POLE AND EQUIPMENT HARDWARE - OVERHEAD	DS-130300	EA	BRACKET ARRESTER OR CUTOUT- F/ CROSSARM MOUNTING
11	Columbus,Anthony J	62848530	1	1	P	4-129172	CUTOUTS & FUSES	CUTOUTS AND FUSES	DS-320510-R	EA	CUTOUT-OPEN 15KV W/ 100A FUSEHOLDER - POLYMER
12	Columbus,Anthony J	62848530	1	1	P	4-129172	CUTOUTS & FUSES	CUTOUTS AND FUSES	DS-320510-R	EA	CUTOUT-OPEN 15KV W/ 100A FUSEHOLDER - POLYMER
13	Columbus,Anthony J	62848530	1	1	P	4-129172	CUTOUTS & FUSES	CUTOUTS AND FUSES	DS-320510-R	EA	CUTOUT-OPEN 15KV W/ 100A FUSEHOLDER - POLYMER
14	Columbus,Anthony J	62848530	1	1	P	4-129172	CUTOUTS & FUSES	CUTOUTS AND FUSES	DS-320510-R	EA	CUTOUT-OPEN 15KV W/ 100A FUSEHOLDER - POLYMER
15	Columbus,Anthony J	62848530	1	1	P	4-129172	CUTOUTS & FUSES	CUTOUTS AND FUSES	DS-320510-R	EA	CUTOUT-OPEN 15KV W/ 100A FUSEHOLDER - POLYMER
16	Columbus,Anthony J	62848530	1	1	P	4-129172	CUTOUTS & FUSES	CUTOUTS AND FUSES	DS-325700	EA	FUSE-LINK TYPE T 100A
17	Columbus,Anthony J	62848530	1	1	P	4-129172	RECLOSER	RECLOSERS	DS-347150	EA	RECLOSER 15 KV 6300 AMP TRIPSAVER (S&C) 200A CONTINUOUS CURRENT
18
19
20
21
22	Columbus,Anthony J	62850323	1	1	P	4-122372	POLE & EQ HW-OH	POLE AND EQUIPMENT HARDWARE - OVERHEAD	DS-130300	EA	BRACKET ARRESTER OR CUTOUT- F/ CROSSARM MOUNTING
23	Columbus,Anthony J	62850323	1	1	P	4-122372	CUTOUTS & FUSES	CUTOUTS AND FUSES	DS-320510-R	EA	CUTOUT-OPEN 15KV W/ 100A FUSEHOLDER - POLYMER
24	Columbus,Anthony J	62850323	1	1	P	4-122372	CUTOUTS & FUSES	CUTOUTS AND FUSES	DS-320510-R	EA	CUTOUT-OPEN 15KV W/ 100A FUSEHOLDER - POLYMER
25	Columbus,Anthony J	62850323	1	1	P	4-122372	CUTOUTS & FUSES	CUTOUTS AND FUSES	DS-320510-R	EA	CUTOUT-OPEN 15KV W/ 100A FUSEHOLDER - POLYMER
26	Columbus,Anthony J	62850323	1	1	P	4-122372	CUTOUTS & FUSES	CUTOUTS AND FUSES	DS-320510-R	EA	CUTOUT-OPEN 15KV W/ 100A FUSEHOLDER - POLYMER
27	Columbus,Anthony J	62850323	1	1	P	4-122372	CUTOUTS & FUSES	CUTOUTS AND FUSES	DS-320510-R	EA	CUTOUT-OPEN 15KV W/ 100A FUSEHOLDER - POLYMER
28	Columbus,Anthony J	62850323	1	1	P	4-122372	CUTOUTS & FUSES	CUTOUTS AND FUSES	DS-325650	EA	FUSE-LINK TYPE T 80A
29	Columbus,Anthony J	62850323	1	1	P	4-122372	RECLOSER	RECLOSERS	DS-347150	EA	RECLOSER 15 KV 6300 AMP TRIPSAVER (S&C) 200A CONTINUOUS CURRENT
30	Columbus,Anthony J	62848530	1	1	P	4-129172	POLE & EQ HW-OH	POLE AND EQUIPMENT HARDWARE - OVERHEAD	DS-130300	EA	BRACKET ARRESTER OR CUTOUT- F/ CROSSARM MOUNTING
31	Columbus,Anthony J	62848530	1	1	P	4-129172	CUTOUTS & FUSES	CUTOUTS AND FUSES	DS-320510-R	EA	CUTOUT-OPEN 15KV W/ 100A FUSEHOLDER - POLYMER
32	Columbus,Anthony J	62848530	1	1	P	4-129172	CUTOUTS & FUSES	CUTOUTS AND FUSES	DS-320510-R	EA	CUTOUT-OPEN 15KV W/ 100A FUSEHOLDER - POLYMER
33	Columbus,Anthony J	62848530	1	1	P	4-129172	CUTOUTS & FUSES	CUTOUTS AND FUSES	DS-320510-R	EA	CUTOUT-OPEN 15KV W/ 100A FUSEHOLDER - POLYMER
34	Columbus,Anthony J	62848530	1	1	P	4-129172	CUTOUTS & FUSES	CUTOUTS AND FUSES	DS-320510-R	EA	CUTOUT-OPEN 15KV W/ 100A FUSEHOLDER - POLYMER
35	Columbus,Anthony J	62848530	1	1	P	4-129172	CUTOUTS & FUSES	CUTOUTS AND FUSES	DS-320510-R	EA	CUTOUT-OPEN 15KV W/ 100A FUSEHOLDER - POLYMER
36	Columbus,Anthony J	62848530	1	1	P	4-129172	CUTOUTS & FUSES	CUTOUTS AND FUSES	DS-325700	EA	FUSE-LINK TYPE T 100A
37	Columbus,Anthony J	62848530	1	1	P	4-129172	RECLOSER	RECLOSERS	DS-347150	EA	RECLOSER 15 KV 6300 AMP TRIPSAVER (S&C) 200A CONTINUOUS CURRENT
Sheet5

Cell Formulas
Range	Formula
A22:K37	A22	=FILTER(A2:K20,COUNTIFS(B2:B20,B2:B20,G2:G20,"recloser"))
Dynamic array formulas.

That seemed to work! What is the first two arguments of the COUNTIFS saying? Thank you

 

[Fluff]

It's effectively the same as

COUNTIFS(B$2:B$20,B2,G$2:G$20,"recloser")

dragged down, but it does it all at once.

 

[srh5428]

It's effectively the same as

COUNTIFS(B$2:B$20,B2,G$2:G$20,"recloser")

dragged down, but it does it all at once.

Thank you for you're help

 

[Fluff]

You're welcome & thanks for the feedback.