Pivot table or index + match + filter + { }?

[Vcoppens]

Hi,

I have one table that would need to be split up in several (9 in total) sub-tables in the same TAB.

Each sub-table is based on a combination of a code (which has a unique value) in combination with a product. One product can be linked to several unique codes.

There are 30 columns but I would only need the data from 18 columns.

I have been pondering if I should
1. create a pivot table?
Problem: the columns data is either sum or count but not the value that is in the table. For example Name (ABC) and # (123) will show up as ABC 1 Instead of AB 123.
2. create a formula to extract those specific rows that fulfill the requirements. But I am a bit stuck on how to accomplish this.

Thank you very much if anyone could help me shed some light!

A	B	C-Name	D-#	E-Product	F-code (Unique Value)	Z-$
10	5	ABC	123	8	50	$100
10	5	BCD	234	1	60	$150
10	5	CDE	345	3	70	$175
10	5	DEF	456	3	80	$90
10	5	EFG	567	1	90	$60
Requirement	code 50&60	& Product 8 & 1
A	B	C-Name	D-#	E-Product	F-Code	Z-$
10	5	ABC	123	8	50	$100
10	5	BCD	234	1	60	$150
TOTAL						$250
Requirement	code 70	& Product 3
A	B	C-Name	D-#	E-Product	F-Code	Z-$
10	5	CDE	345	3	70	$175
TOTAL						$175
Requirement	code 80 & 90	& Product 1 & 3
A	B	C-Name	D-#	E-Product	F-Code	Z-$
10	5	DEF	456	3	80	$90
10	5	EFG	567	1	90	$60
TOTAL						$150

 

[Fluff]

I do have Excel 360 Enterprise.

Maybe you missed this

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Vcoppens]

Hi Awoohaw,

I hope that this XL2BB makes it clearer:

To the right is the original table (I restricted it to 17 rows but there are many more) with 30 columns.

To the left (columns A & B) are the criteria that I am looking for, for each table. Here I took 3 different examples. There will be 9 different tables, so there will be 9 different criteria tables. The criteria are based on "Product #" and "Code".

In the center are the 9 tables that will be part of the report (here there are only 3 depicted). The color code in the criteria table (columns A & B) is the one that I am looking for in the original table. It needs to be a combination of for example A2 & B2 or A3 & B3 for the first table; for the second table it will be A1 & b7 OR A8 & B8 OR A9 & B9 OR ..... A14 & B14; for the third table it will be A17& B17 OR A18 & B18 ... OR A24 & B24.

Only 18 columns of the 30 columns should be part of the tables that will be used for reporting.

I color coded, so it might be less confusing.

Hope this help.

Thank you very much.

