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

 

[Vcoppens]

HI, I researched further, and I don't think it will be possible.
The only solution seems to sort the table by the code and product and then copy that result as a separate table. And repeat 9 times for the different code and product combination.
Unless someone has experience and was able to successfully split a table in multiple tables in one TAB.
Thank you,

 

[awoohaw]

I'm not sure I understand your problem completely.
But, why can't you keep your aggregate data in one table and then build formulas for each circumstance you want.
If you have 365 or a version that has the FILTER function it would be very easy. SO... please update your profile so the forum knows what version you are using.

Thanks in advance.

 

[Vcoppens]

I'm not sure I understand your problem completely.
But, why can't you keep your aggregate data in one table and then build formulas for each circumstance you want.
If you have 365 or a version that has the FILTER function it would be very easy. SO... please update your profile so the forum knows what version you are using.

Thanks in advance.

Hi! Thank you for your response.
The filter function would be the easiest but the issue is:

There is one main table with all the data.

That table needs to be broken down in different tables containing only specific information from this main table based on 2 headers (in my example: column E = Product and column F = Code).
All those table containing only specific information need to be shown on the same tab so it can be printed as a pdf for a report.

There are a total of 30 columns but the smaller tables only needs 18 columns.
I found a formula but it is a bit cumbersome and extracts all columns instead of just specific ones:
=INDEX($S$2:$AB$200,SMALL(IF(COUNTIF($A$2:$A$4,$W$2:$W$200)*COUNTIF($B$2:$B$4,$X$2:$X$200),MATCH(ROW($S$2:$AB$200),ROW($S$2:$AB$200))),ROWS($R$2:R2)),COLUMNS($R$2:R2))

Index = main table
countif: A2:A4 is the criteria that I am looking for in the main table in column w2:w200 (idem for B2:B4).

I was just wondering if there was an easier (automatic) way to extract the data.

I tried pivot in tabular form but that doesn't work neither.

Thank you very much,

 

[Fluff]

What version of Excel are you using?

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’)

 

[awoohaw]

Hi! Thank you for your response.
The filter function would be the easiest but the issue is:

There is one main table with all the data.

That table needs to be broken down in different tables containing only specific information from this main table based on 2 headers (in my example: column E = Product and column F = Code).
All those table containing only specific information need to be shown on the same tab so it can be printed as a pdf for a report.

There are a total of 30 columns but the smaller tables only needs 18 columns.
I found a formula but it is a bit cumbersome and extracts all columns instead of just specific ones:
=INDEX($S$2:$AB$200,SMALL(IF(COUNTIF($A$2:$A$4,$W$2:$W$200)*COUNTIF($B$2:$B$4,$X$2:$X$200),MATCH(ROW($S$2:$AB$200),ROW($S$2:$AB$200))),ROWS($R$2:R2)),COLUMNS($R$2:R2))

Index = main table
countif: A2:A4 is the criteria that I am looking for in the main table in column w2:w200 (idem for B2:B4).

I was just wondering if there was an easier (automatic) way to extract the data.

I tried pivot in tabular form but that doesn't work neither.

Thank you very much,

If what you have posted in Post #1 is your "Main" data construction that is the root cause of your problem.
If it is not your main data table, then please share it so if you have 365 the FILTER function and other great functions will answer your problems.


If it is your main data source, spend some time to convert back to a flat file.

Another option to get the data in a reportable format is to use power query on what you have above and clean it that way, then create all the pivots you need.

 

[awoohaw]

What are the mini headers you have, for instance:
Requirement code 70 & Product 3
they seem to change with each section. Can you put them into the columns?
Delete the TOTALS row for each section.
and only have these columns:

Requirement

A

B

Code

C-Name

D-#

Product

E-Product

F-Code

Z-$

 

[awoohaw]

Also, if you can use the xl2bb add in to paste a mini sheet of your data it could be a bit more useful (link is below).
And again, the forum really needs to know what version of excel you use.

 

[Vcoppens]

HI Awoohaw,

I do have Excel 360 Enterprise. Apologies for not responding to this question earlier.

Thank you very much for your earlier suggestion to use FILTER.

I have applied it and it did work.

=SORT(FILTER(Q2:Z200,(U2:U200=A2)*(V2:V200=B2),""),3,1)

However there remain 2 issues:

1. The code (in column U) should match the data in A2:A3 [basically the OR function]
and product # (in column V) should match the data in B2:B3 [also the OR function]
but I am not sure how to apply it with the filter funcition.

2. The original table has 30 columns - I only need 18 columns for the report. I could fall back on the hide column function if necessary.

Thank you so much for helping out.

I will upload the XL2BB to make it clearer.

 

[awoohaw]

Can you post your data as a mini worksheet please with the xl2bb add in (link below).
You data is pretty complicated and pictures and table print would be hard to decipher.

An alternative would be to post a sanitized version of your workbook on a share site like dropbox.