9 tables.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ
1	Product #	Code		Inst	Loc	Name	#	Product #	Code	Goal	Grade	Org	Date	Balance	Int 1	Date 1	RE / Non RE	Year	MBA / SOC / SOC	Report / Exclude	Lead		Inst	Loc	Name	#	Product #	Code	Goal	Grade	Category	Org	Date	Balance	Int 1	Int Pd To	Int 2	Sum	F or V	Next	Date 1	RE / Non RE	5300 code	Year	MBA / SOC / SOC	Report / Exclude	RE / Non RE	PMT	Approve	INC	Terms	Lead
2	8	59		105	10	KLM	891	1	59	6	2	2	8/31/2021	5,000.00	130.32	8/31/2021	Non RE	2022	COM	Report	397,045.86		105	10	ABC	123	1	60	1	1	5	7	10/1/2023	10,000.00	235.07	10/1/2023	47.0137	6.5	F	10/1/2023	10/1/2023	RE	r	2023	MBA	Exclude	n/a	1973.64	Recommended	531110	60	264,000.00
3	1	59		105	10	MNO	156	8	59	6	1	2	9/1/2022	3,000.00	2159.69	9/1/2022	RE	2022	Lead	Report	700,000.00		105	10	BCD	234	8	61	1	1	5	8	9/1/2022	5,000.00	277.81	9/1/2022	18.52055	6.5	F	9/1/2022	9/1/2022	RE	r	2023	MBA	Exclude	n/a	775.65	Recommended	531110	60	104,000.00
4																							105	10	CDE	124	1	63	1	1	5	9	10/1/2020	4,000.00	248.42	10/1/2020	16.56164	6.5	F	10/1/2020	10/1/2020	RE	r	2023	MBA	Exclude	n/a	693.61	Recommended	531110	60	93,000.00
5																							105	10	EFG	245	1	80	1	1	5	10	9/30/2022	3,000.00	248.42	9/30/2022	16.56164	6.5	F	9/30/2022	9/30/2022	RE	r	2023	MBA	Exclude	n/a	693.61	Recommended	531110	60	93,000.00
6	Product #	Code		Inst	Loc	Name	#	Product #	Code	Goal	Grade	Org	Date	Balance	Int 1	Date 1	RE / Non RE	Year	MBA / SOC / SOC	Report / Exclude	Lead		105	10	FGH	126	8	60	50	2	5	5	8/31/2021	2,000.00	250.02	8/31/2021	27.78082	6.5	F	8/31/2021	8/31/2021	RE	r	2023	MBA	Exclude	n/a	2012.25	Recommended	531110	60	156,000.00
7	1	60		105	10	ABC	123	1	60	1	1	7	10/1/2023	10,000.00	235.07	10/1/2023	RE	2023	MBA	Exclude	264,000.00		105	10	GHI	267	1	82	3	2	5	6	10/1/2023	10,000.00	658.48	10/1/2023	47.03475	6.25	F	10/1/2023	10/1/2023	Non RE	r	2023	MBA	Exclude	n/a	2012.25	Recommended	531110	60	274,682.96
8	1	80		105	10	EFG	245	1	80	1	1	10	9/30/2022	3,000.00	248.42	9/30/2022	RE	2023	MBA	Exclude	93,000.00		105	10	HIJ	282	9	80	2	2	5	8	9/1/2022	7,000.00	0	0:00	0	5.25	F	9/1/2022	9/1/2022	RE	c	2022	SOC	Report	n/a	2012.25	Recommended	531110	61	0.00
9	8	60		105	10	FGH	126	8	60	50	2	5	8/31/2021	2,000.00	250.02	8/31/2021	RE	2023	MBA	Exclude	156,000.00		105	10	IJK	399	1	63	5	1	5	7	10/1/2020	8,000.00	129.95	10/1/2020	9.2821	6.5	F	10/1/2020	10/1/2020	RE	r	2022	MBA	Exclude	n/a	390.26	Recommended	531110	60	52,122.57
10	3	80		105	10	GHI	267	1	82	3	2	6	10/1/2023	10,000.00	658.48	10/1/2023	Non RE	2023	MBA	Exclude	274,682.96		105	10	JKL	105	1	63	5	2	5	1	9/30/2022	10,000.00	311.81	9/30/2022	16.41096	5.99	F	9/30/2022	9/30/2022	RE	r	2022	MBA	Exclude	n/a	2012.25	Recommended	531110	60	100,000.00
11	8	80		105	10	HIJ	282	9	80	2	2	8	9/1/2022	7,000.00	0	9/1/2022	RE	2022	SOC	Report	0.00		105	10	KLM	891	1	59	6	2	5	2	8/31/2021	5,000.00	130.32	8/31/2021	65.15903	5.99	F	8/31/2021	8/31/2021	Non RE	c	2022	COM	Report	n/a	2866.75	Recommended	531110	60	397,045.86
12	1	82		105	10	PQR	129	9	80	4	1	6	8/31/2021	7,000.00	576.62	8/31/2021	RE	2022	COM	Report	311,803.51		105	10	LMN	813	1	65	4	1	5	2	10/1/2023	4,000.00	10751.71	10/1/2023	767.9795	5.75	F	10/1/2023	10/1/2023	RE	e	2022	Lead	Report	n/a	2012.25	Recommended	531120	60	758,759.62
13	1	54		105	10	QRS'	852	8	80	6	1	8	7/28/2019	8,000.00	1371.97	7/28/2019	RE	2022	SOC	Report	700,375.65		105	10	MNO	156	8	59	6	1	5	2	9/1/2022	3,000.00	2159.69	9/1/2022	154.263	5.99	F	9/1/2022	9/1/2022	RE	c	2022	Lead	Report	n/a	2012.25	Recommended	531110	60	700,000.00
14	9	80																					105	10	NOP	675	8	55	5	3	5	5	10/1/2020	2,000.00	310.03	10/1/2020	16.31712	6	F	10/1/2020	10/1/2020	RE	r	2022	MBA	Exclude	n/a	717.5	Recommended	531110	60	99,262.45
15																							105	10	OPQ	975	1	61	5	1	5	4	9/30/2022	10,000.00	1376.85	9/30/2022	72.46575	5.75	F	9/30/2022	9/30/2022	RE	c	2022	MBA	Exclude	n/a	2012.25	Recommended	531110	60	460,000.00
16	Product #	Code		Inst	Loc	Name	#	Product #	Code	Goal	Grade	Org	Date	Balance	Int 1	Date 1	RE / Non RE	Year	MBA / SOC / SOC	Report / Exclude	Lead		105	10	PQR	129	9	80	4	1	5	6	8/31/2021	7,000.00	576.62	8/31/2021	38.44153	4.5	F	8/31/2021	8/31/2021	RE	e	2022	COM	Report	n/a	1998.79	Recommended	493190	60	311,803.51
17	8	61		105	10	BCD	234	8	61	1	1	8	9/1/2022	5,000.00	277.81	9/1/2022	RE	2023	MBA	Exclude	104,000.00		105	10	QRS'	852	8	80	6	1	5	8	7/28/2019	8,000.00	1371.97	7/28/2019	105.5361	5.5	F	7/28/2019	7/28/2019	RE	c	2022	SOC	Report	n/a	2012.25	Recommended	531110	61	700,375.65
18	1	63		105	10	CDE	124	1	63	1	1	9	10/1/2020	4,000.00	248.42	10/1/2020	RE	2023	MBA	Exclude	93,000.00
19	1	65		105	10	IJK	399	1	63	5	1	7	10/1/2020	8,000.00	129.95	10/1/2020	RE	2022	MBA	Exclude	52,122.57
20	1	67		105	10	JKL	105	1	63	5	2	1	9/30/2022	10,000.00	311.81	9/30/2022	RE	2022	MBA	Exclude	100,000.00
21	1	73		105	10	LMN	813	1	65	4	1	2	10/1/2023	4,000.00	10751.71	10/1/2023	RE	2022	Lead	Report	758,759.62
22	1	90		105	10	NOP	675	8	55	5	3	5	10/1/2020	2,000.00	310.03	10/1/2020	RE	2022	MBA	Exclude	99,262.45
23	8	55		105	10	OPQ	975	1	61	5	1	4	9/30/2022	10,000.00	1376.85	9/30/2022	RE	2022	MBA	Exclude	460,000.00
24	1	61
25
26
27
28	etc.
29
30
Sheet1